inserting a NULL timestamp

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

Responses

Browse pgsql-novice by date

  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