From: Jonah H. Harris <jonah.harris@gmail.com>
To: Paul Lambert <paul.lambert@autoledgers.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query RE using COPY
Date: Mon, 7 May 2007 22:16:10 -0400
Message-ID: <36e682920705071916l27d64129j2d3c9d296fbbd6e5@mail.gmail.com> (raw)
In-Reply-To: <463FD7B8.9030700@autoledgers.com.au>
References: <463FD7B8.9030700@autoledgers.com.au>
COPY mytbl (col1, col2, col3, col4, col6) FROM 'myfile.txt' WITH
DELIMITER AS '^' QUOTE '\f' CSV HEADER;
On 5/7/07, Paul Lambert <paul.lambert@autoledgers.com.au> wrote:
> I use the COPY command to load data from a file into tables in my database.
>
> The following is an example done in psql:
> COPY deals_temp_load FROM 'c:/temp/autodrs_deals.txt' WITH DELIMITER AS
> '^' QUOTE '\f' CSV HEADER;
>
> If a column is added to the table but has not been added to the extracts
> which create the autodrs_deals.txt file, this errors thusly:
> ERROR: missing data for column "location"
> CONTEXT: COPY deals_temp_load, line 2: "line containing data removed
> for confidentiality - suffice to say it does not contain the 'location'
> column"
>
> Is there any way of telling it to ignore columns that have not been
> specified in the file used to load? I have tried giving the column a
> default value but I still get the error. The column in this case
> 'location' is a nullable column and does not always have data (yes, bad
> design for a database, but we'll skip that point for now) thus I am not
> concerned if the load procedure doesn't supply it.
>
> BTW, this is done on Weendoze.
>
> Thanks,
> Paul.
>
> --
> Paul Lambert
> Database Administrator
> AutoLedgers
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jonah.harris@gmail.com, paul.lambert@autoledgers.com.au
Subject: Re: Query RE using COPY
In-Reply-To: <36e682920705071916l27d64129j2d3c9d296fbbd6e5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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