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