Can COPY skip columns?

[Adam Witney]

Hi,

Is it possible for the COPY command to read data from a file, but skip
specific columns?

Thanks

Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Joshua D. Drake]

Adam Witney wrote:

Hi,

Is it possible for the COPY command to read data from a file, but skip
specific columns?
\h copy

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Yes.. you just have to specify the columns...

Sincerely,

Joshua D. Drake


Thanks

Adam

--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Adam Witney <aw*****@sghms.ac.uk> writes:

Is it possible for the COPY command to read data from a file, but skip
specific columns?

Nope. When you get into significant massaging of the input data,
usually the best bet is to COPY into a temp table that exactly matches
the format of the data file, and then do your rearrangements using an
INSERT/SELECT into the final target table.

regards, tom lane

---------------------------(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

[Adam Witney]

Hi Joshua,

Sorry, I meant skip a column in the file, not the database table, or is that
what you meant?

Thanks

adam

Adam Witney wrote:

Hi,

Is it possible for the COPY command to read data from a file, but skip
specific columns?

\h copy

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Yes.. you just have to specify the columns...

Sincerely,

Joshua D. Drake

Thanks

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joshua D. Drake]

Joshua D. Drake wrote:

Adam Witney wrote:

Hi,

Is it possible for the COPY command to read data from a file, but skip
specific columns?

Crap, read your message backwards. Sorry. No you can't do this.

Sincerely,

Joshua D. Drake


\h copy

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Yes.. you just have to specify the columns...

Sincerely,

Joshua D. Drake

Thanks

Adam

------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joshua D. Drake]

Adam Witney wrote:

Hi Joshua,

Sorry, I meant skip a column in the file, not the database table, or is that
what you meant?
No, I read your question backwards. I am sorry. As Tom Lane said, copy
the file into a temporary table and then you can deal with it from there.

Sincerely,
Joshua D. Drake

Thanks

adam

Adam Witney wrote:

Hi,

Is it possible for the COPY command to read data from a file, but skip
specific columns?

\h copy

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Yes.. you just have to specify the columns...

Sincerely,

Joshua D. Drake

Thanks

Adam

--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Adam Witney]

On 18/11/04 5:15 pm, "Tom Lane" <tg*@sss.pgh.pa.us> wrote:

Adam Witney <aw*****@sghms.ac.uk> writes:

Is it possible for the COPY command to read data from a file, but skip
specific columns?

Nope. When you get into significant massaging of the input data,
usually the best bet is to COPY into a temp table that exactly matches
the format of the data file, and then do your rearrangements using an
INSERT/SELECT into the final target table.

Ah ok. Can COPY be made to skip the first row then? To avoid the column
headers?

Thanks

adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Patrick B Kelly]

On Nov 18, 2004, at 11:53 AM, Adam Witney wrote:

Hi,

Is it possible for the COPY command to read data from a file, but skip
specific columns?

You can use awk to skip fields and create an intermediate file or
better yet, just pipe the output to copy. Here is a trivial example:

awk '{ FS = "\t" ; OFS = "\t" ; print $1,$3 }' inputdatafile

This sets the input and output field separators to tab and outputs the
first and third fields from inputdatafile. If you want to skip the
first record just add the following if statement:

awk '{ FS = "\t" ; OFS = "\t" ; if ( NR > 1 ) print $1,$3 }'
inputdatafile


Patrick B. Kelly
------------------------------------------------------
http://patrickbkelly.org
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Terry Lee Tucker]

Another thing you can do if you know how to use Perl is to write a load
procedure for the ascii file. We are dumping data from a Progress database so
there was quite a lot of massaging to do, especially with some tables. We
read the Progress dump in and write it back out in the format we need for
PostgreSQL. Then we use COPY. Perl is excellent for this typ of work.

On Thursday 18 November 2004 12:15 pm, Tom Lane saith:

Adam Witney <aw*****@sghms.ac.uk> writes:

Is it possible for the COPY command to read data from a file, but skip
specific columns?

Nope. When you get into significant massaging of the input data,
usually the best bet is to COPY into a temp table that exactly matches
the format of the data file, and then do your rearrangements using an
INSERT/SELECT into the final target table.

regards, tom lane

---------------------------(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

--

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Adam Witney]

Following on from my question yesterday... Can COPY then be made to skip a
header line (the first line of the file say)?

The problem is this... I need to allow a user to upload a data file through
a web browser (PHP driven). This is then processed and the selected file
columns mapped to fields in a database, and then the data file uploaded. I
can do this fine with small files.... But if I get above a 1000 rows it
takes so long it time out.

I can upload a 10,000 row equivalent file using COPY from psql in 2 seconds,
so the time is down to the PHP processing (really all it does is send itto
pg_put_Line)

I liked Toms idea to create a temporary table, but I need to be able to get
rid of the header row then... Is there anyway of avoiding getting PHP
involved in the file processing?

Any ideas?

Thanks

Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Martijn van Oosterhout]

Wouldn't it be easier to have PHP remove the first row?

And PHP has to be there because Apache is sending all the data to it.

Hope this helps,

On Fri, Nov 19, 2004 at 03:43:18PM +0000, Adam Witney wrote:

Following on from my question yesterday... Can COPY then be made to skip a
header line (the first line of the file say)?

The problem is this... I need to allow a user to upload a data file through
a web browser (PHP driven). This is then processed and the selected file
columns mapped to fields in a database, and then the data file uploaded. I
can do this fine with small files.... But if I get above a 1000 rows it
takes so long it time out.

