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?
Add a comment
|
3 Answers
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.
Comments
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.
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.
Comments
In certain contexts, array[] cannot be used whereas '{...}' can. The reason is that the latter passes as a string literal (until it's later interpreted as an array by the SQL engine) whereas the former doesn't.
Example in php:
$stmt = pg_prepare("stmt", 'SELECT $1::int[]');
$result = pg_execute("stmt", array('array[1,2]'))
This fails with this error:
Query failed: ERROR: array value must start with "{" or dimension information
On the other hand, the sequence below succeeds:
$stmt = pg_prepare("stmt", 'SELECT $1::int[]');
$result = pg_execute("stmt", array('{1,2}'))
So both syntaxes are not interchangeable in this context. This is not specific to php, php being for the example here. Most database drivers will pass strings as query parameters instead of the exact SQL type when it's not a simple scalar.
Even if preferring the array[] syntax for its readability, one should still be prepared to switch to the {...} syntax when necessary.
3 Comments
Daniel Vérité
@a_horse_with_no_name: nope, execute() expects the list of parameters inside a php array, even if there's only one parameter.
Pavel Stehule
ARRAY[1,2,3] is a constructor - expression, '{x,y,z}' is a value - and parameter libpq exec function (on client side) have to be a value, due impossibility to evaluate SQL expression there..
