









If you are being prompted for a password for postgres when
using 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.
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.
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)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. 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 89 56 23 (4 rows)
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 )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.


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 