pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY
11+ messages / 8 participants
[nested] [flat]
* COPY
@ 2000-03-31 15:28 Wierzbicki, Ralf <rwierzbicki@stryker.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Wierzbicki, Ralf @ 2000-03-31 15:28 UTC (permalink / raw)
To: pgsql-admin
Hello,
I think I asked this question before but will try again.
I deal a lot with importing text files into my psql database and
quite often there are bad records in those files. 'COPY' will
abort the process whenever it encounters bad data. Is there any
way to make 'COPY' skip bad records and continue with the import
process rather than abort the entire thing?
Thanks in advance.
--
Ralf
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: COPY
@ 2000-03-31 18:05 Edward Chase <echase@homer.providence.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Edward Chase @ 2000-03-31 18:05 UTC (permalink / raw)
To: pgsql-admin
Not knowing exactly what your doing and what psql is considering "bad",
have you thought about pre-processing your files first and eliminating
these bad records before importing them into your database?
At 10:28 AM 3/31/2000 -0500, Ralf wrote:
>
>Hello,
>
> I think I asked this question before but will try again.
>I deal a lot with importing text files into my psql database and
>quite often there are bad records in those files. 'COPY' will
>abort the process whenever it encounters bad data. Is there any
>way to make 'COPY' skip bad records and continue with the import
>process rather than abort the entire thing?
>
>Thanks in advance.
---------------------------------------------------------------
Edward F. Chase III | echase@providence.edu
Providence College | http://www.providence.edu
Computer Services | http://studentweb.providence.edu
Providence, RI 02918 |
^ permalink raw reply [nested|flat] 11+ messages in thread
* Copy
@ 2001-04-30 21:53 John Coers <coers@intrinsity.com>
0 siblings, 1 reply; 11+ messages in thread
From: John Coers @ 2001-04-30 21:53 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Does a COPY FROM block? In case I am using the wrong terminology,
what I need to know is if I have multiple clients performing COPY FROM...PQputline()
using asynchronous connections, will I lose data? Will the server simply execute
them serially?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Copy
@ 2001-04-30 22:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Coers <coers@intrinsity.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2001-04-30 22:16 UTC (permalink / raw)
To: coers@intrinsity.com; +Cc: pgsql-general@postgresql.org
John Coers <coers@intrinsity.com> writes:
> Does a COPY FROM block? In case I am using the wrong terminology,
> what I need to know is if I have multiple clients performing COPY FROM...PQputline()
> using asynchronous connections, will I lose data?
No.
> Will the server simply execute them serially?
They'll be executed in parallel, just the same as if each client had
done a bunch of INSERTs wrapped in a BEGIN/END block. You'd only see
problems if clients inserted conflicting data (eg, identical keys in
a column with a UNIQUE index). You may care to read the documentation
about multi-version concurrency control and locking.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Copy
@ 2001-05-01 15:49 John Coers <coers@intrinsity.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: John Coers @ 2001-05-01 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org
Tom Lane wrote:
>
> John Coers <coers@intrinsity.com> writes:
> > Does a COPY FROM block? In case I am using the wrong terminology,
> > what I need to know is if I have multiple clients performing COPY FROM...PQputline()
> > using asynchronous connections, will I lose data?
>
> No.
>
> > Will the server simply execute them serially?
>
> They'll be executed in parallel, just the same as if each client had
> done a bunch of INSERTs wrapped in a BEGIN/END block. You'd only see
> problems if clients inserted conflicting data (eg, identical keys in
> a column with a UNIQUE index). You may care to read the documentation
> about multi-version concurrency control and locking.
>
> regards, tom lane
hmmmm...I get best performance with N=2 and force the clients to keep trying to
get a connection. If I leave the default of N=32 the machine gets bogged down
to a complete standstill by 32 copies of 15k rows of data. The CPU doesn't seem
stressed -- what may be causing the bottleneck?
--
John Coers Intrinsity, Inc.
coers@intrinsity.com Austin, Texas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Copy
@ 2001-05-01 22:05 John Coers <coers@intrinsity.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: John Coers @ 2001-05-01 22:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org
Tom Lane wrote:
>
> John Coers <coers@intrinsity.com> writes:
> > Does a COPY FROM block? In case I am using the wrong terminology,
> > what I need to know is if I have multiple clients performing COPY FROM...PQputline()
> > using asynchronous connections, will I lose data?
>
> No.
>
> > Will the server simply execute them serially?
>
> They'll be executed in parallel, just the same as if each client had
> done a bunch of INSERTs wrapped in a BEGIN/END block. You'd only see
> problems if clients inserted conflicting data (eg, identical keys in
> a column with a UNIQUE index). You may care to read the documentation
> about multi-version concurrency control and locking.
Do I need to do a PQconnectStart(),PQconnectPoll connection to make the server process
the COPY's in parallel, or does that just affect the client-side thread? How about a
PQconnectdb() and then PQsetnonblocking()?
Can I just do a PQconnectdb()?
I am unclear on how these different connection types affect the client threads vs. the server threads.
--
John Coers Intrinsity, Inc.
coers@intrinsity.com Austin, Texas
^ permalink raw reply [nested|flat] 11+ messages in thread
* copy
@ 2004-12-01 12:33 H.J. Sanders <hjs@rmax.nl>
0 siblings, 1 reply; 11+ messages in thread
From: H.J. Sanders @ 2004-12-01 12:33 UTC (permalink / raw)
To: pgsql-admin
Hello.
We are moving a database from informix to Postgresql.
To do so we do an unload from Informix and copy to postgresql.
Informix unloads with a delimiter also after the last field in a record.
This is no problem when doing a copy to Postgresql 7.2
You get a warning (to many fields) but the copy process continues.
In 7.4 however the copy stops .
Does anyone know how to proceed the copy or a fast way to remove the last
character
from the unload files.
They are anyhow to big to handle with vi.
Many thanks
Henk Sanders
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: copy
@ 2004-12-01 13:01 Geoffrey <esoteric@3times25.net>
parent: H.J. Sanders <hjs@rmax.nl>
0 siblings, 0 replies; 11+ messages in thread
From: Geoffrey @ 2004-12-01 13:01 UTC (permalink / raw)
To: H.J. Sanders <hjs@rmax.nl>; +Cc: pgsql-admin
H.J. Sanders wrote:
> Hello.
>
> We are moving a database from informix to Postgresql.
>
> To do so we do an unload from Informix and copy to postgresql.
>
> Informix unloads with a delimiter also after the last field in a record.
>
> This is no problem when doing a copy to Postgresql 7.2
> You get a warning (to many fields) but the copy process continues.
>
> In 7.4 however the copy stops .
>
> Does anyone know how to proceed the copy or a fast way to remove the last
> character
> from the unload files.
>
> They are anyhow to big to handle with vi.
sed 's/.$//' file > newfile
Always test before using, it's off the top of my head..
--
Until later, Geoffrey
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: copy
@ 2004-12-01 15:03 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: CHRIS HOOVER @ 2004-12-01 15:03 UTC (permalink / raw)
To: H J Sanders <hjs@rmax.nl>; +Cc: pgsql-admin
cat <file> | sed '<delimiter>$//' > <file>.new
this should remove the last delimiter
------------------( Forwarded letter 1 follows )---------------------
Date: Wed, 1 Dec 2004 13:33:26 +0100
To: pgsql-admin@postgresql.org.comp
From: H.J.Sanders[hjs]@rmax.nl.comp
Sender: pgsql-admin-owner+m15591@postgresql.org.comp
Subject: [ADMIN] copy
Hello.
We are moving a database from informix to Postgresql.
To do so we do an unload from Informix and copy to postgresql.
Informix unloads with a delimiter also after the last field in a record.
This is no problem when doing a copy to Postgresql 7.2
You get a warning (to many fields) but the copy process continues.
In 7.4 however the copy stops .
Does anyone know how to proceed the copy or a fast way to remove the last
character
from the unload files.
They are anyhow to big to handle with vi.
Many thanks
Henk Sanders
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: copy
@ 2004-12-01 15:13 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: CHRIS HOOVER @ 2004-12-01 15:13 UTC (permalink / raw)
To: CHRIS.HOOVER@companiongroup.com; +Cc: pgsql-admin@postgresql.org, H J Sanders <hjs@rmax.nl>
fat fingered that one:
cat < file> | sed 's/<delimiter>$//'> <file>.new
------------------( Forwarded letter 1 follows )---------------------
Date: Wed, 1 Dec 2004 10:03:00 -0500
To: H.J.Sanders[hjs]@rmax.nl.comp
Cc: pgsql-admin@postgresql.org.comp
From: CHRIS.HOOVER@companiongroup.com.comp
Sender: pgsql-admin-owner+m15600@postgresql.org.comp
Subject: Re: [ADMIN] copy
cat <file> | sed '<delimiter>$//' > <file>.new
this should remove the last delimiter
------------------( Forwarded letter 1 follows )---------------------
Date: Wed, 1 Dec 2004 13:33:26 +0100
To: pgsql-admin@postgresql.org.comp
From: H.J.Sanders[hjs]@rmax.nl.comp
Sender: pgsql-admin-owner+m15591@postgresql.org.comp
Subject: [ADMIN] copy
Hello.
We are moving a database from informix to Postgresql.
To do so we do an unload from Informix and copy to postgresql.
Informix unloads with a delimiter also after the last field in a record.
This is no problem when doing a copy to Postgresql 7.2
You get a warning (to many fields) but the copy process continues.
In 7.4 however the copy stops .
Does anyone know how to proceed the copy or a fast way to remove the last
character
from the unload files.
They are anyhow to big to handle with vi.
Many thanks
Henk Sanders
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
^ permalink raw reply [nested|flat] 11+ messages in thread
* COPY
@ 2005-09-21 22:09 Sidar López Cruz <sidarlopez@hotmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Sidar López Cruz @ 2005-09-21 22:09 UTC (permalink / raw)
To: pgsql-admin
Why this wonder dabase don't have COPY with fixed columns width?
Or how to do it.
Thanks
_________________________________________________________________
Charla con tus amigos en línea mediante MSN Messenger:
http://messenger.latam.msn.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-09-21 22:09 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-31 15:28 COPY Wierzbicki, Ralf <rwierzbicki@stryker.ca>
2000-03-31 18:05 Re: COPY Edward Chase <echase@homer.providence.edu>
2001-04-30 21:53 Copy John Coers <coers@intrinsity.com>
2001-04-30 22:16 ` Re: Copy Tom Lane <tgl@sss.pgh.pa.us>
2001-05-01 15:49 ` Re: Copy John Coers <coers@intrinsity.com>
2001-05-01 22:05 ` Re: Copy John Coers <coers@intrinsity.com>
2004-12-01 12:33 copy H.J. Sanders <hjs@rmax.nl>
2004-12-01 13:01 ` Re: copy Geoffrey <esoteric@3times25.net>
2004-12-01 15:03 Re: copy CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-12-01 15:13 Re: copy CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2005-09-21 22:09 COPY Sidar López Cruz <sidarlopez@hotmail.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