Sequences are guaranteed to be unique and in numeric order, but they are not guaranteed to be sequential. Sometimes sequences are cached for the query and if they are not used, they are skipped. Rows could also be deleted in the middle of a sequence. This is all OK according to how sequences should work.

Occasionally you may find that you want to know where the holes in your sequences are. This is pretty rare if the sequences are meeting your uniqueness and ordering needs. Of course, if skipped numbers are that important, you may want to use something other than a sequence...

The SQL below should be able to find the sequence holes in your table. It is left as an exercise to the reader to write this solution as a plpython function, saving the previous value in SD[] eliminating the need for the EXISTS subquery. :-)

SELECT
  CASE WHEN start = finish THEN start::text
       ELSE start || '-' || finish END AS gap
FROM (
  SELECT hole.id AS start, MIN(f.id)-1 AS finish
  FROM fred f, (
    SELECT id+1 AS id FROM fred f1
    WHERE NOT EXISTS (SELECT 1 FROM fred f2 WHERE f2.id = f1.id+1)
  ) AS hole
  WHERE f.id > hole.id
  GROUP BY 1
) AS wilma;


Here is the table I used to test with:

CREATE TABLE fred (
  id INTEGER PRIMARY KEY
);

INSERT INTO fred VALUES (1);
INSERT INTO fred VALUES (2);
INSERT INTO fred VALUES (3);
INSERT INTO fred VALUES (5);
INSERT INTO fred VALUES (6);
INSERT INTO fred VALUES (7);
INSERT INTO fred VALUES (10);
INSERT INTO fred VALUES (16);
INSERT INTO fred VALUES (18);
INSERT INTO fred VALUES (30);

Contributors: Madhavi Daroor madhavi at zoniac.com, Henshall, Stuart shenshall at tnp-southwest.co.uk, greg at turnstep.com

Release Notes [GENERAL] Where can I find the release notes for 7.3.3? 17-Jul-2003

Release notes come with the PostgreSQL latest release Administrator's Guide documentation with an installed PostgreSQL system or they can be found under the PostgreSQL Docs Link . Look for Appendix A in the Administrator's Guide.

To see the latest greatest versions you can look in the latest development version of the docs.

If you are trying to decide to upgrade, it is strongly suggested that you review the changes between releases so that you can make the most informed and timely choices.

Contributors: P G pg_dba at yahoo.com, Alvaro Herrera alvherre at dcc.uchile.cl, Henrik Steffen steffen at city-map.de, Tom Lane tgl at sss.pgh.pa.us

Alphanumeric Sorting [SQL] numerical sort on mixed alpha/numeric data 16-Jul-2003

When you have an alphanumeric field, natural sorting will be alphabetical. This means that 11 will come before 3, for example.

If your table contained:

	select * from an_test order by code;
	  name   | code 
	---------+------
	 one     | 1
	 eleven  | 11
	 three   | 3
	 five    | 5
	 seven   | 7
	 nine    | 9
	 A-two   | A1
	 A-four  | A4
	 C-ten   | C10
	 C-eight | C8
	 B-six   | D6

	select name, code from
		( select *, 
			(case when code ~ '^[0-9]+' 
			then code::integer
			else NULL
			end) as number 
		from an_test ) foo
order by number, code;

	  name   | code 
	---------+------
	 one     | 1
	 three   | 3
	 five    | 5
	 seven   | 7
	 nine    | 9
	 eleven  | 11
	 A-two   | A1
	 A-four  | A4
	 C-ten   | C10
	 C-eight | C8
	 B-six   | D6

Contributors: Dmitry Tkach dmitry at openratings.com, Gary Stainburn gary.stainburn at ringways.co.uk, Bruno Wolff III bruno at wolff.to, elein at varlena.com

Order Matters in the pg_hba.conf [GENERAL] Help with privilege or pg_hba.conf 19-Jul-2003

You can use pg_hba.conf to restrict a user to a particular database. pg_hba.conf usually lives in your PGDATA directory and is usually owned by postgres. This is the file to change to enable and disable connections to your database.

If you want to allow everyone else access but limit the user cool access to the cool database only, set up the pg_hba.conf file like this:

# If you want to allow non-local connections, you need to add more
# "host" records.  Also, remember TCP/IP connections are only enabled
# if you enable "tcpip_socket" in postgresql.conf.

# TYPE  DATABASE USER   IP-ADDRESS   IP-MASK           METHOD

# For user cool on db cool
local  cool     cool                                   password sameuser
host    all     cool    127.0.0.1    255.255.255.255   reject
host    all     cool    x.x.x.x      255.255.255.224   reject
host    all     cool    192.168.0.0  255.255.255.0     reject
#For local others
local   all      all                                   password sameuser
host    all      all    127.0.0.1    255.255.255.255   trust
host    all      all    x.x.x.x      255.255.255.224   trust
#

Contributors: Arcadius A. ahouans at sh.cvut.cz, Richard Poole rp at jalapeno.crazydogs.org, Andrew Gould andrewgould at yahoo.com

Using variables in SQL scripts [GENERAL] what's the scope of psql parameter values? 17-Jul-2003

You can set and use your own SQL variable names within a psql session or SQL script run by psql. Displaying the SQL variables is not well documented (I couldn't find it.)

command	function
\set	shows all SQL variables set
\set foo bar	sets SQL variable foo to value bar

To see the SQL variable in an interactive session or for record, try \echo. Here we are setting foo to "harold" and echoing it.

	e=# \set foo harold
	e=# \echo Variable foo is set to :foo
	Variable foo is set to harold

	e=# \set when now()
	e=# \echo :when
	now()
	elein=# select :when;
              	now              
	-------------------------------
 	2003-07-20 18:39:24.339276-07
	(1 row)

Of course SQL variables can be used in ordinary SQL statements.

	e=# \set newuser huey
	e=# insert into users (user_id, user_name) values (99,:newuser);
	INSERT 2037329 1

There is one warning however, and that is that if the SQL variable name :foo is enclosed in quotes, it will not be evaluated. Notice that when the users record was inserted above, the user_name is a text field but it did not require quotes. However, if you were creating, for example, a function, you could not use a SQL variable in the function because the body of the function is quoted.

When more global replacement of variables is necessary but the task is simple and straight forward, you can also just write a shell script where the shell variables are substituted wherever you need them.

You'll need to have the single quotes in that case. Also, if you wanted to create a plpgsql function in a bash script, you'll have to backslash the dollar signs for the plpgsql parameters in the body of the function.

By using singleton selects you can query the database within the shell script by using the -Atc options in a backticked command. In the case below, some funky permission management is being done with the boolean function canupdateusers().

	#!/bin/bash

	# validate the variables in real life
	user_id=$1
	user_name=$2

	# which database?
	echo "Enter database name (default is $USER):"
	read db
	[ -z $db ] && db=$USER

	perm=`psql -Atc "select canupdateusers( $USER );"`;
	if [ $perm != 't' ]
	then
		echo "$User is not allowed to update the users table."
		exit 1;
	fi

	pgsql $db << END

	insert into users (user_id, user_name) values ( $user_id , '$user_name' );

	END
	exit 0;

Both SQL variables and shell scripts with variables are particularly helpful in maintaining and updating schemas in multiple database.

Contributors: Dick Wieland dick.wieland at wiline.com, Doug McNaught doug at mcnaught.org, Nigel J. Andrews nandrews at investsystems.co.uk, elein at varlena.com