Skip site navigation (1) Skip section navigation (2)

Re: Query RE using COPY

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: "Paul Lambert" <paul(dot)lambert(at)autoledgers(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Query RE using COPY
Date: 2007-05-08 02:16:10
Message-ID: 36e682920705071916l27d64129j2d3c9d296fbbd6e5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
COPY mytbl (col1, col2, col3, col4, col6) FROM 'myfile.txt' WITH
DELIMITER AS '^' QUOTE '\f' CSV HEADER;


On 5/7/07, Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)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(at)enterprisedb(dot)com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

In response to

Responses

pgsql-sql by date

Next:From: Paul LambertDate: 2007-05-08 02:44:21
Subject: Re: Query RE using COPY
Previous:From: Paul LambertDate: 2007-05-08 01:51:52
Subject: Query RE using COPY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group