Postgres Pro
Downloads
Home   >   mailing lists

Thread: NULL TIMESTAM problem

NULL TIMESTAM problem

From
"Enrique Sánchez"
Date:
Hi! I'm new in Postgres.

I nedd to fill a database table x from a file With the COPY command an the
delimiter '*'.
This  table  has a timestamp null column (I declared like: ' birthday
timestamp NULL' ).

But when I try to insert NULL values(specified in the file), postgres throw
an error.


I don't know how can I specify this NULL value wkthout an '\N' character.


Thanks
Enrique.

Re: NULL TIMESTAM problem

From
Michael Fuhr
Date:
On Thu, Mar 09, 2006 at 12:49:14PM -0600, Enrique Sánchez wrote:
> I nedd to fill a database table x from a file With the COPY command an the
> delimiter '*'.
> This  table  has a timestamp null column (I declared like: ' birthday
> timestamp NULL' ).
>
> But when I try to insert NULL values(specified in the file), postgres throw
> an error.

What's the error?  How are the NULL values specified in the file?

> I don't know how can I specify this NULL value wkthout an '\N' character.

See the COPY documentation -- COPY has a NULL option that allows
you to specify what string should be interpreted as NULL.  Recent
versions also have a CSV option that interprets empty strings as
NULL.

http://www.postgresql.org/docs/8.1/interactive/sql-copy.html

(Use the documentation for whatever version you're running.)

--
Michael Fuhr

Re: NULL TIMESTAM problem

From
"Enrique Sánchez"
Date:
OK.  the error was that: postgress didn't recognize which was the
NULLstring, because the format file was: value1*value2**value4, where the
delimiter is: * .

I learnd the default NULL string is '\N', but as you told me, you can
specify this in the command definition.

Thanks Michael.


>From: Michael Fuhr <mike@fuhr.org>
>To: Enrique S�nchez <sancheztru@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] NULL TIMESTAM problem
>Date: Thu, 9 Mar 2006 12:26:10 -0700
>
>On Thu, Mar 09, 2006 at 12:49:14PM -0600, Enrique S�nchez wrote:
> > I nedd to fill a database table x from a file With the COPY command an
>the
> > delimiter '*'.
> > This  table  has a timestamp null column (I declared like: ' birthday
> > timestamp NULL' ).
> >
> > But when I try to insert NULL values(specified in the file), postgres
>throw
> > an error.
>
>What's the error?  How are the NULL values specified in the file?
>
> > I don't know how can I specify this NULL value wkthout an '\N'
>character.
>
>See the COPY documentation -- COPY has a NULL option that allows
>you to specify what string should be interpreted as NULL.  Recent
>versions also have a CSV option that interprets empty strings as
>NULL.
>
>http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>
>(Use the documentation for whatever version you're running.)
>
>--
>Michael Fuhr
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

Re: NULL TIMESTAM problem

From
"Guy Rouillier"
Date:
Enrique Sánchez wrote:
> Hi! I'm new in Postgres.
>
> I nedd to fill a database table x from a file With the COPY command
> an the delimiter '*'.
> This  table  has a timestamp null column (I declared like: ' birthday
> timestamp NULL' ).
>
> But when I try to insert NULL values(specified in the file), postgres
> throw an error.
>
>
> I don't know how can I specify this NULL value wkthout an '\N'
> character.

I created a table t1 with 3 columns, all nullable:

    f1 int
    f2 timestamp
    f3 int

Using the following input file t1.csv:

    5,NULL,7
    8,NULL,10

The following COPY command successfully put those rows in the table, with f2 null:

    copy t1 (f1, f2, f3)
    from 't1.csv'
    null as 'NULL'
    csv;

--
Guy Rouillier