You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant. DO $$ BEGIN EXECUTE format(‘CREATE TABLE %I AS SELECT * FROM backup’, ‘backup_’ … Read more
One old trick is using parser for this purpose: postgres=# select e’Telefon\u00ED kontakty’; ?column? ——————- Telefoní kontakty (1 row) CREATE OR REPLACE FUNCTION public.unescape(text) RETURNS text LANGUAGE plpgsql AS $function$ DECLARE result text; BEGIN EXECUTE format(‘SELECT e”%s”’, $1) INTO result; RETURN result; END; $function$ It works, but it is SQL injection vulnerable – so you … Read more
Untangled, simplified and fixed, it might look like this: SELECT to_char(s.tag,’yyyy-mm’) AS monat , count(t.id) AS eintraege FROM ( SELECT generate_series(min(date_from)::date , max(date_from)::date , interval ‘1 day’ )::date AS tag FROM mytable t ) s LEFT JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1 GROUP BY 1 ORDER BY 1; db<>fiddle here … Read more
That is expected and documented behaviour: From the manual: Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within … Read more
DO command vs. PL/pgSQL function A DO command does not return rows. You can send NOTICES or RAISE other messages (using default LANGUAGE plpgsql), or you can write to a (temporary) table and later SELECT from it to get around this. But really, create a function instead, where you can define a return type with … Read more
Long story short: Install hstore in the template1 database: psql -d template1 -c ‘create extension hstore;’ Step-by-step explanation: As stated by the PostgreSQL documentation: CREATE EXTENSION loads a new extension into the current database. Installing an extension is database-specific. The following returns you the current database name: $ psql -c ‘select current_database()’ current_database —————— username … Read more
Perhaps using SIMILAR TO would work ? SELECT * from table WHERE column SIMILAR TO ‘(AAA|BBB|CCC)%’;
Postgres 9.5 or later Or use array_position(). Basically: SELECT array_position(arr, NULL) IS NOT NULL AS array_has_null See demo below. Postgres 9.3 or later You can test with the built-in functions array_remove() or array_replace(). Postgres 9.1 or any version If you know a single element that can never exist in your arrays, you can use this … Read more
With Postgres 9.6 this can be done using the option if not exists ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
RETURNING That’s possible with a single round-trip to the database: INSERT INTO tbl(filename) VALUES (‘my_filename’) RETURNING tbl_id; tbl_id would typically be a serial or IDENTITY (Postgres 10 or later) column. More in the manual. Explicitly fetch value If filename needs to include tbl_id (redundantly), you can still use a single query. Use lastval() or the … Read more