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

Thank you!

2007/12/12, Obe, Regina <robe(dot)dnd(at)cityofboston(dot)gov>:
> Actually what you are doing below is trying to stuff '' in a timestamp
> field.
>
> Keep in mind '' and NULL are not the same. '' is invalid for timestamp
> where as NULL is fine. Your example should be
> insert into T_Admin(name,key,regDate,isLock,realName)
> values('aaa','aaa',NULL,'1','aaa');
>
> I think the bcp is trying to insert 'NULL' instead of NULL.
>
> According to the docs - looks like you can tell copy that
> http://www.postgresql.org/docs/techdocs.15
>
> So my guess is you should do
> copy t_admin from "/home/postgres/data/admin.txt" USING DELIMITERS '\t'
> WITH NULL As 'NULL'
>
> Hope that helps,
> Regina
>
>
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of bookman bookman
> Sent: Monday, December 10, 2007 9:29 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] How can I insert NULL into column with the type of
> timestamp?
>
> 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
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
>
>