Can COPY skip columns?

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.

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Adam Witney <awitney@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

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

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

Adam Witney <awitney@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.

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

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 <awitney@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: terry@esc1.com

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.

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 majordomo@postgresql.org

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

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 majordomo@postgresql.org

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

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

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 ?

Since we can specify the order of columns in copy, how hard would it be
(he asked, naively) to specify a column name that points to /dev/null
(or the postgresql internals equivalent)? Sybase's copy utility is very
similar to our copy, and has a function that you can specify in your
column list instead of a real column name that ignores the data.

Something like

copy mytable (col1, col2, ignore(), col3) from '/tmp/dump';

Not a big deal, but kind of handy.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

Tom Lane <tgl@sss.pgh.pa.us> 11/18/04 9:15 AM >>>

Adam Witney <awitney@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

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:

On 19/11/04 6:46 pm, "Pierre-Frédéric Caillaud"
<lists@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.