0

Unintentionally discovered that following query works in PostgreSQL:

UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1;

I'm passing an array of strings to field and the result is a string of '("string1","string2")', not an array as expected, at least then using php extension pgsql.

How this happens and that does ('string1', 'string2') means in PostgreSQL? I don't think this works in other SQL RDBMSes.

2

I'm passing an array of strings ...

You are not passing an array, at least not a Postgres array type.
An array literal would look like this:

'{string1, string2}'

You can type it explicitly:

'{string1, string2}'::text[]

The same can be achieved with an ARRAY constructor in most places:

ARRAY['string1', 'string2']

This is constructing a row (of type "anonymous record"):

('string1', 'string2')

It's short syntax for a ROW constructor:

ROW('string1', 'string2')

And can, in similar fashion be provided as string literal (row literal) as well:

'(string1,string2)'

Each of those anonymous records can be cast to a well know row type:

'(string1,string2)'::my_row_type

In the UPDATE statement Postgres coerces the value to the data type of the target column or raises an exception if that cannot be done. If sometable.somefield is data type text, the text representation of the above row is saved, which is '("string1","string2")'. Double quotes are only added where necessary to keep the syntax unambiguous.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.