Thread: Puzzled by ROW constructor behaviour?
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.
Hi Eagna,
Cheers,
Steve
On Wed, Nov 23, 2022 at 8:11 AM Eagna <eagna@protonmail.com> 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.
On Tue, Nov 22, 2022 at 2:11 PM Eagna <eagna@protonmail.com> wrote:
NUMBER 2
> SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a test')) AS test2;
My question is that if a ROW constructor works for a VALUES clause in statement NUMBER 2, then why not NUMBER 3?
You've drawn a false equivalence from the similarity of the syntax.
The following also works:
SELECT 1 = (SELECT 1*1);
But I don't think there is any argument that while this works:
INSERT INTO tbl (col) SELECT 1*1;
This doesn't:
INSERT INTO tbl (col) 1;
There is no difference with replacing 1 with a composite type and the SELECT subquery with VALUES instead.
Your "VALUES" is just a scalar subquery expression that, if it indeed produces a single row and column, can be compared to any other scalar value (if it produces more than one row you will get an error - try it).
David J.