Re: Primary key on existing table?

Carol Walter wrote:
> This may be a silly question but I don't see any place where the
> documentation explicitly addresses whether or not you can designate a
> particular column as a primary key after the table is created. I used
> the "create table as " syntax to create a table with the same columns as
> an original table, but I want to rename id column, make it a serial, and
> designate it as a primary key. Is it possible to do this? I've tried a
> number of things to do this and none of them have worked.
>
> Carol
>

Is this close to what you are wanting to do ?

******* Create Test Table

test_it=# create table rename_me (
test_it(# id int primary key,
test_it(# name char(2)
test_it(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"rename_me_pkey" for table "rename_me"
CREATE TABLE

******* Table Status

test_it=# \d rename_me
Table "public.rename_me"
Column | Type | Modifiers
--------+--------------+-----------
id | integer | not null
name | character(2) |
Indexes:
"rename_me_pkey" PRIMARY KEY, btree (id)

******* Drop Constraint (primary key index)

test_it=# alter table rename_me drop constraint rename_me_pkey;
ALTER TABLE

******* New Table Status

test_it=# \d rename_me
Table "public.rename_me"
Column | Type | Modifiers
--------+--------------+-----------
id | integer | not null
name | character(2) |

******* Drop Constraint (not null)

test_it=# alter table rename_me alter column id drop not null;
ALTER TABLE

******* Table Status

test_it=# \d rename_me
Table "public.rename_me"
Column | Type | Modifiers
--------+--------------+-----------
id | integer |
name | character(2) |

******* Rename Column (from id to new_id)

test_it=# alter table rename_me rename column id to new_id;
ALTER TABLE

******* Table Status

test_it=# \d rename_me
Table "public.rename_me"
Column | Type | Modifiers
--------+--------------+-----------
new_id | integer |
name | character(2) |

******* Change new_id from integer to serial
******* (this is from the Postgresql manual)

test_it=# CREATE SEQUENCE rename_me_new_id_seq;
CREATE SEQUENCE
test_it=# SELECT setval( 'rename_me_new_id_seq', ( SELECT MAX( new_id )
FROM rename_me ) );
setval
--------

(1 row)

test_it=# ALTER TABLE rename_me ALTER COLUMN new_id SET DEFAULT nextval(
'rename_me_new_id_seq');
ALTER TABLE

test_it=# \d rename_me
Table "public.rename_me"
Column | Type | Modifiers

--------+--------------+------------------------------------------------------------
new_id | integer | not null default
nextval('rename_me_new_id_seq'::regclass)
name | character(2) |

******* Add Primary Key

test_it=# alter table rename_me add primary key (new_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"rename_me_pkey" for table "rename_me"
ALTER TABLE

******* Table Status

test_it=# \d rename_me
Table "public.rename_me"
Column | Type | Modifiers
--------+--------------+-----------
new_id | integer | not null default
nextval('rename_me_new_id_seq'::regclass)
name | character(2) |
Indexes:
"rename_me_pkey" PRIMARY KEY, btree (new_id)

******* Done