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

Thank you!


2007/12/12, Obe, Regina <robe.dnd@cityofboston.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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of bookman bookman
> Sent: Monday, December 10, 2007 9:29 PM
> To: pgsql-general@postgresql.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.
>
>