No registered users in community rubick
in last 10 minutes
in last 10 minutes
Postgres notes
This is a page for miscellaneous notes on Postgres.
Remember that psql 7.3 doesn't like tabs
Example of a trigger in Postgres
Thanks to Priti Sawant for his help on this.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();
Common errors
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.ERROR: Relation "rl_resources_revisions" with OID 330733 no longer exists
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
Upgrading from 7.2.x to 7.3.x
Upgrade script that handles the function and view truncation problemsRemember that psql 7.3 doesn't like tabs
Installation troubleshooting
- If you get an error message like this during ./configure :
Then you need to install the libreadline4-dev package (which includes the header files you need). On Debian: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.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=118550Dealing 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