You have to enable debugging in two places. On PGAdmin and on the database itself. That article you referenced does a wonderful job explaining it but there were some nuances. PGAdmin When updating your postgresql.conf file to load the debugging library, I was running PGAdmin on Windows so the file was here: C:\Program Files\PostgreSQL\9.4\data\postgresql.conf And … Read more
Since PostgreSQL 9.1 There is the convenient FOREACH which can loop over slices of arrays. The manual: The target variable must be an array, and it receives successive slices of the array value, where each slice is of the number of dimensions specified by SLICE. DO $do$ DECLARE m text[]; arr text[] := ‘{{key1,val1},{key2,val2}}’; — … Read more
Simpler, shorter, faster: EXISTS. IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN — do something END IF; The query planner can stop at the first row found – as opposed to count(), which scans all (qualifying) rows regardless. Makes a big difference with big tables. The difference is small for a condition … Read more
In Postgres functions can be overloaded, so parameters are necessary to distinguish overloaded functions. To unambiguously identify a function you can put only types of its parameters. DROP FUNCTION IF EXISTS Foo(INT);
The query above is very bad as it is really slow. I would recommend this official version: http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns if schema is needed the query is as follows SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute, pg_namespace WHERE pg_class.oid = ‘foo’::regclass AND indrelid = pg_class.oid AND nspname=”public” AND pg_class.relnamespace = pg_namespace.oid AND pg_attribute.attrelid = pg_class.oid AND … Read more
You can define optional parameters by supplying a default value. create function foo(p_one integer default null, p_two integer default 42, p_three varchar default ‘foo’) returns text as $$ begin return format(‘p_one=%s, p_two=%s, p_three=%s’, p_one, p_two, p_three); end; $$ language plpgsql; You can “leave out” parameters from the end, so foo(), foo(1) or foo(1,2) are valid. … Read more
Column / Row … I don’t need the transactional integrity to be maintained across the entire operation, because I know that the column I’m changing is not going to be written to or read during the update. Any UPDATE in PostgreSQL’s MVCC model writes a new version of the whole row. If concurrent transactions change … Read more
It seems like pg_restore tries to restore some extra data which you don’t own. Try to add -n public option to your pg_restore command line. It will tell pg_restore restore only contents of public schema. Your command line should looks like pg_restore -U username -c -n public -d database_name
SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more
Don’t use CREATE TYPE to return a polymorphic result. Use and abuse the RECORD type instead. Check it out: CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE ret RECORD; BEGIN — Arbitrary expression to change the first parameter IF LENGTH(a) < LENGTH(b) THEN SELECT TRUE, a || b, ‘a shorter than b’ … Read more