Adding columns to existing tables 
Author Message
 Adding columns to existing tables

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_000_01C0D84B.1CED2D77
Content-Type: text/plain; charset="iso-8859-1"

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>>

------_=_NextPart_000_01C0D84B.1CED2D77
Content-Type: application/octet-stream; name="John Pagakis.vcf"
Content-Disposition: attachment; filename="John Pagakis.vcf"

BEGIN:VCARD
VERSION:2.1
N:Pagakis;John
FN:John Pagakis
ORG:DevelopOnline.com;Development
TITLE:Developer
TEL;WORK;VOICE:480-377-6828
ADR;WORK;ENCODING=QUOTED-PRINTABLE:;Centerpoint;660 South Mill Avenue=0D=0ASuite 400 ;Tempe;AZ;85281;USA
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Centerpoint=0D=0A660 South Mill Avenue=0D=0ASuite 400 =0D=0ATempe, AZ 85281=
=0D=0AUSA

REV:20001101T043310Z
END:VCARD

------_=_NextPart_000_01C0D84B.1CED2D77
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

------_=_NextPart_000_01C0D84B.1CED2D77--



Sun, 26 Oct 2003 13:51:21 GMT
 Adding columns to existing tables

Quote:

> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl



Mon, 27 Oct 2003 00:35:48 GMT
 Adding columns to existing tables
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_000_01C0D8A3.6D41C75D
Content-Type: text/plain; charset="iso-8859-1"

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

Quote:
-----Original Message-----

Sent: Wednesday, May 09, 2001 9:01 AM
To: John Pagakis


Subject: Re: [ADMIN] 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?

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.

------_=_NextPart_000_01C0D8A3.6D41C75D
Content-Type: application/octet-stream; name="John Pagakis.vcf"
Content-Disposition: attachment; filename="John Pagakis.vcf"

BEGIN:VCARD
VERSION:2.1
N:Pagakis;John
FN:John Pagakis
ORG:DevelopOnline.com;Development
TITLE:Developer
TEL;WORK;VOICE:480-377-6828
ADR;WORK;ENCODING=QUOTED-PRINTABLE:;Centerpoint;660 South Mill Avenue=0D=0ASuite 400 ;Tempe;AZ;85281;USA
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Centerpoint=0D=0A660 South Mill Avenue=0D=0ASuite 400 =0D=0ATempe, AZ 85281=
=0D=0AUSA

REV:20001101T043310Z
END:VCARD

------_=_NextPart_000_01C0D8A3.6D41C75D
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

------_=_NextPart_000_01C0D8A3.6D41C75D--



Mon, 27 Oct 2003 00:35:23 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Adox - Add Column to existing table (HELP!!)

2. Script to copy data from one existing column to a new column in another existing table

3. Add Columns to Existing Table? Copy a Recordset?

4. Adding new column to existing table

5. Adding Columns to an existing table frame

6. Add Column In Existing Merge Replication Table

7. Adding new column to existing table

8. Column - How do you add one to an existing table

9. How to add new columns to an existing table

10. add a new column into the middle of an existing table

11. Adding a bit column to an existing table

12. Adding columns to an existing SQL 6.5 table


 
Powered by phpBB® Forum Software