Searching in Arrays : Array Column Select « Array « PostgreSQL






Searching in Arrays


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=# 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=# -- Searching in Arrays
postgres=# SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
postgres-#                            pay_by_quarter[2] = 10000 OR
postgres-#                            pay_by_quarter[3] = 10000 OR
postgres-#                            pay_by_quarter[4] = 10000;
 name |      pay_by_quarter       |                 schedule
------+---------------------------+-------------------------------------------
 Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
(1 row)

postgres=#
postgres=#
postgres=# drop table sal_emp;
DROP TABLE
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.Accessing two dimensional Arrays
8.Reference array element
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