PostgreSQL INSERT bigint[] Array quote syntax

Question

I need to UPDATE existing rows with numeric arrays. I'm stuck on the syntax in regards to quotes (I think) currently. Here are the queries I have tried...

Test 1

WITH update_table_1 (id,column_b) AS 
(VALUES (1,'{22}'), (72,'{29, 5}')) 
UPDATE table_1 SET id = up.id, column_b = up.column_b FROM update_table_1 up 
WHERE up.id = table_1.id;

Test 2

WITH update_table_1 (id,column_b) AS 
(VALUES (1,'{"22"}'), (72,'{"29","5"}')) 
UPDATE table_1 SET id = up.id, column_b = up.column_b FROM update_table_1 up 
WHERE up.id = table_1.id;

Test 3

WITH update_table_1 (id,column_b) AS 
(VALUES (1,{22}), (72,{29, 5})) 
UPDATE table_1 SET id = up.id, column_b = up.column_b FROM update_table_1 up 
WHERE up.id = table_1.id;

Test 4

WITH update_table_1 (id,column_b) AS 
(VALUES (1,{22}), (72,{"29","5"})) 
UPDATE table_1 SET id = up.id, column_b = up.column_b FROM update_table_1 up 
WHERE up.id = table_1.id;

@DanielVérité Thank you, please post it as an answer so I can accept it. — user3750809
– user3750809, Commented Dec 10, 2014 at 14:35

Denis de Bernardy · Accepted Answer · 2014-12-10 14:37:00Z

16

You need to use casts, the array[] constructor, or both:

select '{1,2}'::bigint[];
select array[1,2];           -- this is an int[]
select array[1,2]::bigint[];

Else, Postgres will (correctly) complain about unknown operators for types and the like.

answered Dec 10, 2014 at 14:37

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

1 Comment

IMSoP Over a year ago

The manual is actually rather lax on mentioning this; the relevant page would seem to be Section 8.15 Arrays, which just has this parenthetical comment: "(These kinds of array constants are actually only a special case of the generic type constants discussed in Section 4.1.2.7. The constant is initially treated as a string and passed to the array input conversion routine. An explicit type specification might be necessary.)"

Collectives™ on Stack Overflow

PostgreSQL INSERT bigint[] Array quote syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related