pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCast NULL into Timestamp?
5+ messages / 5 participants
[nested] [flat]
* Cast NULL into Timestamp?
@ 2004-12-10 19:24 Wei Weng <wweng@kencast.com>
2004-12-11 00:10 ` Re: Cast NULL into Timestamp? Steve Crawford <scrawford@pinpointresearch.com>
2004-12-11 00:48 ` Re: Cast NULL into Timestamp? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-12-11 05:11 ` Re: Cast NULL into Timestamp? Josh Berkus <josh@agliodbs.com>
2004-12-11 17:10 ` Re: Cast NULL into Timestamp? Frank Bax <fbax@sympatico.ca>
0 siblings, 4 replies; 5+ messages in thread
From: Wei Weng @ 2004-12-10 19:24 UTC (permalink / raw)
To: pgsql-sql
I have a table
create table temp
(
tempdate timestamp,
tempname varchar(10)
);
And I tried to insert the following:
insert into table temp (tempname, tempdate)
select distinct 'tempname', null from some_other_relevant_table;
And I got an error that says "column "tempdate" is of type timestamp ... but
expression is of type text... will need to rewrite or cast the expression".
I really need the distinct. Is there anyway to cast this NULL into a
timestamp or any other workarounds?
Thanks
Wei
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cast NULL into Timestamp?
2004-12-10 19:24 Cast NULL into Timestamp? Wei Weng <wweng@kencast.com>
@ 2004-12-11 00:10 ` Steve Crawford <scrawford@pinpointresearch.com>
3 siblings, 0 replies; 5+ messages in thread
From: Steve Crawford @ 2004-12-11 00:10 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; pgsql-sql
On Friday 10 December 2004 11:24 am, Wei Weng wrote:
> I have a table
>
> create table temp
> (
> tempdate timestamp,
> tempname varchar(10)
> );
>
> And I tried to insert the following:
>
> insert into table temp (tempname, tempdate)
> select distinct 'tempname', null from some_other_relevant_table;
>
> And I got an error that says "column "tempdate" is of type
> timestamp ... but expression is of type text... will need to
> rewrite or cast the expression".
>
> I really need the distinct. Is there anyway to cast this NULL into
> a timestamp or any other workarounds?
How about:
insert into table temp (tempname)
select distinct 'tempname' from some_other_relevant_table;
Unless there's something you have left out in describing your setup
this will leave the tempdate column null.
Cheers,
Steve
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cast NULL into Timestamp?
2004-12-10 19:24 Cast NULL into Timestamp? Wei Weng <wweng@kencast.com>
@ 2004-12-11 00:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2004-12-11 00:48 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
On Fri, 10 Dec 2004, Wei Weng wrote:
> I have a table
>
> create table temp
> (
> tempdate timestamp,
> tempname varchar(10)
> );
>
> And I tried to insert the following:
>
> insert into table temp (tempname, tempdate)
> select distinct 'tempname', null from some_other_relevant_table;
>
> And I got an error that says "column "tempdate" is of type timestamp ... but
> expression is of type text... will need to rewrite or cast the expression".
>
> I really need the distinct. Is there anyway to cast this NULL into a
> timestamp or any other workarounds?
CAST(NULL as timestamp) should work.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cast NULL into Timestamp?
2004-12-10 19:24 Cast NULL into Timestamp? Wei Weng <wweng@kencast.com>
@ 2004-12-11 05:11 ` Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-12-11 05:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: Wei Weng <wweng@kencast.com>
Wei,
> insert into table temp (tempname, tempdate)
> select distinct 'tempname', null from some_other_relevant_table;
I don't think you're reporting the error exactly as it happened. Try cutting
and pasting your actual PSQL session into your e-mail.
Perhaps you are mixing up the column order?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cast NULL into Timestamp?
2004-12-10 19:24 Cast NULL into Timestamp? Wei Weng <wweng@kencast.com>
@ 2004-12-11 17:10 ` Frank Bax <fbax@sympatico.ca>
3 siblings, 0 replies; 5+ messages in thread
From: Frank Bax @ 2004-12-11 17:10 UTC (permalink / raw)
To: pgsql-sql; +Cc: Wei Weng <wweng@kencast.com>
At 12:11 AM 12/11/04, Josh Berkus wrote:
>Wei,
>
> > insert into table temp (tempname, tempdate)
> > select distinct 'tempname', null from some_other_relevant_table;
>
>I don't think you're reporting the error exactly as it happened. Try cutting
>and pasting your actual PSQL session into your e-mail.
>
>Perhaps you are mixing up the column order?
A copy/paste certainly would have been helpful in this case. There are
several problems with the above query.
1) the keyword 'table' is not part of insert command.
2) If 'tempname' is a fieldname, it should not have single quotes.
3) NULL doesn't seem to work as expression in select.
If (3) is the real problem here, then either solution proposed by
Steve/Stephan will work.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-12-11 17:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-10 19:24 Cast NULL into Timestamp? Wei Weng <wweng@kencast.com>
2004-12-11 00:10 ` Steve Crawford <scrawford@pinpointresearch.com>
2004-12-11 00:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-12-11 05:11 ` Josh Berkus <josh@agliodbs.com>
2004-12-11 17:10 ` Frank Bax <fbax@sympatico.ca>
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