Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Assume this schema (looks best with fixed width font):

# \d service_bills
                                         Table "public.service_bills"
       Column       |            Type             |
 id                 | integer                     | not null default
 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


[* This is the beginning of a fancier query that will only insert
records that don't yet exist.]


pgsql-novice by date

Next:From: Steve CrawfordDate: 2011-03-29 17:09:54
Subject: Re: inserting a NULL timestamp
Previous:From: Rob RichardsonDate: 2011-03-28 13:36:11
Subject: Re: Adding a stored number of minutes to a stored time

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group