In current versions of PostgreSQL you can specify column names with the COPY command to copy in or out only specific columns. This can come in handy if you want to recalculate your generated keys.

Given table:

	CREATE TABLE people_131 (
   	ID SERIAL PRIMARY KEY,
   	name TEXT,
   	role TEXT
	);

	\COPY people_131 (name, role) to 'people.dat'

	DELETE FROM people_131;

	ALTER SEQUENCE people_131_id_seq RESTART 100;
	\COPY people_131 (name, role) from 'people.dat'

This technique can be used to copy in new data if the COPY formatted data does not have the generated key info in it. In that case, you would NOT alter the sequence or delete data from your original table.

You can achieve the same effect of the original example by selecting the data into a temporary table instead of copying out. After you copy the data into the temporary table, delete the original data, reset the sequence and copy it back in.

	CREATE TEMPORARY TABLE people_temp AS SELECT name, role FROM people_131;
	ALTER SEQUENCE people_131_id_seq RESTART 1000;
	DELETE FROM people_131;
	INSERT INTO people_131 (name, role) select name, role from people_temp;

Things to notice:

• The sequence name (if you need it) is table_column_seq
• The format of the data to copy in must match the columns speficied.

Contributors: Scott Ribe scott_ribe at killerbytes.com, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com

Creating Functional Indexes [GENERAL] UNIQUE constraints on function results 08-Oct-06

When creating a UNIQUE constraint on a table, only column names are allowed per the SQL Specifications. You can specify:

	CREATE TABLE test (
	   id SERIAL PRIMARY KEY,
	   t1 TEXT NOT NULL,
	   t2 TEXT NOT NULL,
	   UNIQUE (t1, t2));

	CREATE TABLE test (
	   id SERIAL PRIMARY KEY,
	   t1 TEXT NOT NULL,
	   t2 TEXT NOT NULL,
	   UNIQUE (t1, lower(t2)));

Postgresql indexes are much more flexible. They allow UNIQUE indexes to be specified on both column names and function names. To get the functionalitly you want in this case use:

	CREATE TABLE test (
	   id SERIAL PRIMARY KEY,
	   t1 TEXT NOT NULL,
	   t2 TEXT NOT NULL);
	CREATE UNIQUE INDEX test_idx on test( t1, lower(t2) );

When Postgres sees a UNIQUE contraint on a table, it automatically creates a UNIQUE index on the columns involved. However, since there is not SQL approved way of including functions or expressions in the index, you much create your own, possibily more powerful index.

Contributors: Markus Schiltknecht markus at bluegap.ch, Emanuele Rocca ema at linux.it, Tom Lane tgl at sss.pgh.pa.us

Update or Replace Rows with Foreign Keys [SQL] How to overwrite a table [only data]? 8-Oct-2006

There are times when a table is such a mess that you want to do some serious replacement of the rows in that table. If the table has no foreign keys associated with it, then you just go ahead and perform your operation. Otherwise, you must take some caution to preserve your primary keys.

With care, you can perform actions like UPDATE, INSERT and DELETE on the table. First you must SET CONSTRAINTS ALL DEFERRED and then you must perform your operations within the context of a transaction.

In this example, we have a table furn_in_rooms which containts two columns (room text, item text) which are FOREIGN KEYS into rooms table and the furniture table. We will set the constraints to be deferred, delete all of the rows in the furn_in_row table and add the new correct rows.

elein=#  SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
elein=# BEGIN;
BEGIN
elein=# delete from furn_in_room;
DELETE 4
elein=# insert into furn_in_room values ('living room', 'sofa');
INSERT 0 1
elein=# insert into furn_in_room values ('living room', 'Left chair');
INSERT 0 1
elein=# insert into furn_in_room values ('Left Bed room', 'KingBed');
INSERT 0 1
elein=# insert into furn_in_room values ('Right Bed room', 'KidBed');
INSERT 0 1
elein=# COMMIT;
COMMIT

You can use this technique of setting contraints to be deferred followed by a BLOCK transaction to do any intermediate editing that it appropriate for correcting your base table.

Contributors: Ezequias Rodrigues da Rocha ezequias.rocha at gmail.com, Aaron Bono postgresql at aranya.com, Markus Schaber schabi at logix-tt.com