FOR EACH STATEMENT trigger example in PostgreSQL

OLD and NEW are null or not defined in a statement-level trigger. Per documentation: NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in … Read more

ERROR: function … does not exist and HINT: No function matches the given name and argument types

Your function has a couple of smallint parameters. But in the call, you are using numeric literals that are presumed to be type integer. A string literal or string constant (‘123’) is not typed immediately. It remains type “unknown” until assigned or cast explicitly. However, a numeric literal or numeric constant is typed immediately. The … Read more

PostgreSQL: create database with UTF8 encoding same as in MySQL (including character set, encoding, and lc_type)

Yes, you can be more specific. For example: CREATE DATABASE “scratch” WITH OWNER “postgres” ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’; Also I recommend to read the following pages about locales and collations in PostgreSQL: http://www.postgresql.org/docs/current/interactive/locale.html http://www.postgresql.org/docs/current/interactive/collation.html

How to get the OID of a Postgres table?

To get a table OID, cast to the object identifier type regclass (while connected to the same DB!). This finds the first table (or view etc.) with the given (unqualified) name along the search_path or raises an exception if not found: SELECT ‘mytbl’::regclass::oid; Schema-qualify the table name to remove the dependency on the search path: … Read more

How to show the trigger(s) associated with a view or a table in PostgreSQL?

This will return all the details you want to know select * from information_schema.triggers; or if you want to sort the results of a specific table then you can try SELECT event_object_table ,trigger_name ,event_manipulation ,action_statement ,action_timing FROM information_schema.triggers WHERE event_object_table=”tableName” — Your table name comes here ORDER BY event_object_table ,event_manipulation; the following will return table … Read more

Declare local variables in PostgreSQL?

Postgresql historically doesn’t support procedural code at the command level – only within functions. However, in Postgresql 9, support has been added to execute an inline code block that effectively supports something like this, although the syntax is perhaps a bit odd, and there are many restrictions compared to what you can do with SQL … Read more

LANGUAGE SQL vs LANGUAGE plpgsql in PostgreSQL functions

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

How to stop a Postgres script when it encounters an error?

I think the solution to add following to .psqlrc is far from perfection \set ON_ERROR_STOP on there exists much more simple and convenient way – use psql with parameter: psql -v ON_ERROR_STOP=1 better to use also -X parameter turning off .psqlrc file usage. Works perfectly for me p.s. the solution found in great post from … Read more