syntax pb

On Tuesday, May 30, 2023, Marc Millas <marc.millas@mokadb.com> wrote:

I get:
ERROR: column "d" is of type numeric but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

HINT: You will need to rewrite or cast the expression.

Can someone give a short SQL syntax hint ?

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

David J.

On 30/05/2023 14:45, Marc Millas wrote:

and , now, if I want to insert that:
Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
From t1 test1;

I get:
ERROR:  column "d" is of type numeric but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

HINT:  You will need to rewrite or cast the expression.

I'm guessing you'll need to cast the NULLs:

.... select distinct test1.t, 'abc', null::text, null::numeric ...

I don't think you need the aliases.

Ray.

Can someone give a short SQL syntax hint ?

thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com <http://www.mokadb.com&gt;

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 5/30/23 06:45, Marc Millas wrote:

Hi,

I always have had difficulties to understand syntax. So...

If I have:
create table t1 (t text);
create table t2 (a text, b text, c test, d numeric);

Is c supposed to be text?
Or are you indeed referring to some unspecified type?

insert into t1('azerty');
INSERT 0 1
fine !

Not with that syntax:

insert into t1('azerty');
ERROR: syntax error at or near "'azerty'"
LINE 1: insert into t1('azerty');

insert into t1 values('azerty');
INSERT 0 1

so, now, if I write:
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
From t1 test1;
   t      |  b   | c  | d
--------+-----+---+---
 azerty | abc |   |
(1 row)

ok.

Yes

and , now, if I want to insert that:
Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
From t1 test1;

I get:
ERROR:  column "d" is of type numeric but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

HINT:  You will need to rewrite or cast the expression.

Can someone give a short SQL syntax hint ?

The hint is that though NULL is unknown it can have a type.

To get this to work I first did:

create table t2 (a text, b text, c text, d numeric);

to have c be text for simplicity sake.

Then I did:

Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL::test, NULL::numeric
From t1 test1;

which results in:

select * from t2;
a | b | c | d
--------+-----+------+------
azerty | abc | NULL | NULL

thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com <http://www.mokadb.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/30/23 07:38, Adrian Klaver wrote:

On 5/30/23 06:45, Marc Millas wrote:

Hi,

I always have had difficulties to understand syntax. So...

If I have:
create table t1 (t text);
create table t2 (a text, b text, c test, d numeric);

Is c supposed to be text?
Or are you indeed referring to some unspecified type?

insert into t1('azerty');
INSERT 0 1
fine !

Not with that syntax:

insert into t1('azerty');
ERROR:  syntax error at or near "'azerty'"
LINE 1: insert into t1('azerty');

insert into t1 values('azerty');
INSERT 0 1

so, now, if I write:
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
 From t1 test1;
    t      |  b   | c  | d
--------+-----+---+---
  azerty | abc |   |
(1 row)

ok.

Yes

and , now, if I want to insert that:
Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
 From t1 test1;

I get:
ERROR:  column "d" is of type numeric but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

HINT:  You will need to rewrite or cast the expression.

Can someone give a short SQL syntax hint ?

The hint is that though NULL is unknown it can have a type.

To get this to work I first did:

create table t2 (a text, b text, c text, d numeric);

to have c be text for simplicity sake.

Then  I did:

Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL::test, NULL::numeric
From t1 test1;

The above should have been:

Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL::text, NULL::numeric
From t1 test1;

which results in:

select * from t2;
   a    |  b  |  c   |  d
--------+-----+------+------
 azerty | abc | NULL | NULL

thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com <http://www.mokadb.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

The above should have been:

Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL::text, NULL::numeric
From t1 test1;

which results in:

select * from t2;
a | b | c | d
--------+-----+------+------
azerty | abc | NULL | NULL

Thanks Adrian, but if the query becomes more complex, for example with a
few joins more, then even casting doesn't work.
This comes from a prod environment and even casting NULLs (which is more
than strange, BTW) generates absurd errors.
Too my understanding it looks like the parser did not parse the select
distinct as we think he does.

On Tue, May 30, 2023 at 05:53:30PM +0200, Marc Millas wrote:

Thanks Adrian, but if the query becomes more complex, for example with a
few joins more, then even casting doesn't work.
This comes from a prod environment and even casting NULLs (which is more
than strange, BTW) generates absurd errors.
Too my understanding it looks like the parser did not parse the select
distinct as we think he does.

Show *real* example that doesn't work, with schema. Clearly your example
can be easily made to work.

Best regards,

depesz

On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.millas@mokadb.com> wrote

This comes from a prod environment and even casting NULLs (which is more
than strange, BTW) generates absurd errors.

If you want an input to be anything other than plain text (numbers
partially exempted) you need to cast it. Sure, some limited cases allow
for other parts of a query to infer untyped literals, but literals defined
at the top-level of a SELECT is not one of those places.

Too my understanding it looks like the parser did not parse the select

distinct as we think he does.

