From: | "Wim Ceulemans" <wim(dot)ceulemans(at)nice(dot)be> |
---|---|
To: | <pgsql-general(at)postgreSQL(dot)org> |
Subject: | Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs? |
Date: | 1998-11-27 08:51:48 |
Message-ID: | 000701be19e3$2b606200$67faa8c0@aladdin.nice.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>There are, on my mind, at least two answers. For experience I use
>small table 'create table a( dt datetime, i int)'. Hera are data in
>this table (one row has NULL as dt value):
>
>tolik=> select * from a;
>dt | i
>----------------------------+--
>Thu Nov 26 16:35:23 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
> |10
>
>First use 'union':
>-----------------------------------------------
>select dt::date, i from a where dt is not null
>union
>select NULL, i from a where dt is null;
> date| i
>----------+--
>11-25-1998| 2
>11-26-1998| 1
>11-27-1998| 3
> |10
>(4 rows)
>-----------------------------------------------
>
>
>Second, try use date_trunc('day', dt) instead date_part:
>--------------------------------------------------------------
>tolik=> select date_trunc('day', dt), i from a;
>date_trunc | i
>----------------------------+--
>Thu Nov 26 00:00:00 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
> |10
>(4 rows)
>
>--------------------------------------------------------------
>
I find the above interesting and I've tried something similar that won't
work.
I have two tables defined as follows
create table test1 (id1 int,link1 int);
create table test2 (id2 int, field2 varchar(5));
where link1 is a foreign key of test1 that should be linked to id2 of test2
Now when I execute the following query:
select id1,field2,link1 from test1,test2 where test1.link1=test2.id2
union
select id1,NULL,link1 from test1;
I always get the following error:
Each UNION query must have identical target types.
Why this error, and what does it mean?
TIA
Wim Ceulemans - wim(dot)ceulemans(at)nice(dot)be
Nice Software Solutions
Eglegemweg 3, 2811 Hombeek - Belgium
Tel +32(0)15 41 29 53 - Fax +32(0)15 41 29 54
From | Date | Subject | |
---|---|---|---|
Next Message | Anatoly K. Lasareff | 1998-11-27 10:20:21 | Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs? |
Previous Message | Anatoly K. Lasareff | 1998-11-27 07:27:42 | Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs? |