Re: timestamp and null value

Nicolas,
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.

John

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