Postgres notes

This is a page for miscellaneous notes on Postgres.

Example of a trigger in Postgres

create function modify_date() returns OPAQUE as '
 begin
	new.modified_date := ''now'';
	return new;
 end;
 ' language plpgsql;

create trigger modify_date_trig 
before insert or update on pan_type
for each row
execute procedure modify_date();

Thanks to Priti Sawant for his help on this.

Common errors

ERROR:  Relation "rl_resources_revisions" with OID 330733 no longer exists

This is Postgres' helpful way of telling you to recreate your view. Or it might mean that you should say "CREATE OR REPLACE" when you drop and create a function.

Hierarchical queries

See using tree_sortkey for OpenACS.

Getting around 16 argument limit

See how to update after an insert.

Or upgrade to Postgres 7.3.x, which doesn't have that limit.

Dates

To specify the present date and time, use CURRENT_TIMESTAMP. If you just want the date, use CURRENT_DATE.

Also, adding integers to dates in Postgres

Also, selecting a series with Postgres:

select current_date + s.a as dates from generate_series(0,14,7) as s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19

(3 rows)

to_date vs. to_timestamp (also info on coalesce and truncate vs. date_trunc)

Reserved words

• Postgres reserved words list

Upgrading from 7.2.x to 7.3.x

Upgrade script that handles the function and view truncation problems
Remember that psql 7.3 doesn't like tabs

Installation troubleshooting

• If you get an error message like this during ./configure :

  checking for readline... no
  configure: error: readline library not found
  If you have readline already installed, see config.log for details on the
  failure.  It is possible the compiler isn't looking in the proper directory.
  Use --without-readline to disable readline support.
  

  Then you need to install the libreadline4-dev package (which includes the header files you need). On Debian:

  apt-get install libreadline4-dev
  

Postgres query debugging

*.xql files are loaded into memory at start. They take precedence over queries quoted in *.tcl files.

Sometimes Oracle syntax or reserved words make it into postgresql queries. Here (is the start of a list) of equivalents

• Oracle > Postgres equivalent
• sysdate = current_timestamp

Querying multiple databases from OpenACS

http://openacs.org/forums/message-view?message_id=118550

Dealing with concurrent issues

How do you make sure that user A and user B don't clobber each other's changes?

http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-FOR-UPDATE