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.

1

1 Answer 1

2

I'm passing an array of strings ...

But you are not passing an array.

Array

A Postgres array literal looks 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']

Row

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

('string1', 'string2')

(Except for places in the SQL syntax where a list is expected, like to the right of IN - which is neither row nor array.)

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 and acknowledge you have read our privacy policy.

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