DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=BtLQ0yNTh0K43yvFi/WeckaZIaUdWbjd2q22SmXG7RD/e8DP1131R+p8cjp/gnAKxyqa6DDXmFZZZi3l1auy6ajhfuENIgVXzXywx2CIz+rp4y57K9d3LtHxoL8eNpZrhNBGrSSvSAoengZJMy7JP5HjKesP49dlO7I0nijgj9A=
Message-ID: <36e682920705071916l27d64129j2d3c9d296fbbd6e5@mail.gmail.com>
Date: Mon, 7 May 2007 22:16:10 -0400
From: "Jonah H. Harris" <jonah.harris@gmail.com>
To: "Paul Lambert" <paul.lambert@autoledgers.com.au>
Subject: Re: Query RE using COPY
Cc: pgsql-sql@postgresql.org
In-Reply-To: <463FD7B8.9030700@autoledgers.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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/