Helgrim.com

First, you need to add a primary key ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column [,column ...]); constraint_name may be something like table_pkey. You need to set the key column(s) to be not null.

Next, create the sequence the table will use: CREATE SEQUENCE table_id_seq;

Now set the value of the sequence: SELECT setval('some_table_id_seq', value); e.g. SELECT setval('examples_id_seq', 100); or SELECT setval('examples_id_seq', (SELECT max(id) from examples)); When setting the value, you are setting it to the last number *used*. So if you set it to 100, the next INSERT will get id 101.

Set the default value of the key field to use the sequence: ALTER TABLE example ALTER id SET DEFAULT nextval('public.table_id_seq'::text);

Drop an index:

DROP INDEX constraint_name; If index is used by a table as a key, you'll need to drop the constraint first.

Drop a constraint:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Add a not null constraint

ALTER TABLE table_name ALTER field_name SET NOT NULL;

Query Optimisation:

Joins tend to be most expensive, so make sure the joined tuples are indexed e.g. SELECT * FROM cats,dogs WHERE cats.ook='fish' AND cats.dogid=dog.id may benefit from an index on cats.dogid: CREATE INDEX cats_dogid_idx ON cats(dogid); You really should try to identity what is in need of indexing, too many indexes sometimes defeats the purpose.

multi-column index:

CREATE INDEX cats_dog_kennel_idx ON cats(dog,kennel);

To see what the planner is doing:

EXPLAIN ANALYZE <query>

To see the size that the planner thinks each table and index is:

SELECT relname,reltuples,relpages FROM pg_class WHERE relname LIKE 'cats%'

IM Status