In postgres, is there any difference between using the expression array[] vs using the string representation of an array with {}?

Question

Is there any difference in referencing or creating arrays using the two different syntax: array[1,2,3] vs '{1,2,3}'?

Is there any benefit to using either?

Answer 1

The ARRAY[] is the expression with value constructor. The '{}' is string literal (constant). The processing is little bit different - but for almost use cases there are not any significant difference.

The array constructor is necessary, when you can build the array dynamically

  target_var := ARRAY[var1, var2]; 

Next advantage of array constructor is building array from field with some special characters. In this case the usage of string literal can be less readable.

postgres=# select ARRAY['Tomas', 'Jiri'];
┌──────────────┐
│    array     │
╞══════════════╡
│ {Tomas,Jiri} │
└──────────────┘
(1 row)

postgres=# select ARRAY['Tomas{', 'Jiri'];
┌─────────────────┐
│      array      │
╞═════════════════╡
│ {"Tomas{",Jiri} │
└─────────────────┘
(1 row)

postgres=# select ARRAY['Tomas"', 'Jiri'];
┌──────────────────┐
│      array       │
╞══════════════════╡
│ {"Tomas\"",Jiri} │
└──────────────────┘
(1 row)

I usually prefer syntax with ARRAY due stronger expressivity, but sometimes I use string literal due shorter text.

Answer 2

No, there is no difference.

Personally I prefer to use ARRAY[...] as it doesn't require messing around with different levels of quotes if you create array with string literals.

Quote from the manual:

Tip: The ARRAY constructor syntax (see Section 4.2.12) is often easier to work with than the array-literal syntax when writing array values in SQL commands. In ARRAY, individual element values are written the same way they would be written when not members of an array.