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.]
