Thread: 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!
On Tue, Dec 11, 2007 at 10:29:04AM +0800, bookman bookman wrote: > 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? NULL values are encoded as an unquoted \N by default in PG. You've got a few ways of fixing things then. Tell MS-SQL to do the same, write a sed script to do the translation, or use the "NULL AS 'NULL'" option in the COPY command. Sam
bookman bookman escribió: > 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? No, it doesn't mean that. It means that you are trying to insert an empty string. Try this: insert into T_Admin(name,key,regDate,isLock,realName) values('aaa','aaa',NULL,'1','aaa'); Similarly, the NULL timestamp column in the file you give to COPY should not contain empty quotes. -- Alvaro Herrera http://www.advogato.org/person/alvherre "Crear es tan difícil como ser libre" (Elsa Triolet)
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@gmail.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 >
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.
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. > >