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"}}'); 

Answer 1

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)