varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

9-Oct-2006 Issue: 131

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

Use copy column names to reinitialize key sequence
[GENERAL] Generating synthetic keys on copy 9-Oct-2006

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
	);
You can copy out only the name and the role like this:
	\COPY people_131 (name, role) to 'people.dat'
And then you will want to delete your original data.
	DELETE FROM people_131;
Then you can reset your sequence. Perhaps you found out later that you wanted to start the id field at 100 instead of 1. To restart your sequence and copy the data back in use:
	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));
But not on the lower cased 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
At the end of the transaction the constraints are considered and all are found to be correct. We know that because there was no error message on 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


Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Top
Google
Search General Bits & varlena.com Search WWW