Primary Keys in PostgreSQL shouldn’t be a Mystery

PostgreSQL has so much power, and people think it’s much harder to use.  You can do just about anything with Alter Table in terms of Adding Columns.  The only tricky thing I’ve found is that you can’t add a NOT NULL column without a Default if there is data, since the default would be NULL.  So you can allow Nulls, fill in your data, then tag it Not Null, or give it a useful default and move on.  I saw another post of someone making PostgreSQL way more difficult than needed, creating a Primary Key later.  While there are plenty (or some) reasons to create a sequence manually (I had a need for unique integers for insertion into a third-party system once), there isn’t a need for something simply like a primary key.

Let’s setup our test table, you can obviously do all the inserts on one line, I was playing on an older server for testing.


test_database=# CREATE TABLE test_table (col_A varchar, col_B varchar);
CREATE TABLE
test_database=# INSERT INTO test_table(col_a, col_B) VALUES ('a','A');
INSERT 0 1
test_database=# INSERT INTO test_table(col_a, col_B) VALUES ('b', 'B');
INSERT 0 1
test_database=# INSERT INTO test_table(col_a, col_B) VALUES ('c', 'C');
INSERT 0 1

Now we want to add a Primary Key:

test_database=# ALTER TABLE test_table ADD COLUMN test_id SERIAL PRIMARY KEY;
NOTICE: ALTER TABLE will create implicit sequence "test_table_test_id_seq" for serial column "test_table.test_id"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_table_pkey" for table "test_table"
ALTER TABLE
test_database=# SELECT * from test_table;
col_a | col_b | test_id
-------+-------+---------
a | A | 1
b | B | 2
c | C | 3
(3 rows)
test_database=# \d test_table
Table "public.test_table"
Column | Type | Modifiers
---------+-------------------+--------------------------------------------------------------
col_a | character varying |
col_b | character varying |
test_id | integer | not null default nextval('test_table_test_id_seq'::regclass)
Indexes:
"test_table_pkey" PRIMARY KEY, btree (test_id)

Look, one line, added a new column test_id, with the sequence, tagged as a primary key.  Not so hard?  Why isn’t that an obvious example under Alter Table, given that it’s something that one might want to do?