Accessing two dimensional Arrays : Array Column Select « Array « PostgreSQL






Accessing two dimensional Arrays


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=# -- Accessing Arrays
postgres=#
postgres=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
 name
-------
 Carol
(1 row)

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=#
           
       








Related examples in the same category

1.Use array constant in select statement
2.Using two dimension array
3.Selecting entire array values
4.Selecting array values with subscripts
5.Select array column by index
6.Selecting From a Multi-Dimensional Array
7.Reference array element
8.Searching in Arrays
9.Array Input and Output Syntax
10.SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array
11.Specify non-default array subscripts in an array literal