Postgres Pro
Downloads
Home   >   mailing lists

Thread: Copy From problem

Copy From problem

From
Eduardo Vázquez Rodríguez
Date:
Hi I try to "copy from" a plain text file that looks someting like this:

John Red (\t) Garden Grove (\t) jwayne@garden.com
Jessie Rodriguez (\t) West Beach
Jane Harrison (\t) Malibu (\t) jharrison@malibu.com

Deliberately I missed the email info in the second line, I try to copy into
a table that has the following structure:

CREATE TABLE public."Example"
(
  "Name" varchar(30),
  "Adress" varchar(60),
  "Email" varchar(30),
) WITHOUT OIDS;


When I issue the following command
copy public."Example" from '/home/postgres/abc.txt';

The following error appears

ERROR:  missing data for column "Email"
CONTEXT:  COPY Prueba, line 2: "Jessie Rodriguez    Santa Anna "

My question is how can I insert a null value into the field email,and that
"copy from" continue copying the next lines?

Re: Copy From problem

From
Manuel Sugawara
Date:
Eduardo Vázquez Rodríguez <evazquez@insys-corp.com.mx> writes:

> My question is how can I insert a null value into the field
> email,and that "copy from" continue copying the next lines?

Use \N to represent the null value, for instance (following your
notation (\t) to represent the tab char).

Jessie Rodriguez (\t) West Beach (\t) \N

Regards,
Manuel.

Re: Copy From problem

From
Nabil Sayegh
Date:
Eduardo Vázquez Rodríguez wrote:
> Hi I try to "copy from" a plain text file that looks someting like this:
>
> John Red (\t) Garden Grove (\t) jwayne@garden.com
> Jessie Rodriguez (\t) West Beach
> Jane Harrison (\t) Malibu (\t) jharrison@malibu.com
>
> Deliberately I missed the email info in the second line, I try to copy into
> a table that has the following structure:
>
> CREATE TABLE public."Example"
> (
>   "Name" varchar(30),
>   "Adress" varchar(60),
>   "Email" varchar(30),
> ) WITHOUT OIDS;

> My question is how can I insert a null value into the field email,and that
> "copy from" continue copying the next lines?

In recent versions you can specify the columns to import.

COPY "Example" ("Name", "Adress") FROM ...

or somethign like that.

BTW: I suggest not using uppercase table/field-names.
It's too much hazzle always having to quote table and field names ;)

HTH
--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de