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

Re: timestamp and null value

From: John McKown <jmckown(at)prodigy(dot)net>
To: PostgreSQL novice mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: timestamp and null value
Date: 2000-07-28 00:17:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
I don't know of a way to do what you want just changing the "create"
Does the script produce a file which is then used? If so, how about
writing another script to "fix" the output of the script in error. I think
that you could simply use sed as follows:

script_creating_inserts | sed "s/(''\(NULL/g" >changed.inserts

Or you can do the following:

create table entite(temp text, 
	dt_suppression timestamp,
	dt_creation timestamp null);

After the inserts have been done, use psql and do the following (or put in
in a script)

update entite set dt_creation=temp::timestamp where dt_creation is null
and temp != '';

Another possibility is to have two tables, but I think that just
complicates thing. But in case you're curious:

create table entite(creation text, suppression timestamp);
create tabel entite2(dt_creation timestamp, dt_suppression timestamp);

... do the inserts into the "entite" table. When finished, run psql
similiar to:

insert into entite2 
	select creation::timestamp, suppression from entite 
		where creation!=''
	union select NULL, suppression from entite
		where creation=''; 

Hope this helps, or gives you some ideas.


On Thu, 27 Jul 2000, Nicolas Kizilian wrote:

>  Hi,
> I don't succeed in placing null values in a timestamp column.
> This is an example :
> create table entite (dt_creation timestamp null , dt_suppression timestamp);
> insert into entite values ('', '1999-11-08');
> ERROR:  Bad timestamp external representation ''
> Ok, that's my problem.  Please note the fact that my "insert" command line
> could not be modified, cause it's provided by a script, and I cannot modify
> the script. I can't type for example :
> insert into entite (dt_suppression) values ('1999-11-08');
> wich would work.
> I can't write it by hand, because I have more than 8,000,000 entries.
>  So if i can solve this problem just by modifying my "create table" line, it
> would be great.
> Thanks,
>  best regards,
> Nicolas

In response to

pgsql-novice by date

Next:From: Thomas LockhartDate: 2000-07-28 02:14:21
Subject: Re: 4 billion record limit?
Previous:From: Chris BitmeadDate: 2000-07-28 00:03:36
Subject: Re: 4 billion record limit?

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