Thread: COPY column order
Hi there I'm trying to use COPY with HEADER option but my header line in file is in different order than the column order specified in database. Is the column name order necessary in my file ?? thxs -- View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-column-order-tp5690950.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 05/07/2012 05:33 AM, MD33 wrote: > Hi there > > I'm trying to use COPY with HEADER option but my header line in file is in > different order than the column order specified in database. > Is the column name order necessary in my file ?? From the docs: http://www.postgresql.org/docs/9.0/interactive/sql-copy.html "HEADER Specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table, and on input, the first line is ignored. This option is allowed only when using CSV format." So when going from file to table the HEADER is ignored, The column order is important though. This can be specified by using the column parameter. -- Adrian Klaver adrian.klaver@gmail.com
You can specify the column order in the copy statement: psql -d test -c "create table ttt (id serial primary key, name varchar(10), value int);" echo "10|one" | psql -d test -c "copy ttt (value,name) from stdin with delimiter '|';" psql -d test -c "select * from ttt;" id | name | value ----+------+------- 1 | one | 10 (1 row) HTH Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 ________________________________________ From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of MD33 [mdubosforum@yahoo.com] Sent: Tuesday, May 08, 2012 12:33 AM To: pgsql-general@postgresql.org Subject: [GENERAL] COPY column order Hi there I'm trying to use COPY with HEADER option but my header line in file is in different order than the column order specified in database. Is the column name order necessary in my file ?? thxs -- View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-column-order-tp5690950.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
HEADER option is not as useful as I wanted, but sounds good with column names. Thanks a lot ! -- View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-column-order-tp5690950p5696058.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.