Inserting array values

Question

How do I write and execute a query which inserts array values using libpqxx?

INSERT INTO exampleTable(exampleArray[3]) VALUES('{1, 2, 3}');

This example code gives me:

ERROR:  syntax error at or near "'"

What is wrong? In PostgreSQL documentation I found that:

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

...

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

Of course this matters. Your query would be syntactically correct on a text array column . — klin
– klin, Commented Oct 26, 2015 at 10:52
@klin I know. I meant, there is no difference between types, which are used. — ktoś tam
– ktoś tam, Commented Oct 26, 2015 at 15:17

klin · Accepted Answer · 2018-07-27 11:44:02Z

68

You should use a column name without an index to insert an array:

create table example(arr smallint[]);
insert into example(arr) values('{1, 2, 3}');
-- alternative syntax
-- insert into example(arr) values(array[1, 2, 3]);

select * from example;

   arr   
---------
 {1,2,3}
(1 row)

Use the column name with an index to access a single element of the array:

select arr[2] as "arr[2]"
from example;

 arr[2] 
--------
      2
(1 row)

update example set arr[2] = 10;
select * from example;

   arr    
----------
 {1,10,3}
(1 row)

You can use arr[n] in INSERT but this has special meaning. With this syntax you can create an array with one element indexed from the given number:

delete from example;
insert into example(arr[3]) values (1);
select * from example;

    arr    
-----------
 [3:3]={1}
(1 row)

As a result you have an array which lower bound is 3:

select arr[3] from example;
 arr 
-----
   1
(1 row)

edited Jul 27, 2018 at 11:44

answered Oct 26, 2015 at 10:53

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ktoś tam Over a year ago

It works, thanks. I tried "[]", "[3]", " ' " and without " ' " but I don't tried without "[]".

Victor Over a year ago

also you shall be able to execute this statement INSERT INTO example(arr) VALUES (ARRAY[1,2,3]) (as another alternative to use if you don't have a string value)

yurenchen · Accepted Answer · 2022-07-16 07:32:15Z

38

ref: https://ubiq.co/database-blog/how-to-insert-into-array-in-postgresql/

A. use `ARRAY`

insert into employees (id, name, phone_numbers)
         values (1, ' John Doe', ARRAY ['9998765432','9991234567']);

// less nested quotes

B. use `'{}'`

insert into employees (id, name, phone_numbers)
  values (2, ' Jim Doe', '{"9996587432","9891334567"}');

OR

insert into employees (id, name, phone_numbers)
  values (2, ' Jim Doe', '{9996587432,9891334567}');

// seems inner quotes " not necessary,
// number or string depends on column type.

edited Jul 16, 2022 at 7:32

answered Apr 20, 2022 at 9:19

yurenchen

2,77429 silver badges24 bronze badges

1 Comment

hmmftg Over a year ago

For string arrays it's more readble to use ARRAY

Collectives™ on Stack Overflow

Inserting array values

2 Answers 2

2 Comments

A. use `ARRAY`

B. use `'{}'`

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

A. use ARRAY

B. use '{}'

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

A. use `ARRAY`

B. use `'{}'`