Re: How can I insert NULL into column with the type of timestamp?

Hello

use simply NULL

postgres=# create table bb(a timestamp, b integer);
CREATE TABLE
postgres=# insert into bb values(null, null);
INSERT 0 1
postgres=# insert into bb values(null, 10);
INSERT 0 1
postgres=# insert into bb values(current_timestamp, 10);
INSERT 0 1
postgres=# copy bb to stdout;
\N \N
\N 10
2007-12-12 16:48:28.122776 10
or
postgres=# copy bb to stdout with null as 'null';
null null
null 10
2007-12-12 16:48:28.122776 10

Regards
Pavel Stehule

On 11/12/2007, bookman bookman <bookmanb(at)gmail(dot)com> wrote:
> H i ,
>
> I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I
> use bcp to export a table named admin in sqlserver to a text file:
>
> --table T_admin
> id name key regDate
> isLock realName
> 1 rison 998877 2007-08-27 10:24:57 False admin
> 2 lijun 778899 NULL False NULL
> 3 guanliyuan 111111 2007-11-05 10:30:08 False myAdmin
>
> --admin.txt
> id name key regDate
> isLock realname
> 1 ris 998877 2007-08-27 10:24:57.000 0 admin
> 2 lij 778899 0
> 3 guanliyuan 111111 2007-11-05 10:30:08.813 0 myAdmin
>
> I created a table in postgresql,and I use "copy" to import datas
>
> create table T_Admin(
> adminID serial not null primary key,
> name varchar(30) null,
> key varchar(30) null,
> regDate timestamp null,
> isLock bool null,
> realName varchar(30) null
> )
>
> copy admin from "/home/postgres/data/admin.txt"
>
> Then error occured:
> error:invalid input syntax for type timestamp:""
> context:copy T_Admin ,line 2,column regDate:""
> It seemed that the column REGDATE cannot accept a NULL.I tested it use:
> insert into T_Admin(name,key,regDate,isLock,realName)
> values('aaa','aaa','','1','aaa');
> The same error occured.
>
> So it means that the column with type timestamp cannot accept a NULL
> .Is there any way I can tansfer this table into postgre?How can i deal
> with NULL in this case?
> Thank you!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>