Technical stuff: I have finally some time to break into some specific Postgres stuff and do something for this database as many others have always done for MySQL. Nothing earth-shattering.
Here’s the first finding: my personal Postgres (debian) is a bit more advanced than the ones we use at work:
>select version();
PostgreSQL 7.4.2 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.3 (Debian 20040401)
Note: via the Postgres mailinglist: be careful when trying to update your Debian Postgres version to the latest 8 series. Dump your tables. Dump your tables.
Postgres version 7.4 and up have the ability to query the information_schema tables to find out which extra features are supported (a la Oracle/Sybase/MSSql). This works easy as in 1…2…3:
>select * from information_schema.sql_features;
Other tables in information_schema that can be queried are: sql_implementation_info, sql_languages, sql_packages, sql_sizing and sql_sizing_profiles (and so on). If you know what these tables are for, have fun. If you run an older version of postgres, do the queries on pg_namespace and pg_type to get the table names. The following query basically fetches all tables for a specific database:
SELECT oid, nspname, pg_get_userbyid(nspowner) AS namespaceowner, nspacl
FROM pg_namespace
ORDER BY nspname
This fetches all the namespaces: naturally, we have to look for the ‘public’ namespace. Use its OID to find the real set of tables within the (currently) selected database.
SELECT oid, relname, pg_get_userbyid(relowner) as tableowner, relacl, relhasoids
FROM pg_class
WHERE ((relkind = 'r') OR (relkind = 's'))
AND relnamespace = 2200::oid /* my public has an oid of 2200: yours may or may not */
ORDER BY relname
Naturally you will need to find triggers and typecasts and functions of course. Later more on those.