Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of bookman bookman
Sent: Monday, December 10, 2007 9:29 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject:  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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux