pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
syntax pb
14+ messages / 6 participants
[nested] [flat]

* syntax pb
@ 2023-05-30 13:45 Marc Millas <marc.millas@mokadb.com>
  2023-05-30 13:51 ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
  2023-05-30 13:51 ` Re: syntax pb Ray O'Donnell <ray@rodonnell.ie>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 3 replies; 14+ messages in thread

From: Marc Millas @ 2023-05-30 13:45 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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);
insert into t1('azerty');
INSERT 0 1
fine !


so, now, if I write:
Select distinct test1.t, 'abc' as b, NULL as c, NULL as d

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
@ 2023-05-30 13:51 ` David G. Johnston <david.g.johnston@gmail.com>
  2023-05-30 18:28   ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  2 siblings, 1 reply; 14+ messages in thread

From: David G. Johnston @ 2023-05-30 13:51 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 13:51 ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
@ 2023-05-30 18:28   ` Marc Millas <marc.millas@mokadb.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Marc Millas @ 2023-05-30 18:28 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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 ?



>
> David J.
>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
@ 2023-05-30 13:51 ` Ray O'Donnell <ray@rodonnell.ie>
  2 siblings, 0 replies; 14+ messages in thread

From: Ray O'Donnell @ 2023-05-30 13:51 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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;
> 

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






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
@ 2023-05-30 14:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2 siblings, 1 reply; 14+ messages in thread

From: Adrian Klaver @ 2023-05-30 14:38 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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;
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-05-30 15:10   ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Adrian Klaver @ 2023-05-30 15:10 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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;
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-05-30 15:53     ` Marc Millas <marc.millas@mokadb.com>
  2023-05-30 15:57       ` Re: syntax pb hubert depesz lubaczewski <depesz@depesz.com>
  2023-05-30 17:11       ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
  2023-05-30 17:59       ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 3 replies; 14+ messages in thread

From: Marc Millas @ 2023-05-30 15:53 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.



> >> Marc MILLAS
> >> Senior Architect
> >> +33607850334
> >> www.mokadb.com <http://www.mokadb.com;
> >>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
@ 2023-05-30 15:57       ` hubert depesz lubaczewski <depesz@depesz.com>
  2 siblings, 0 replies; 14+ messages in thread

From: hubert depesz lubaczewski @ 2023-05-30 15:57 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
@ 2023-05-30 17:11       ` David G. Johnston <david.g.johnston@gmail.com>
  2023-05-30 17:31         ` Re: syntax pb Tom Lane <tgl@sss.pgh.pa.us>
  2023-05-30 18:31         ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  2 siblings, 2 replies; 14+ messages in thread

From: David G. Johnston @ 2023-05-30 17:11 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 17:11       ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
@ 2023-05-30 17:31         ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-05-30 17:57           ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 18:33           ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  1 sibling, 2 replies; 14+ messages in thread

From: Tom Lane @ 2023-05-30 17:31 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Marc Millas <marc.millas@mokadb.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

"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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 17:11       ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
  2023-05-30 17:31         ` Re: syntax pb Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-05-30 17:57           ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Adrian Klaver @ 2023-05-30 17:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: Marc Millas <marc.millas@mokadb.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 17:11       ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
  2023-05-30 17:31         ` Re: syntax pb Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-05-30 18:33           ` Marc Millas <marc.millas@mokadb.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Marc Millas @ 2023-05-30 18:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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:

> "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
>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 17:11       ` Re: syntax pb David G. Johnston <david.g.johnston@gmail.com>
@ 2023-05-30 18:31         ` Marc Millas <marc.millas@mokadb.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Marc Millas @ 2023-05-30 18:31 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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...

>
> David J.
>
>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: syntax pb
  2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
  2023-05-30 14:38 ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:10   ` Re: syntax pb Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-30 15:53     ` Re: syntax pb Marc Millas <marc.millas@mokadb.com>
@ 2023-05-30 17:59       ` Adrian Klaver <adrian.klaver@aklaver.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Adrian Klaver @ 2023-05-30 17:59 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2023-05-30 18:33 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-30 13:45 syntax pb Marc Millas <marc.millas@mokadb.com>
2023-05-30 13:51 ` David G. Johnston <david.g.johnston@gmail.com>
2023-05-30 18:28   ` Marc Millas <marc.millas@mokadb.com>
2023-05-30 13:51 ` Ray O'Donnell <ray@rodonnell.ie>
2023-05-30 14:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-30 15:10   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-30 15:53     ` Marc Millas <marc.millas@mokadb.com>
2023-05-30 15:57       ` hubert depesz lubaczewski <depesz@depesz.com>
2023-05-30 17:11       ` David G. Johnston <david.g.johnston@gmail.com>
2023-05-30 17:31         ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-30 17:57           ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-30 18:33           ` Marc Millas <marc.millas@mokadb.com>
2023-05-30 18:31         ` Marc Millas <marc.millas@mokadb.com>
2023-05-30 17:59       ` Adrian Klaver <adrian.klaver@aklaver.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox