Re: Puzzled by ROW constructor behaviour?

[Steve Baldwin <steve.baldwin@xxxxxxxxx>]

Hi Eagna,

Did you check the syntax of the INSERT statement? You either need 'VALUES ...' or a query. I don't think your _expression_ on its own is considered a query.

Cheers,

Steve

On Wed, Nov 23, 2022 at 8:11 AM Eagna <eagna@xxxxxxxxxxxxxx> wrote:

Hi all,

I'm puzzled by some behaviour of the ROW constructor that I noticed when I was playing around.

>From the documentation (https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS), we have

NUMBER 1

> SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same') AS test1;

result:

> test1
> f

This is fine.

and then

NUMBER 2

> SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a test')) AS test2;

result:

> test2
> t

OK - notice the equivalence of a ROW constructor and a VALUES clause.

So, then I create this table:

> CREATE TABLE test
> (
>  a INT NOT NULL,
>  b INT NOT NULL,
>  c TEXT NOT NULL
> );

and then tried:

NUMBER 3

> INSERT INTO test ((ROW (1, 2.4, 'test...')));

and I get:

> ERROR:  syntax error at or near "ROW"
> LINE 1: INSERT INTO test ((ROW (1, 2.4, 'test...')));


I tried various permutations of brackets and whatnot but nothing doing.


My question is that if a ROW constructor works for a VALUES clause in statement NUMBER 2, then why not NUMBER 3?


TIA and rgs,

E.