Re: syntax pb

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