Thread: Importing data

Importing data

From
Patrick Nelson
Date:
If I have a table that has a column called id that I would like to have a
incrementing field.  However, the data that I'm importing doesn't have that
field.  I though that using a sequence would work, but that doesn't seem to
do it, here was the setup:

  CREATE SEQUENCE "mss_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1;
  SELECT nextval ('"mss_id_seq"');
  CREATE TABLE "mss" (
   "id"  int4         DEFAULT nextval('mss_id_seq'::text) NOT NULL,
   "ind" int4         NOT NULL,
   "syb" varchar(5)   NOT NULL,
   "nam" varchar(50)  NOT NULL,
   "yts" varchar(50)  NOT NULL,
   "bys" int4         NOT NULL,
   "byr" int4         NOT NULL,
   "hld" int4         NOT NULL,
   "ser" int4         NOT NULL,
   "ses" int4         NOT NULL,
   "tor" int4         NOT NULL,
   "wor" numeric(5,2) NOT NULL,
   "dat" varchar(50)  NOT NULL,
  );

Then I import using psql command:

  COPY mss FROM '/subset.csv' USING DELIMITERS ',';

Which imports a file like:

  ,1,SLWS,SLWS DATA,$489.18M,6,1,1,0,0,8,1.37,Sat Jun 29 12:42:47 PDT 2002
  ,2,SRDE,SRDE DATA,$232.30M,0,0,2,0,0,2,3,Sat Jun 29 12:43:00 PDT 2002
  ...

However, id doesn't get populated with a sequence of numbers.  Note that the
1 and 2 of the above import file is being properly dumped into ind, even
though I have to add "," to the beginning of each line.  So I think that I
don't really get it.  Hmm...

What I would like the data to look like (from the above import) is like:

 id ind syb  nam       yts       bys byr hld ser ses tor wor  dat
 -- --- ---- --------- --------  --- --- --- --- --- --- ----
----------------------------
 1  1   SLWS SLWS DATA $489.18M  6   1   1   0   0   8   1.37 Sat Jun 29
12:42:47 PDT 2002
 2  2   SRDE SRDE DATA $232.30M  0   0   2   0   0   2   3    Sat Jun 29
12:43:00 PDT 2002

Where I could make the id as the primary key.  What don't I get?

Re: Importing data

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
On Mon, Jul 29, 2002 at 10:34:31AM -0700, Patrick Nelson wrote:
>   COPY mss FROM '/subset.csv' USING DELIMITERS ',';
>
> Which imports a file like:
>
>   ,1,SLWS,SLWS DATA,$489.18M,6,1,1,0,0,8,1.37,Sat Jun 29 12:42:47 PDT 2002
>   ,2,SRDE,SRDE DATA,$232.30M,0,0,2,0,0,2,3,Sat Jun 29 12:43:00 PDT 2002
>   ...
>
> However, id doesn't get populated with a sequence of numbers.  Note that the
> 1 and 2 of the above import file is being properly dumped into ind, even
> though I have to add "," to the beginning of each line.  So I think that I
> don't really get it.

If data for an attribute is missing, it will be set to NULL in 7.2 and
earlier versions of PostgreSQL. This behavior will be fixed in 7.3, but
until then, you can set the "id" column yourself:

COPY ...;
UPDATE mss SET id = nextval('mss_id_seq');

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Importing data

From
Patrick Nelson
Date:
nconway@klamath.dyndns.org wrote:
----------------->>>>
If data for an attribute is missing, it will be set to NULL in 7.2 and
earlier versions of PostgreSQL. This behavior will be fixed in 7.3, but
until then, you can set the "id" column yourself:

COPY ...;
UPDATE mss SET id = nextval('mss_id_seq');
----------------->>>>
Wow, thanks that helps a lot.  Maybe you can help shed some light.  How do
you add a column and make it not null?  I want to add a column the is:

  NOT NULL DEFAULT nextval('mss_id_seq'::text)

Tried the following:

 ALTER TABLE mss ADD COLUMN id int4 NOT NULL;
 ALTER TABLE mss ALTER COLUMN id SET DEFAULT nextval('mss_id_seq'::text);

But it doesn't set the column modifier to NOT NULL.  Why?

Re: Importing data

From
Andrew Sullivan
Date:
On Mon, Jul 29, 2002 at 12:32:53PM -0700, Patrick Nelson wrote:
> Tried the following:
>
>  ALTER TABLE mss ADD COLUMN id int4 NOT NULL;
>  ALTER TABLE mss ALTER COLUMN id SET DEFAULT nextval('mss_id_seq'::text);
>
> But it doesn't set the column modifier to NOT NULL.  Why?

You should get an error in 7.2.x; it fails silently in 7.1.  It's not
implemented yet.  You can make it happen, however, by setting a
CHECK NOT NULL constraint on the column.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110


Re: Importing data

From
Patrick Nelson
Date:
Andrew Sullivan wrote:
----------------->>>>
You should get an error in 7.2.x; it fails silently in 7.1.  It's not
implemented yet.  You can make it happen, however, by setting a
CHECK NOT NULL constraint on the column.
----------------->>>>

Thanks, but I just parsed the data and dumped the idea of adding a column.
Is there a way to set a primary key after the table has been crated?

Re: Importing data

From
Andrew Sullivan
Date:
On Mon, Jul 29, 2002 at 03:08:43PM -0700, Patrick Nelson wrote:
>
> Thanks, but I just parsed the data and dumped the idea of adding a column.
> Is there a way to set a primary key after the table has been crated?

See
<http://techdocs.postgresql.org/techdocs/compensating4features.php>
for a way to add primary keys after creating a table.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110