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

21-Apr-2003 Issue: 22

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

Foreign Key ERROR is not obvious
[GENERAL] ERROR: UNIQUE constraint ... not found? 02-Apr-2003

In 7.2, a SERIAL column had a unique index on it by default. In 7.3, this is no longer the case. Foreign Key definitions which depended on the predefined uniqueness of the referenced column now give an error.

	CREATE TABLE person (
	        id      SERIAL,
	        name    VARCHAR NOT NULL
	psql:test.sql:6: NOTICE:  CREATE TABLE will create implicit sequence 
	'person_id_seq' for SERIAL column ''
	CREATE TABLE session (
	        id                      SERIAL,
	        person_id               INTEGER NOT NULL,
	        FOREIGN KEY (person_id) REFERENCES person (id)
	psql:test.sql:12: NOTICE:  CREATE TABLE will create implicit sequence 
	'session_id_seq' for SERIAL column ''
	psql:test.sql:12: NOTICE:  CREATE TABLE will create implicit trigger(s) for 
	FOREIGN KEY check(s)
	psql:test.sql:12: ERROR:  UNIQUE constraint matching given keys for 
	referenced table "person" not found

The solution for this in 7.3 is to explicity define the id column in the person table to be UNIQUE or be a PRIMARY KEY (which implies uniqueness).

Contributors: Ed L. pgsql at, Stephan Szabo sszabo at, Nigel J. Andrews nandrews at, Dennis Gearon gearond at
Known bug causes table locking
[GENERAL] 'DROP INDEX' kills stored procedures 02-Apr-2003

If postgresql cached a query plan using an index that was subsequently dropped, the cache isn't always cleared. This causes the query to try to use the dropped index and the error

'Relation [OID of the index] does not exist'.
is displayed.

Query plans are only cached by connection and only for prepared statements and queries in plpgsql functions. This problem only occurs if indexes are dropped while those plans are cached. If it is necessary to drop and recreate indexes on live systems, you can avoid the caching problems by closing the old connections. Sometime closing the old connections is not reasonable if the client caches the connections, however, in that case you may need to plan your drop and recreate indexes more carefully.

The ultimate fix for this will be to notice when there is a DDL update and then clear the query caches. In the meanwhile it is categorized as a bug. (Tom may have this fixed by now...:-)

Contributors: Vlad Krupin vlad at, scott.marlowe scott.marlowe at, Tom Lane tgl at
Explaining the cost
[GENERAL] calculating time 16-Apr-2003

When you use EXPLAIN to show the cost of running a query, you get output that looks like this:

 Nested Loop  (cost=0.00..5662254656.82 rows=138 width=199)
The question is: What is the unit value for "cost"?

The there is no real time equivalent for "cost", however, it is a time unit. And a high value of cost is a lot and the low value is not much. Costs are consistent on an installation on a machine and can reasonably be extrapolated to similar machines and configurations.

Using EXPLAIN ANALYZE will give you an idea of the value of cost on your machine and installation. The two examples below are from my machine. The first is a fast query. The second is an unfortunate view that uses a NOT IN clause.

 explain analyze select * from pg_database;
 QUERY PLAN                                              
   Seq Scan on pg_database 
   (cost=0.00..1.08 rows=8 width=182) (actual time=0.08..0.11 rows=9 loops=1)
 Total runtime: 0.20 msec
(2 rows)

 explain analyze select * from badacct_route;
 QUERY PLAN                                                         
 Seq Scan on account a 
 (cost=0.00..163887.65 rows=194 width=383)
 (actual time=7479.80..10642.65 rows=1 loops=1)
   Filter: (subplan)
     ->  Seq Scan on routing_numbers  
         (cost=0.00..844.70 rows=25170 width=32)
         (actual time=0.02..19.46 rows=8225 loops=388)
 Total runtime: 10642.75 msec
(5 rows)
With this absurdly small sample, it looks like cost is equivalent to 10 times the number of seconds it takes to run the query. But clearly, more samples and more control over the machine would be needed to be exact. Luckily, exact is not the point. The cost values from EXPLAIN are meant to be read relatively.

Contributors: Johnson Shaunn SJohnson6 at, Alvaro Herrera alvherre at, Tom Lane tgl at, Bruno Wolff III bruno at elein at
Changing a column type
[GENERAL] How to change data type in column ? 15-Apr-2003

NOTE: As of 8.0 there is an ALTER command to change the type of a column. It is:

	ALTER TABLE name ALTER COLUMN column TYPE type [USING expression] 

There is not an ALTER command which will change the type of a column in a table. Changing types involves changing the storage of the table so it cannot be done with a simple update of the system catalog table. There is also the implicit requirement that the before type and after type be compatible. That is, that the before type must be able to be cast or manipulated into the proper format for the after type.

To change the type of a column "by hand" there are two prevalent methodologies. The first is to recreate the table and the second is to add and drop a column. The first is best because its result is cleaner. However, you have to drop and recreate any dependencies on the the table. If there are many dependencies on the table, then adding and dropping a column might be better.

In these examples, the changes we want to do are as follows:
Table acct
ColumnOld TypeNew Type
nametextno change
idintno change

Recreating the table

	--> Drop dependencies as necessary
	CREATE TABLE accttemp AS 
		SELECT name, id, balance::float, num::numeric(10,2) FROM acct;
	--> Check the data in the accttemp table to be sure your casts worked.
	--> Don't skip this step. Really.
	ALTER TABLE accttemp RENAME TO acct;
	--> Recreate dependencies as necessary

Adding and Dropping a column

	ALTER TABLE RENAME balance TO balance_old;
	ALTER TABLE ADD COLUMN balance float;
	ALTER TABLE ADD COLUMN num numeric(10,2);
	UPDATE acct SET balance=balance_old::float, num=num_old::numeric(10,2);
	--> Check the data in the acct table to be sure your casts worked.
	--> Don't skip this step. Really.

Contributors: Vitali Djatsuk Vitali.Djatsuk at, Richard Huxton dev at, scott.marlowe scott.marlowe at, mallah at
Why those comments in functions never worked right...
[GENERAL] does parser still parse those comment out lines? 19-Apr-2003

Did you ever wonder why some comments in a CREATE FUNCTION statement could be commented out and why some could not? Now we know why.

You would think that the following lines would be parsed as you meant them. However, the parser grabs the string without paying attention to anything inside them except the ordinary escape characters. As it should--it is a string literal.

The entire function definition is itself a string and if you have any stray unescaped single quotes within that string, then the string will "end" there causing a parsing problem with the rest of the text.

	drop function test();
	create or replace function test() returns integer as '
		-- raise NOTICE ''can't comment out!'';
		raise notice ''didn''''t comment out'';
		return 1;
	language 'plpgsql';
In this example, the stray quote in can't will cause parsing errors. Changing it to four or zero quotes should allow the commenting and uncommenting to work correctly.

Contributors: weiping He laser at, davidgn at, Tom Lane tgl at
Joining with aggregation
[GENERAL] Please some help on a join question with sum aggregate 19-Apr-2003

This question involves tables AA and BB.
Table AA:
Table BB:

We want to get the id and sequence numbers from table AA and the sum of AA's price and the totals of the corresponding rows in BB.

There were two suggestions for this query. The first uses a subselect to sum the price from BB for each entry in AA. Both queries use a COALESCE() function to handle the case where there are no entries in BB corresponding to AA. In that case a NULL would be returned--we coalesce the NULL into a 0 so adding it to AA's price works.

	SELECT, aa.seqno, aa.price + COALESCE(
		 (SELECT SUM(bb.price) 
			FROM bb 
			WHERE = AND bb.seqno = aa.seqno), 
		0) AS price
	FROM aa;

In the second suggestion a LEFT OUTER JOIN is used to create the base table and the BB's price is summed. We are grouping by AA's id and sequence number. This means the sum will restart after each sequence number in AA. The group by also assumes that id and seqno are primary keys.

	SELECT, AA.seq, AA.price + coalesce(sum(BB.price),0)
	FROM AA left outer join BB using (id, seq) 
	GROUP BY, AA.seq, AA.price;

Contributors: Feite Brekeveld f.brekeveld at, Oliver Elphick olly at, elein at

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

Search General Bits & Search WWW