Обсуждение: Adding columns to existing tables

I have a table - suppose it is called company:

CREATE TABLE ( company_id char(36) NOT NULL UNIQUE PRIMARY KEY,
            .
            .
            .
        );

I want to add the following field:
    company_nemonic char( 3 ) NOT NULL UNIQUE

Well, ALTER TABLE only gets me part of the way there.  While this code
successfully adds the table:
    ALTER TABLE
        company
    ADD COLUMN
        company_nemonic char( 3 ) NOT NULL UNIQUE;

the constraints are ignored.


Is there an easy way to add the constraints after the fact?

I have resorted to creating a company_temp table, insert into company_temp
selecting from company, dropping company and then renaming company_temp to
company.  This works, but causes another problem.  The index for the unique
key company_temp_pkey does not get renamed with the table.  This means if I
ever want to do something like this again, the create of company_temp will
fail because company_temp_pkey already exists.

Any help would be much appreciated.
_______________________________________
John Pagakis
DevelopOnline.com
480.377.6828
 <<John Pagakis.vcf>>

Stephan -
Thanks for the reply and good information.

I was afraid it was going to involve learning more about the internal tables
.... I was hoping to avoid that as I have a tight deadline.

For the time being,  I resolved this problem by taking a slightly different
tact with the intermediate table idea I had.  Using the company example (see
original message below):

1) SELECT INTO temp_company from company - this creates and populates a
table that looks almost exactly like company.  It has none of the
constraints, has no key - it is simply a place to park the data.

2) DROP company - this also automatically drops the primary key index (
company_pkey ).

3) CREATE TABLE company - with the new columns/constraints.  This re-creates
the primary key index ( company_pkey ), correctly named.

4) INSERT INTO company SELECT * FROM temp_company - this reloads company.

5) DROP temp_company.


By doing it this way, I avoid the problem I was  having with the primary key
index mis-named at the end of the process (last paragraph of original
message).

I suppose I really ought to read up on the pg_ tables <sigh> .......

Thanks again!!!

_______________________________________
John Pagakis
DevelopOnline.com
480.377.6828


-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Wednesday, May 09, 2001 9:01 AM
To: John Pagakis
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Adding columns to existing tables


On Tue, 8 May 2001, John Pagakis wrote:

> I have a table - suppose it is called company:
>
> CREATE TABLE ( company_id char(36) NOT NULL UNIQUE PRIMARY KEY,
>             .
>             .
>             .
>         );
>
> I want to add the following field:
>     company_nemonic char( 3 ) NOT NULL UNIQUE
>
> Well, ALTER TABLE only gets me part of the way there.  While this code
> successfully adds the table:
>     ALTER TABLE
>         company
>     ADD COLUMN
>         company_nemonic char( 3 ) NOT NULL UNIQUE;
>
> the constraints are ignored.
>
>
> Is there an easy way to add the constraints after the fact?

Unique is easy, you can create a unique index on the column which
is effectively what the unique constraint is doing behind the scenes.
Not null is harder, IIRC, you need to go and actually toggle
attnotnull in pg_attribute for the attribute.

On Tue, 8 May 2001, John Pagakis wrote:

> I have a table - suppose it is called company:
>
> CREATE TABLE ( company_id char(36) NOT NULL UNIQUE PRIMARY KEY,
>             .
>             .
>             .
>         );
>
> I want to add the following field:
>     company_nemonic char( 3 ) NOT NULL UNIQUE
>
> Well, ALTER TABLE only gets me part of the way there.  While this code
> successfully adds the table:
>     ALTER TABLE
>         company
>     ADD COLUMN
>         company_nemonic char( 3 ) NOT NULL UNIQUE;
>
> the constraints are ignored.
>
>
> Is there an easy way to add the constraints after the fact?

Unique is easy, you can create a unique index on the column which
is effectively what the unique constraint is doing behind the scenes.
Not null is harder, IIRC, you need to go and actually toggle
attnotnull in pg_attribute for the attribute.