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(dot)klaver(at)aklaver(dot)com