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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
