Обсуждение: syntax pb

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 ?

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

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

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

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

"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