The DISTINCT clause doesn't really come into play here at all, so if you
think it does you indeed have a misunderstanding.
Inputting literal NULLs, and using DISTINCT, are both, IMO, considered code
smells and seldom used. You still need to be able to interpret error
messages but if you are running actual queries with these things you may
have larger model design and query writing concerns to deal with in
addition to being able to identify the problems specific error messages are
pointing out and trying to fix them.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.millas@mokadb.com> wrote

Too my understanding it looks like the parser did not parse the select
distinct as we think he does.

The DISTINCT clause doesn't really come into play here at all, so if you
think it does you indeed have a misunderstanding.

No, he's correct:

postgres=# create table z (f1 int);
CREATE TABLE
postgres=# insert into z values(null);
INSERT 0 1
postgres=# insert into z select null;
INSERT 0 1
postgres=# insert into z select distinct null;
ERROR: column "f1" is of type integer but expression is of type text
LINE 1: insert into z select distinct null;
^
HINT: You will need to rewrite or cast the expression.

The first two INSERTs are accepted because there's nothing
"between" the untyped NULL and the INSERT, so we can resolve
the NULL as being of type int. But use of DISTINCT requires
resolving the type of the value (else how do you know what's
distinct from what?) and by default we'll resolve to text,
and then that doesn't match what the INSERT needs.

regards, tom lane

On 5/30/23 10:31 AM, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.millas@mokadb.com> wrote

Too my understanding it looks like the parser did not parse the select
distinct as we think he does.

The DISTINCT clause doesn't really come into play here at all, so if you
think it does you indeed have a misunderstanding.

No, he's correct:

postgres=# create table z (f1 int);
CREATE TABLE
postgres=# insert into z values(null);
INSERT 0 1
postgres=# insert into z select null;
INSERT 0 1
postgres=# insert into z select distinct null;
ERROR: column "f1" is of type integer but expression is of type text
LINE 1: insert into z select distinct null;
^
HINT: You will need to rewrite or cast the expression.

The first two INSERTs are accepted because there's nothing
"between" the untyped NULL and the INSERT, so we can resolve
the NULL as being of type int. But use of DISTINCT requires
resolving the type of the value (else how do you know what's
distinct from what?) and by default we'll resolve to text,
and then that doesn't match what the INSERT needs.

Huh, new lesson learned:

create table t1 (t text);
create table t2 (a text, b text, c test, d numeric);
insert into t1 values('azerty');

Insert into t2 (a, b, c, d)
Select test1.t, 'abc' as b, NULL as c, NULL as d
From t1 test1;

INSERT 0 1

select * from t2;
a | b | c | d
--------+-----+------+------
azerty | abc | NULL | NULL

Insert into t2 (a, b, c, d)
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
From t1 test1

ERROR: column "c" is of type test but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d
^
HINT: You will need to rewrite or cast the expression.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/30/23 8:53 AM, Marc Millas wrote:

Thanks Adrian, but if the query becomes more complex, for example with a
few joins more, then even casting doesn't work.
This comes from a prod environment and even casting NULLs (which is more
than strange, BTW) generates absurd errors.
Too my understanding it looks like the parser did not parse the select
distinct as we think he does.

Per Hubert we will need to see the complex query to be able to
troubleshoot it.

--
Adrian Klaver
adrian.klaver@aklaver.com

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, May 30, 2023 at 3:51 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tuesday, May 30, 2023, Marc Millas <marc.millas@mokadb.com> wrote:

I get:
ERROR: column "d" is of type numeric but expression is of type text
LINE 2: Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

HINT: You will need to rewrite or cast the expression.

Can someone give a short SQL syntax hint ?

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

I plainly agree on that...
but its NOT what's happeninng. The doc you point to states:
"An explicit type cast can usually be omitted if there is no ambiguity as
to the type that a value expression must produce (for example, when it is
assigned to a table column);"

in the SQL I provide I ask to put a NULL in a numeric column.
Can you tell where the ambiguity is ?

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, May 30, 2023 at 7:12 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, May 30, 2023 at 8:53 AM Marc Millas <marc.millas@mokadb.com> wrote

This comes from a prod environment and even casting NULLs (which is more
than strange, BTW) generates absurd errors.

If you want an input to be anything other than plain text (numbers
partially exempted) you need to cast it. Sure, some limited cases allow
for other parts of a query to infer untyped literals, but literals defined
at the top-level of a SELECT is not one of those places.

Too my understanding it looks like the parser did not parse the select

distinct as we think he does.

The DISTINCT clause doesn't really come into play here at all, so if you
think it does you indeed have a misunderstanding.
Inputting literal NULLs, and using DISTINCT, are both, IMO, considered
code smells and seldom used. You still need to be able to interpret error
messages but if you are running actual queries with these things you may
have larger model design and query writing concerns to deal with in
addition to being able to identify the problems specific error messages are
pointing out and trying to fix them.

Hi David, my guess about the distinct syntax was just because if I take the
distinct OUT, the SQL works fine. nothing more, nothing less...

Thanks for the explanation. Crystal clear, thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, May 30, 2023 at 7:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: