{"id":48,"date":"2005-03-28T21:12:18","date_gmt":"2005-03-29T01:12:18","guid":{"rendered":"http:\/\/hoogervorst.dyndns.org\/~arthur\/wordpress\/?p=48"},"modified":"2005-03-28T21:12:26","modified_gmt":"2005-03-29T01:12:26","slug":"postgres-stuff-1","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=48","title":{"rendered":"Postgres stuff #1"},"content":{"rendered":"

T<\/span>echnical 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.\n<\/p>\n

Here’s the first finding: my personal Postgres (debian) is a bit more advanced than the ones we use at work:\n<\/p>\n


\n>select version();
\nPostgreSQL 7.4.2 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.3 (Debian 20040401)
\n<\/code>\n<\/p>\n

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.\n<\/p>\n

Postgres version 7.4 and up have the ability to query the information_schema<\/em> tables to find out which extra features are supported (a la Oracle\/Sybase\/MSSql). This works easy as in 1…2…3:\n<\/p>\n

>select * from information_schema.sql_features;<\/p>\n

<\/code>\n<\/p>\n

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:\n<\/p>\n


\nSELECT oid, nspname, pg_get_userbyid(nspowner) AS namespaceowner, nspacl
\nFROM pg_namespace
\nORDER BY nspname
\n<\/code>\n<\/p>\n

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.\n<\/p>\n


\nSELECT oid, relname, pg_get_userbyid(relowner) as tableowner, relacl, relhasoids
\nFROM pg_class
\nWHERE ((relkind = 'r') OR (relkind = 's'))
\nAND relnamespace = 2200::oid \/* my public has an oid of 2200: yours may or may not *\/
\nORDER BY relname
\n<\/code>\n<\/p>\n

Naturally you will need to find triggers and typecasts and functions of course. Later more on those.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/48"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=48"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/48\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=48"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=48"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=48"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}