PostgreSQL/Array/Array Slice

Материал из SQL эксперт
Перейти к: навигация, поиск

An array slice is denoted by writing lower-bound:upper-bound for one or more array dimensions

   <source lang="sql">

postgres=# postgres=# postgres=# CREATE TABLE sal_emp ( postgres(# name text, postgres(# pay_by_quarter integer[], postgres(# schedule text[][] postgres(# ); CREATE TABLE postgres=# postgres=# INSERT INTO sal_emp postgres-# VALUES ("Bill", postgres(# ARRAY[10000, 10000, 10000, 10000], postgres(# ARRAY[["meeting", "lunch"], ["training", "presentation"]]); INSERT 0 1 postgres=# postgres=# INSERT INTO sal_emp postgres-# VALUES ("Carol", postgres(# ARRAY[20000, 25000, 25000, 25000], postgres(# ARRAY[["breakfast", "consulting"], ["meeting", "lunch"]]); INSERT 0 1 postgres=# postgres=# -- An array slice is denoted by writing lower-bound:upper-bound for one or more array dimensions. postgres=# postgres=# SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = "Bill";

       schedule

{{meeting},{training}}

(1 row) postgres=# postgres=# postgres=# SELECT * FROM sal_emp;

name  |      pay_by_quarter       |                 schedule

+---------------------------+-------------------------------------------
Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}

(2 rows) postgres=# postgres=# postgres=# drop table sal_emp; DROP TABLE postgres=# postgres=# postgres=#

      </source>
   
  


Selecting array values with slices

   <source lang="sql">

postgres=# postgres=# CREATE TABLE book (id integer,titles text[]); CREATE TABLE postgres=# postgres=# INSERT INTO book VALUES (102, "{"AAA\"s VVVV"}"); INSERT 0 1 postgres=# INSERT INTO book VALUES (103, "{"AAA", "VVVV"}"); INSERT 0 1 postgres=# postgres=# postgres=# -- Selecting array values with slices postgres=# postgres=# SELECT titles[1:2] FROM book;

    titles

{"AAA"s VVVV"}
{AAA,VVVV}

(2 rows) postgres=# postgres=# drop table book; DROP TABLE postgres=#

      </source>