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

6-June-2005 Issue: 112

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

Know your pg_hba.conf
[GENERAL] a way to reset the postgres password to nothing.. 30-May-2005

If you are being prompted for a password for postgres when using psql -U postgres and you haven't set a password for postgres, then it is possible the system did it for you.

To reset the password properly, change your $PGDATA/ file to trust for user postgres. Comment out the previous line pertaining to postgres so you can put it back.


Then you can use ALTER USER to change that password for postgres.

	ALTER USER postgres WITH PASSWORD 'newpassword';
Now you want to put your $PGDATA/pg_hba.conf file back the way you found it. You should be able to use any client with your new postgres password.

Contributors: bruce bedouglas at Richard Huxton dev at
JOINS and WHERE clauses and Gaps in Series
[GENERAL] Missing numbers 31-May-2005

I have a table of numbers with a column, num, which contains values 1, 4, 7, 10 and 11. And I want to find out what numbers in the series num are missing.

There are many different ways to do this. It can be done in a plperl or plpython Set Returning Function (an exercise for the reader :) and it can be done in the following ways using the generate_series() function. And you can find the ranges of missing numbers using SQL if you want.

generate_series(start,end) is a Set Returning Function which generates an integer series of numbers from start to end. Joining the series generation to the number table with an outer join will list the missing numbers. Those are the ones where the value from the number table is missing. When using generate_series() notice the alias is that of a function and the value is that of a column.

	SELECT g.num
	FROM generate_series ((SELECT min(num) FROM numbers),
	                      (SELECT max(num) FROM numbers)) AS g(num)
	LEFT JOIN numbers ON numbers.num = g.num
	WHERE numbers.num IS NULL;

	(7 rows)
An alternative solution using generate_series was this:
	SELECT g.num
	FROM generate_series ((SELECT min(num) FROM numbers),
	                      (SELECT max(num) FROM numbers)) AS g(num)
	WHERE g.num NOT IN (SELECT num FROM numbers WHERE num IS NOT NULL);
This does not take advantage of the JOIN syntax, but otherwise is perfectly correct. For some it is easier to read, but I prefer the previous function.

A different approach to the problem is to select the ranges of missing numbers. This was also addressed in issue #35. These final two little queries show the ranges of missing values by comparing the second (or next) number and the number before then next valid number. The first uses limit with order by to get the next existent value and decrements it by one. That should be the last missing value in the range.

	select o1.num+1 as first,
	((select num 
	      from numbers 
	      where num > o1.num+ 1 
	      order by num limit 1)) -1 as last
	from numbers as o1
	where o1.num+1 not in (select o2.num from numbers as o2)
	order by num;

	    first | last
	   2 |    3
	   5 |    6
	   8 |    9
	   12 |   12
	   14 |
	   (5 rows)

The second that shows a range shows it as one text column, but that is easily changed if you like. It starts at the end and goes to the beginning.

	   CASE WHEN start = finish THEN start::text
	   ELSE start || '-' || finish END AS gap
	      SELECT hole.num AS start, MIN(n.num)-1 AS finish
	      FROM numbers n, 
	     (SELECT num+1 AS num FROM numbers n1
	      WHERE NOT EXISTS (SELECT 1 FROM numbers n2 WHERE n2.num = n1.num+1)
	      ) AS hole
	WHERE n.num > hole.num
	) AS foo;

	(4 rows)

Contributors: josue josue at, Harald Fuchs use_reply_to at, Simon Riggs simon at, Alvaro Herrera alvherre at, Tom Lane tgl at, elein at
Different Plans for Deleting Foreign Keys
[GENERAL] Deleting orphaned records to establish Ref Integrity 02-Jun-2005

We all know that all foreign keys should have foreign key constraints defined. But sometimes they don't quite get defined, yet. If so, at some time it is necessary to eliminate, in bulk, orphaned records that have no referring parents. This, of course, should be in preparation for installing foreign key constraints properly.

In this article we have three different SQL statements which can eliminate the orphaned rows. The first is the simplest to understand. The second is a simple outer join which would scale better for a large number of rows. The third would not be too speedy but may take advantage of indexes.

	DELETE FROM child_table WHERE parentid NOT IN
	   (SELECT parentid FROM parent_table)

	SELECT child_table.parentid INTO tmp_table
	FROM child_table LEFT JOIN parent_table
	   ON (child_table.parentid = parent_table.parentid)
	WHERE parent_table.parentid IS NULL;

	FROM child_table
	                  from parent_table
	                  where parent_id = child_table.parent_id
In any case, if your query is too slow, you may want to take a look at your value of sort_mem in your postgresql.conf file.

Contributors: Roman F romanf at, Tom Lane tgl at, Greg Stark gsstark 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