It’s the recurring problem of SELECT or INSERT under possible concurrent write load, related to (but different from) UPSERT (which is INSERT or UPDATE). This PL/pgSQL function uses UPSERT (INSERT … ON CONFLICT ..) to INSERT or SELECT a single row: CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) LANGUAGE plpgsql AS $func$ BEGIN … Read more
PostgreSQL has the uuid-ossp extension which ships with the standard distributions and it has 5 standard algorithms for generating uuids. Note that a guid is the Microsoft version of a uuid, conceptually they are the same thing. CREATE EXTENSION “uuid-ossp”; Then: SELECT uuid_generate_v4(); Note also that, once you installed the extension, PostgreSQL has an actual … Read more
From the fine manual: 36.1. Overview of Trigger Behavior […] For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement. And from … Read more
If you want to skip the rows with invalid JSON, you must first test if the text is valid JSON. You can do this by creating a function which will attempt to parse the value, and catch the exception for invalid JSON values. CREATE OR REPLACE FUNCTION is_json(input_text varchar) RETURNS boolean AS $$ DECLARE maybe_json … Read more
I just encountered the same issue days ago. It does not work if we add the changeset into changelog.xml file using the format below: <include file=”path/to/sqlfile” /> To work around, I use another format: <changeSet author=”authour_name” id=”your_id”> <sqlFile path=”path/to/sqlfile” splitStatements=”false”/> </changeSet> Here is the link which gives a brief explanation to Problem with dollar-quoted-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
DO LANGUAGE plpgsql $$ DECLARE BEGIN execute ‘ create temporary table t as SELECT NOW() ‘; END $$; select * from t;
You can use EXCEPTION WHEN OTHERS clause: BEGIN do something EXCEPTION WHEN OTHERS THEN handle any exception END; Without some exception a using of this clause is not good idea. The debugging, issue diagnostics can be terrible when you use this pattern. It is strong feature (sometimes necessary), but dangerous! Attention – the entry to, … Read more
Try CREATE TEMP TABLE mytable AS SELECT * FROM orig_table; Per http://www.postgresql.org/docs/current/static/sql-selectinto.html CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset … Read more
Modern PostgreSQL format() has a built-in way to escape identifiers. Simpler than before: CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘INSERT INTO %I.%I SELECT $1.*’ , TG_TABLE_SCHEMA, TG_TABLE_NAME || ‘shadow’) USING OLD; RETURN OLD; END $func$; Works with a VALUES expression as well. db<>fiddle here Old sqlfiddle Major points … Read more