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,
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@protonmail.com> wrote:
Show quoted text
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 haveNUMBER 1
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same') AS test1;
result:
test1
fThis 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
tOK - 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.