Re: [SQL] Cast NULL into Timestamp?

2004-12-11 Thread Frank Bax
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. 

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [SQL] Cast NULL into Timestamp?

2004-12-10 Thread Steve Crawford
On Friday 10 December 2004 11:24 am, Wei Weng wrote:
> I have a table
>
> create table temp
> (
>   tempdatetimestamp,
>   tempnamevarchar(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


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


Re: [SQL] Cast NULL into Timestamp?

2004-12-10 Thread Stephan Szabo
On Fri, 10 Dec 2004, Wei Weng wrote:

> I have a table
>
> create table temp
> (
>   tempdatetimestamp,
>   tempnamevarchar(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.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [SQL] Cast NULL into Timestamp?

2004-12-10 Thread Josh Berkus
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


[SQL] Cast NULL into Timestamp?

2004-12-10 Thread Wei Weng
I have a table
create table temp
(
tempdatetimestamp,
tempnamevarchar(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html