Difference in array syntaxes in postgres [duplicate]

Question

What is the difference between the following two ways to define an array?

select '{1, 2, 3, 4}', ARRAY[1, 2, 3, 4];

For example the docs gives the following two examples:

-- An array value can be replaced completely:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

-- or using the ARRAY expression syntax:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Are there any differences between these two forms? https://www.postgresql.org/docs/current/arrays.html

Also answered in here: dba.stackexchange.com/questions/161301/… — jian
– jian, Commented Aug 5, 2022 at 6:21

user330315 · Accepted Answer · 2022-08-05 05:49:59Z

1

There is no difference. Both result in the same array value.

One advantage of the array[] constructor syntax is, that the elements can be written with the usual syntax for SQL literals. E.g. array['One String', 'Other String'] vs. {"One String", "Other String"}'

Quote from the manual

Notice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal.

answered Aug 5, 2022 at 5:49

user330315

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

2 Comments

David542 Over a year ago

but they're not quite the same when I was testing. For example: select array[1]||array[2], '{1}'||'{2}' produces two different values.

David542 Over a year ago

Seems to require explicit casting for them to be treated the same, for example: '{1}'::int[]||'{2}'::int[].

Collectives™ on Stack Overflow

Difference in array syntaxes in postgres [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related