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/pg_hba.com file to trust for user postgres. Comment out the previous line pertaining to postgres so you can put it back.

local	DATABASE	USER	METHOD
local	all	postgres	trust

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

	ALTER USER postgres WITH PASSWORD 'newpassword';

Contributors: bruce bedouglas at earthlink.net Richard Huxton dev at archonet.com

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;

	 num
	-----
	   2
	   3
	   5
	   6
	   8
	   9
	  12
	(7 rows)

	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);

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.

	SELECT
	   CASE WHEN start = finish THEN start::text
	   ELSE start || '-' || finish END AS gap
	FROM (
	      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
	GROUP BY 1
	) AS foo;

	   gap 
	  -----
	   12
	   8-9
	   5-6
	   2-3
	(4 rows)

Contributors: josue josue at lamundial.hn, Harald Fuchs use_reply_to at protecting.net, Simon Riggs simon at 2ndquadrant.com, Alvaro Herrera alvherre at surnet.cl, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com

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;

	DELETE
	FROM child_table
	WHERE NOT EXISTS (select 1
	                  from parent_table
	                  where parent_id = child_table.parent_id
	)

Contributors: Roman F romanf at fusemail.com, Tom Lane tgl at sss.pgh.pa.us, Greg Stark gsstark at mit.edu