Postgresql – Array of strings when updating a field

arraydatatypespostgresqlrowupdate

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.

Best Answer

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.

Postgresql – WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT

Locate the namespace inside which the citext type resides:

select nspname from pg_type t join pg_namespace n
  on n.oid=t.typnamespace where typname='citext';

Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext:

$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::public.citext";

If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and search_path: make sure that all your custom types are accessible no matter what.

PostgreSQL Update – Array Syntax vs (?,?) Syntax for NULL Point Field

Per documentation:

It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1.

But geometric types are not arrays.
While the value as a whole can be NULL, parts of a point (or any geometric type) cannot.

All geometric types can be input as string literals and cast to the respective type. None of them accept NULL as part of the input. This results in an error message:

SELECT '(NULL,1)'::point;

ERROR: invalid input syntax for type point: "(NULL,1)"
LINE 1: SELECT '(NULL,1)'::point

On the other hand, if you try passing one or more NULL values to a constructor function, you get NULL for the whole value - which is a bit inconsistent, but still makes sense: if one component is unknown, the whole value is unknown. All of these tests return TRUE:

SELECT point(NULL, NULL) IS NULL
      ,point(1, NULL) IS NULL
      ,point(NULL, 1) IS NULL
      ,point(NULL, 1) IS NULL
      ,circle(point(1, 1), NULL) IS NULL;

This leaves the case where an UPDATE silently ignores an assignment of NULL to pt[0] or pt[1]. This should really do one of two things:

Set the point to NULL - like SELECT point(NULL,1) does.
Raise an exception - like SELECT '(NULL,1)'::point does.

I would say this is a bug and should be reported.

Best Answer

Related Solutions

Postgresql – WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT

PostgreSQL Update – Array Syntax vs (?,?) Syntax for NULL Point Field

Related Question