DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=GwLsdztR9D9QdJ7Gtdsgs4sLPHReKZna76SQpBhTYbBid/2QFIrPpnv/a8A7iaCSHP
	S4+jO9ahLzw16CgyVQQCuAouCA72uO8CNOBJHgKRGxRsA2EK097yMhpMoFxHnAa4GuH0
	wUhBRSKd3gJhAzO4V8kT/r0Q0sHOuQKOlFQNU=
Message-ID: <4D34AAC1.60002@gmail.com>
Date: Mon, 17 Jan 2011 12:46:57 -0800
From: Adrian Klaver <adrian.klaver@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
To: Amar Dhole <adhole@tibco.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Help needed in skipping column for copy command
References: 
 <1293747263.1650.7.camel@tony1.localdomain><AANLkTikHcZy-H_iS2KMvvoMLnKDekE=3KBKD9EoBaNa5@mail.gmail.com><6b49785d598885ab7f8c5d3955afd74c.squirrel@mail.prospectiv.com><201012310905.35527.adrian.klaver@gmail.com><B290BFEC59278744B17A7A3CB14307E9038726F0@NA-PA-VBE04.na.tibco.com>
	<AANLkTin3G7wXF0e8_ysFYP6zLYV-7=nt_FPBjzvj_vhe@mail.gmail.com>
	<B290BFEC59278744B17A7A3CB14307E9038728DD@NA-PA-VBE04.na.tibco.com>
	<B290BFEC59278744B17A7A3CB14307E90387304A@NA-PA-VBE04.na.tibco.com>
In-Reply-To: 
 <B290BFEC59278744B17A7A3CB14307E90387304A@NA-PA-VBE04.na.tibco.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 01/17/2011 05:59 AM, Amar Dhole wrote:
> I have table created as follows
>
> CREATE TABLE D_2147483927_2147484848_TAB(
>
> CP VARCHAR(256) ,
>
> CPR VARCHAR(256) ,
>
> CHOUSENO VARCHAR(256) ,
>
> CSTREET VARCHAR(256) ,
>
> CLOCALITY VARCHAR(256) ,
>
> CCITY VARCHAR(256) ,
>
> CPROVINCE VARCHAR(256) ,
>
> CCOUNTRY VARCHAR(256) ,
>
> CZIP VARCHAR(256) ,
>
> CCO VARCHAR(256) )
>
> I am using copy command to copy the content of file into the table.
> (one.txt)
>
> PR,PRO,HOUSENO,STREET,LOCALITY,CITY,PROVINCE,COUNTRY,ZIP,CON
>
> ,,A-24 Siddi vihar apt.,Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027
>
> In the above data, data for last column is missing.

Looks like two columns missing. I see 10 headers and 8 data values 
unless that is a cut and paste issue.

>
> copy D_2147483927_2147484848_TAB from 'D:/work/one.txt' with delimiter
> as ',' quote '"' csv HEADER ;
>
> I get the following error as
>
> ERROR: missing data for column "ccontains"

Where does ccontains from? I am not seeing it in the table or the text file.

>
> CONTEXT: COPY d_2147483927_2147484848_tab, line 2: "q,q,A-24 Siddi vihar
> apt.,
>
> Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 "
>
> Can any one please tell me how can I make copy command to ignore the
> data missing column ? as the data in file is unknown so it column name
> is which is missing is not known in advance.

You can specify a column list to COPY. See here:
http://www.postgresql.org/docs/9.0/interactive/sql-copy.html

>
> Thanks
>
> Amar
>


-- 
Adrian Klaver
adrian.klaver@gmail.com