pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy command
8+ messages / 6 participants
[nested] [flat]
* copy command
@ 2002-04-25 14:53 Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
0 siblings, 2 replies; 8+ messages in thread
From: Duncan Adams (DNS) @ 2002-04-25 14:53 UTC (permalink / raw)
To: pgsql-sql
hi all
how do you use the copy command
call me dumb, i have RTFM but i don't seem to get it.
an exstract from my dns.txt file.
aeomc04 10.121.4.157
somcmd1 10.121.23.57
aeomc05 10.121.4.162
gsmomc6 10.121.4.154
wire_dev=> copy dns_ip from dns.txt
wire_dev-> ;
ERROR: parser: parse error at or near "dns"
wire_dev=> copy dns_ip from /database/pgsql/dns.txt
wire_dev-> ;
ERROR: parser: parse error at or near "/"
wire_dev=>
wire_dev=> \d dns_ip
Table "dns_ip"
Attribute | Type | Modifier
-----------+-----------------------+----------
dns | character varying(32) |
ip | character varying(32) |
thanx
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: copy command
@ 2002-04-25 18:34 ARP <arnaud.mlist1@free.fr>
parent: Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
1 sibling, 0 replies; 8+ messages in thread
From: ARP @ 2002-04-25 18:34 UTC (permalink / raw)
To: Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>; pgsql-sql
I think you need the quote around the file name since it's not a keyword and it's text.
Then your dns.txt file needs to be formated correctly, especially concerning the separator : by default I think TABs are used and \N is used to say NULL. You can go aver that with USING DELIMITERS ';' for example and WITH NULL AS '' if your dns.txt look like this :
aeomc04;10.121.4.157
somcmd1;10.121.23.57
aeomc05;10.121.4.162
gsmomc6;10.121.4.154
hope this helps
Arnaud
----- Original Message -----
From: "Duncan Adams (DNS)" <duncan.adams@vcontractor.co.za>
To: "postgres sql list" <pgsql-sql@postgresql.org>
Sent: Thursday, April 25, 2002 4:53 PM
Subject: [SQL] copy command
hi all
how do you use the copy command
call me dumb, i have RTFM but i don't seem to get it.
an exstract from my dns.txt file.
aeomc04 10.121.4.157
somcmd1 10.121.23.57
aeomc05 10.121.4.162
gsmomc6 10.121.4.154
wire_dev=> copy dns_ip from dns.txt
wire_dev-> ;
ERROR: parser: parse error at or near "dns"
wire_dev=> copy dns_ip from /database/pgsql/dns.txt
wire_dev-> ;
ERROR: parser: parse error at or near "/"
wire_dev=>
wire_dev=> \d dns_ip
Table "dns_ip"
Attribute | Type | Modifier
-----------+-----------------------+----------
dns | character varying(32) |
ip | character varying(32) |
thanx
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: copy command
@ 2002-04-26 00:39 svilen@iname.com
parent: Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
1 sibling, 0 replies; 8+ messages in thread
From: svilen@iname.com @ 2002-04-26 00:39 UTC (permalink / raw)
To: Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>; +Cc: pgsql-sql
Hello!
You only need to put an "\" in front of "copy". It is an internal command. And you don't need ";" at the end
it would be:
bpsimple=# \copy dns_ip from 'dns.txt'
\.
bpsimple=# SELECT * FROM dns_ip;
dns | ip
---------+---------------
aeomc04 | 10.121.4.157
somcmd1 | 10.121.23.57
aeomc05 | 10.121.4.162
gsmomc6 | 10.121.4.154
(4 rows)
\. is the response and tells you that it worked.
I actually tried it with "\copy dns_ip from dns.txt", without the
quotes and it also worked perfectly.
Svilen Dyakovski
On 25 Apr 02, at 16:53, Duncan Adams (DNS) wrote:
>
> wire_dev=> copy dns_ip from dns.txt
> wire_dev-> ;
> ERROR: parser: parse error at or near "dns"
> wire_dev=> copy dns_ip from /database/pgsql/dns.txt
> wire_dev-> ;
> ERROR: parser: parse error at or near "/"
> wire_dev=>
> wire_dev=> \d dns_ip
> Table "dns_ip"
> Attribute | Type | Modifier
> -----------+-----------------------+----------
> dns | character varying(32) |
> ip | character varying(32) |
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* COPY command
@ 2004-01-07 06:19 azwa@nc.com.my
0 siblings, 0 replies; 8+ messages in thread
From: azwa@nc.com.my @ 2004-01-07 06:19 UTC (permalink / raw)
To: pgsql-sql
Hi,
i try to load a file into temporary table but it gives me an error . do
u have any idea/solutions ?? the command that i've running as below :
dwnc=# copy biosadm.custdo_temp
dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'
dwnc-# WITH DELIMITER ',' ;
ERROR: copy: line 141, Extra data after last expected column
FYI, my file was in comma delimiter type (csv) . My table structure as
following :
dwnc-# \dbiosadm.custdo_temp
Table "biosadm.custdo_temp"
Column | Type | Modifiers
----------+-----------------------+-----------
dono | character varying(13) |
dodate | date |
custname | character varying(70) |
custlo | character varying(40) |
attnto | character varying(80) |
Please guide me . thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* COPY command
@ 2004-01-07 08:35 azwa@nc.com.my
0 siblings, 0 replies; 8+ messages in thread
From: azwa@nc.com.my @ 2004-01-07 08:35 UTC (permalink / raw)
To: pgsql-sql
Hi,
i try to load a file into temporary table but it gives me an error . do u
have any idea/solutions ?? the command that i've running as below :
dwnc=# copy biosadm.custdo_temp
dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'
dwnc-# WITH DELIMITER ',' ;
ERROR: copy: line 141, Extra data after last expected column
FYI, my file was in comma delimiter type (csv) . My table structure as
following :
dwnc-# \dbiosadm.custdo_temp
Table "biosadm.custdo_temp"
Column | Type | Modifiers
----------+-----------------------+-----------
dono | character varying(13) |
dodate | date |
custname | character varying(70) |
custlo | character varying(40) |
attnto | character varying(80) |
Please guide me . thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COPY command
@ 2004-01-09 03:06 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 0 replies; 8+ messages in thread
From: Gregory S. Williamson @ 2004-01-09 03:06 UTC (permalink / raw)
To: azwa@nc.com.my; pgsql-sql
This sounds as if the last character in each line might be a delimiter (a ",") which is standard for data unloaded from some sources; if this is the case try removing it and your data should load. Or you may have a comma in a character field and that is throwing off the count for the line in question ?
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: azwa@nc.com.my [mailto:azwa@nc.com.my]
Sent: Tue 1/6/2004 10:19 PM
To: pgsql-sql@postgresql.org
Cc:
Subject: [SQL] COPY command
Hi,
i try to load a file into temporary table but it gives me an error . do
u have any idea/solutions ?? the command that i've running as below :
dwnc=# copy biosadm.custdo_temp
dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'
dwnc-# WITH DELIMITER ',' ;
ERROR: copy: line 141, Extra data after last expected column
FYI, my file was in comma delimiter type (csv) . My table structure as
following :
dwnc-# \dbiosadm.custdo_temp
Table "biosadm.custdo_temp"
Column | Type | Modifiers
----------+-----------------------+-----------
dono | character varying(13) |
dodate | date |
custname | character varying(70) |
custlo | character varying(40) |
attnto | character varying(80) |
Please guide me . thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COPY command
@ 2004-01-09 04:11 azwa@nc.com.my
0 siblings, 1 reply; 8+ messages in thread
From: azwa@nc.com.my @ 2004-01-09 04:11 UTC (permalink / raw)
To: pgsql-sql
Hi ,
yes now i can do the copy after replace comma delimiter with tab delimiter
which is default copy with delimiter . btw i've another
question : how do we run the COPY command from file which has several null
columns in each field.
thanks.
example :
dwnc=# copy biosadm.custinv_temp
dwnc-# from '/home/bios/customer_data/CustomerInvoice_5Dec03_tab.txt' ;
ERROR: copy: line 1, Missing data for column "subsidiary"
p/s: my subsidiary column has several null data.
"Gregory S. Williamson" <gsw@globexplorer.com>
07:06 PM PST Yesterday
To: <azwa@nc.com.my>, <pgsql-sql@postgresql.org>
cc:
Subject: RE: [SQL] COPY command
This sounds as if the last character in each line might be a delimiter (a
",") which is standard for data unloaded from some sources; if this is the
case try removing it and your data should load. Or you may have a comma in
a character field and that is throwing off the count for the line in
question ?
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: azwa@nc.com.my [mailto:azwa@nc.com.my]
Sent: Tue 1/6/2004 10:19 PM
To: pgsql-sql@postgresql.org
Cc:
Subject: [SQL] COPY command
Hi,
i try to load a file into temporary table but it gives me an error . do
u have any idea/solutions ?? the command that i've running as below :
dwnc=# copy biosadm.custdo_temp
dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'
dwnc-# WITH DELIMITER ',' ;
ERROR: copy: line 141, Extra data after last expected column
FYI, my file was in comma delimiter type (csv) . My table structure as
following :
dwnc-# \dbiosadm.custdo_temp
Table "biosadm.custdo_temp"
Column | Type | Modifiers
----------+-----------------------+-----------
dono | character varying(13) |
dodate | date |
custname | character varying(70) |
custlo | character varying(40) |
attnto | character varying(80) |
Please guide me . thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COPY command
@ 2004-01-09 04:49 Michael Glaesemann <grzm@myrealbox.com>
parent: azwa@nc.com.my
0 siblings, 0 replies; 8+ messages in thread
From: Michael Glaesemann @ 2004-01-09 04:49 UTC (permalink / raw)
To: azwa@nc.com.my; +Cc: pgsql-sql
On Jan 9, 2004, at 1:11 PM, azwa@nc.com.my wrote:
> how do we run the COPY command from file which has several null
> columns in each field.
> thanks.
>
> example :
>
> dwnc=# copy biosadm.custinv_temp
> dwnc-# from '/home/bios/customer_data/CustomerInvoice_5Dec03_tab.txt' ;
> ERROR: copy: line 1, Missing data for column "subsidiary"
Just like you specify the delimiter, you need to specify the value for
NULL. For example, if your *.txt file has a line with a "missing" value
(such as the space between 'bar' and 'foofoo')
foo bar foofoo barbar
I usually use 'NULL' as NULL, so the line would be
foo bar NULL foofoo barbar
then use the COPY command with NULL AS 'NULL'
Check out the following link for detailed info on the COPY command:
<http://www.postgresql.org/docs/current/static/sql-copy.html;
HTH
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-01-09 04:49 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-25 14:53 copy command Duncan Adams (DNS) <duncan.adams@vcontractor.co.za>
2002-04-25 18:34 ` ARP <arnaud.mlist1@free.fr>
2002-04-26 00:39 ` svilen@iname.com
2004-01-07 06:19 COPY command azwa@nc.com.my
2004-01-07 08:35 COPY command azwa@nc.com.my
2004-01-09 03:06 Re: COPY command Gregory S. Williamson <gsw@globexplorer.com>
2004-01-09 04:11 Re: COPY command azwa@nc.com.my
2004-01-09 04:49 ` Re: COPY command Michael Glaesemann <grzm@myrealbox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox