postgresql

postgresql error: canceling statement due to user request

by

We have figured out the the cause of this issue. It’s explained by buggy implementation of setQueryTimeout() in latest JDBC drivers 9.2-100x. It might not happen if you open / close connection manually, but very often happens with connection pooling in place and autocommit set to false. In this case, setQueryTimeout() should be called with … Read more

FOR EACH STATEMENT trigger example in PostgreSQL

by

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

by

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)

by

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?

by

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?

by

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?

by

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