From: | Robert Poor <rdpoor(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | inserting a NULL timestamp |
Date: | 2011-03-29 17:03:30 |
Message-ID: | AANLkTikDmJ=64xkpSi+8+bCd3=3vsgQiJRniHypVGGH+@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Assume this schema (looks best with fixed width font):
# \d service_bills
Table "public.service_bills"
Column | Type |
Modifiers
--------------------+-----------------------------+------------------------------------------------------------
id | integer | not null default
nextval('service_bills_id_seq'::regclass)
metered_service_id | integer |
quantity | numeric(10,5) |
cost | numeric(10,5) |
start_time | timestamp without time zone |
end_time | timestamp without time zone |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
I know I can insert a NULL timestamp, as evidenced by:
# INSERT INTO service_bills (cost,start_time) (SELECT 2.3 AS cost,
NULL AS start_time);
# select * from service_bills order by id desc limit 1;
id | metered_service_id | quantity | cost | start_time |
end_time | created_at | updated_at
------+--------------------+----------+---------+------------+----------+------------+------------
1407 | | | 2.30000 | |
| |
But I need to do it "the hard way" [* see below if you must know]:
# INSERT INTO service_bills (cost,start_time)
SELECT candidates.*
FROM (SELECT 2.3 AS cost, NULL AS start_time) AS candidates;
This, though, raises an error:
# ERROR: failed to find conversion function from unknown to timestamp
without time zone
So: What's so different about the second version? Is there a way to
get the query to accept a NULL as a timestamp (as it did in the first
version)?
Thanks.
[* This is the beginning of a fancier query that will only insert
records that don't yet exist.]
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2011-03-29 17:09:54 | Re: inserting a NULL timestamp |
Previous Message | Rob Richardson | 2011-03-28 13:36:11 | Re: Adding a stored number of minutes to a stored time |