Skip site navigation (1) Skip section navigation (2)

Re: Primary key on existing table?

From: Patrick <flymooney(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Primary key on existing table?
Date: 2009-08-25 23:01:55
Message-ID: 4A946D63.4010407@gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

















In response to

pgsql-admin by date

Next:From: Pablo Alonso-Villaverde RozaDate: 2009-08-26 07:01:38
Subject: Re: Avoid duplicated rows when restoring data from pg_dumpall ??
Previous:From: Scott MarloweDate: 2009-08-25 22:25:19
Subject: Re: Primary key on existing table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group