inserting a NULL timestamp - Mailing list pgsql-novice

From Robert Poor
Subject inserting a NULL timestamp
Date
Msg-id AANLkTikDmJ=64xkpSi+8+bCd3=3vsgQiJRniHypVGGH+@mail.gmail.com
Whole thread Raw
Responses Re: inserting a NULL timestamp  (Steve Crawford <scrawford@pinpointresearch.com>)
List 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.]

pgsql-novice by date:

Previous
From: "Rob Richardson"
Date:
Subject: Re: Adding a stored number of minutes to a stored time
Next
From: Steve Crawford
Date:
Subject: Re: inserting a NULL timestamp