PostgreSQL/Array/Array Column Insert

Insert array data to table

   <source lang="sql">

postgres=# CREATE TABLE arr(f1 int[], f2 int[]); CREATE TABLE postgres=# postgres=# INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); INSERT 0 1 postgres=# postgres=# SELECT ARRAY[f1, f2, "{{9,10},{11,12}}"::int[]] FROM arr;

                    array

---

{{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}

(1 row) postgres=# postgres=# SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE "bytea%");

                              ?column?

---

{2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413}

(1 row) postgres=# postgres=# drop table arr; DROP TABLE postgres=#

      </source>
   
  

Insert data for two dimensional array

   <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][2] FROM sal_emp WHERE name = "Bill";

                schedule

---

{{meeting,lunch},{training,presentation}}

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

      </source>
   
  

Insert data for two dimensional array column

   <source lang="sql">

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=# postgres=# SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

name |      pay_by_quarter       |                 schedule

---

+---------------------------+-------------------------------------------

Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}

(1 row) postgres=# postgres=# drop table sal_emp; DROP TABLE postgres=# postgres=#

      </source>
   
  

Inserting array constants

   <source lang="sql">

postgres=# postgres=# -- Inserting array constants 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=# postgres=# INSERT INTO book VALUES (103, "{"AAA", "VVVV"}"); INSERT 0 1 postgres=# postgres=# select * from book;

id  |     titles

---

+----------------

102 | {"AAA"s VVVV"}
103 | {AAA,VVVV}

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

      </source>
   
  

Inserting values into multidimensional arrays

   <source lang="sql">

postgres=# postgres=# -- Inserting values into multidimensional arrays postgres=# CREATE TABLE authors (id integer,titles text[][]); CREATE TABLE postgres=# postgres=# INSERT INTO authors postgres-# VALUES (102, postgres(# "{{"J T", "T S"}, postgres"# {"C D", "G E"}, postgres"# {"A D", "A L"}}"); INSERT 0 1 postgres=# postgres=# select * from authors;

id  |                   titles

---

+---------------------------------------------

102 | {{"J T","T S"},{"C D","G E"},{"A D","A L"}}

(1 row) postgres=# postgres=# drop table authors; DROP TABLE postgres=#

      </source>