PostgreSQL Array

Faruk Erdem

10 months ago

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.

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

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.

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

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.

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']]);

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.

select schedule[1:1] from sal_emp where name='Carol' ;

1	select schedule[1:1] from sal_emp where name='Carol' ;

Let’s reinforce it with another example.

select pay_by_quarter[1] from sal_emp where name='Carol' ;

1	select pay_by_quarter[1] from sal_emp where name='Carol' ;