Re: Query RE using COPY

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/