Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will show you how to work with PostgreSQL array and introduce you to some handy functions for array manipulation.
In this example, we will insert our data in array format and learn how to query this data.
1 2 3 4 5 | CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); |
Let’s create our table as above.
Let’s note that there is a [ ] bracket sign at the end of the second and third column.
These are necessary for us to insert in array format.
We can insert our data in several ways.
1 2 3 4 | INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'); |
Secondly, we can perform the insert operation using the ARRAY command as follows.
1 2 3 4 5 6 7 8 | INSERT INTO sal_emp VALUES ('Ahmet', ARRAY[12000, 13000, 14000, 15000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]); INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]); |
We inserted our data and let’s make our query.
1 | select schedule[1:1] from sal_emp where name='Carol' ; |
Let’s reinforce it with another example.
1 | select pay_by_quarter[1] from sal_emp where name='Carol' ; |