I can upload a 10,000 row equivalent file using COPY from psql in 2 seconds,
so the time is down to the PHP processing (really all it does is send itto
pg_put_Line)

I liked Toms idea to create a temporary table, but I need to be able to get
rid of the header row then... Is there anyway of avoiding getting PHP
involved in the file processing?

Any ideas?

Thanks

Adam


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBnhlmY5Twig3Ge+YRAnsWAKCBEN8tsJdalrkYYXlFCf duSYFbmQCgw+35
ID9bZBkVZ7+5pxR8zuRNGlg=
=xW5J
-----END PGP SIGNATURE-----

[Adam Witney]

Hi Martijn,

Can I get PHP to remove the first row without reading the whole file in? If
there was a way where PHP would just chop the first row off that would be
ideal...

Thanks

adam

Wouldn't it be easier to have PHP remove the first row?

And PHP has to be there because Apache is sending all the data to it.

Hope this helps,

On Fri, Nov 19, 2004 at 03:43:18PM +0000, Adam Witney wrote:

Following on from my question yesterday... Can COPY then be made to skip a
header line (the first line of the file say)?

The problem is this... I need to allow a user to upload a data file through
a web browser (PHP driven). This is then processed and the selected file
columns mapped to fields in a database, and then the data file uploaded. I
can do this fine with small files.... But if I get above a 1000 rows it
takes so long it time out.

I can upload a 10,000 row equivalent file using COPY from psql in 2 seconds,
so the time is down to the PHP processing (really all it does is send itto
pg_put_Line)

I liked Toms idea to create a temporary table, but I need to be able to get
rid of the header row then... Is there anyway of avoiding getting PHP
involved in the file processing?

Any ideas?

Thanks

Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(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

[Joshua D. Drake]

> pg_put_Line)

I liked Toms idea to create a temporary table, but I need to be able to get
rid of the header row then... Is there anyway of avoiding getting PHP
involved in the file processing?
You could write a server side function to do the processing. You could
even use plPHP if that is your preferred language.

Sincerely,

Joshua D. Drake

Any ideas?

Thanks

Adam

--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(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

[Matteo Beccati]

Hi,

Can I get PHP to remove the first row without reading the whole file in? If
there was a way where PHP would just chop the first row off that would be
ideal...

If you are using "COPY table FROM stdin" and pg_put_line() it's not much
difficult to skip the first line ;)
Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Pierre-Frédéric Caillaud]

can do this fine with small files.... But if I get above a 1000 rows it
takes so long it time out.
PHP is slow, but not *that* slow, you have a problem somewhere !
I can upload a 10,000 row equivalent file using COPY from psql in 2
seconds,
so the time is down to the PHP processing (really all it does is send
itto
pg_put_Line)

If you read the whole file in memory, the server will kick your script (I
think the default limit is 8 megabytes or something)...

So, I'd advise reading the file line by line using fgets() (dunno how it
is spelled in php), and just skip the first line, and pg_put_line() the
rest. This way you just use memory for one line at a time. ALso you can
echo (and flush) messages like 'XX lines inserted...' to the user while it
crunches.

If you're really stuck, and have command execution privileges, why not
system() a command line like "awk -blah your file | psql copy to your
table", or even launch it as a background process ?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Johan Wehtje]

MS SQL Server has a BULK INSERT command that can take a parameter "Start
Row" so that you can skip as many Rows as you want.
this is also available using the command line bcp utility. This would be
a nice feature for Postgresql to have , and I can't see it on any of the
to do lists, unless it is already implemented well enough in third party
utility or contrib. I shelled out a fair bit to get hold of EMS Hi Tech
Postgres Manager bundle, and whilst there is a lot to like about it
generally, at least part of the reason was because of the improved bulk
insert tools.

Adam Witney wrote:

Hi Martijn,

Can I get PHP to remove the first row without reading the whole file in? If
there was a way where PHP would just chop the first row off that would be
ideal...

Thanks

adam

Wouldn't it be easier to have PHP remove the first row?

And PHP has to be there because Apache is sending all the data to it.

Hope this helps,

On Fri, Nov 19, 2004 at 03:43:18PM +0000, Adam Witney wrote:

Following on from my question yesterday... Can COPY then be made to skip a
header line (the first line of the file say)?

The problem is this... I need to allow a user to upload a data file through
a web browser (PHP driven). This is then processed and the selected file
columns mapped to fields in a database, and then the data file uploaded. I
can do this fine with small files.... But if I get above a 1000 rows it
takes so long it time out.

I can upload a 10,000 row equivalent file using COPY from psql in 2 seconds,
so the time is down to the PHP processing (really all it does is send itto
pg_put_Line)

I liked Toms idea to create a temporary table, but I need to be able to get
rid of the header row then... Is there anyway of avoiding getting PHP
involved in the file processing?

Any ideas?

Thanks

Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Adam Witney]

On 19/11/04 6:46 pm, "Pierre-Frédéric Caillaud"
<li***@boutiquenumerique.com> wrote:

can do this fine with small files.... But if I get above a 1000 rows it
takes so long it time out.

PHP is slow, but not *that* slow, you have a problem somewhere !

Aha yes, I was reading the file doing this

fgets($fh, 1048576)))

.... But changing it to

fgets($fh, 1024)))

Which is a large enough amount of data for my needs improved the speed
dramatically!

Thanks to all those that replied

adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org