Advertisements I want to create an array from two variables p, q. Here is my attempt: DO $code$ DECLARE p text := ‘Hello’; q text := ‘world!’; ta text[]; –text array BEGIN ta := {p, q}; RAISE INFO ‘% %’, ta[1], ta[2]; END $code$; but it doesn’t work postgres=# DO postgres-# $code$ postgres$# DECLARE postgres$#… Read More How to create an array from variables in postgresql?

Advertisements This is so simple query. I need to update Bookings tables’, LinkedBookings column (int array data type) to it’s own Id. Below query didn’t work for me. update public."Booking" b set "LinkedBookings" = {"Id"} Above is giving below error, SQL Error [42601]: ERROR: syntax error at or near "{" I searching thorough the internet… Read More Postgres update Query for Array column

Advertisements I have a table formatted like this (obviously, simplified) : session_id timestamp action message 4de88be3-2316-4efa-8e17-58a2365534d9 2:04 4363d58b-c9fe-43a1-b636-c65822329aa3 initial 4de88be3-2316-4efa-8e17-58a2365534d9 2:05 d4294aaf-3fee-4154-a3de-b2f9c05a0cf1 queued 4de88be3-2316-4efa-8e17-58a2365534d9 2:10 dc40eaec-2aed-4b24-9b8e-ff1e25194036 connected 4de88be3-2316-4efa-8e17-58a2365534d9 2:32 dd93f0d4-7db9-4a68-876b-956db9300841 hangup noting that multiple sessions could be happening at the same time of course, I’m trying to figure out how to write a query to… Read More pgsql – return row containing data from multiple rows

Advertisements I have tried to output some pgsql commands – specifically grants – to an SQL file. My code looks like this: DO $$ DECLARE rec record; BEGIN FOR rec in SELECT pu.usename, pr.rolname, tp.privilege_type, tp.table_name FROM pg_user pu JOIN pg_auth_members ON pu.usesysid = pg_auth_members.member JOIN pg_roles pr ON pr.oid = pg_auth_members.roleid LEFT JOIN information_schema.table_privileges… Read More Output PGSQL commands from inside of an anonymous block to a SQL file

Advertisements How do I query array size of a JSONB object? DO $$ DECLARE p_obj JSONB; array_size INT; BEGIN p_obj = ‘[{"a":"foo"},{"b":"bar"},{"c":"baz"}]’::jsonb; — This prints out NOTICE: [{"a": "foo"}, {"b": "bar"}, {"c": "baz"}] array_size = json_array_length(p_obj); RAISE NOTICE ‘%’, p_obj; RAISE NOTICE ‘%’, array_size; END; $$ LANGUAGE PLPGSQL; This results in: ERROR: function json_array_length(jsonb) does… Read More PLPGSQL: Query JSONB Array size

Advertisements I have a trigger in a table in postgres, whenever a column called intended_remediation_date is updated. If after the update this column is not null and if another column called binary_status = inactive, then I want to update the value of my target column , which I call remediated to true, otherwise it should… Read More after update trigger not firing based on condition in postgres

Advertisements I work on a PostgreSQL database. I’d like to use a loop to create multiples views from a table column. Let’s say I have this table mylist : id type 1 Fish 2 Bird 3 Fish 4 Fish 5 Bird I use that query to get all the distinct values from the type column:… Read More How to create multiple views from a table column with a PostgreSQL trigger?

Advertisements I am writing an sql program that creates a table with columns computed in a function. The function returns the record type. This is what the header looks like: create or replace function get_items(col1 int, col2 int) returns record What I would like to be able to do is: create table table_items as (… Read More PostgreSQL: Obtaining multiple values from the record type

Advertisements I am writing an sql program that creates a table with columns computed in a function. The function returns the record type. This is what the header looks like: create or replace function get_items(col1 int, col2 int) returns record What I would like to be able to do is: create table table_items as (… Read More PostgreSQL: Obtaining multiple values from the record type

Advertisements This code below dispalys this error SQL Error [42601]: ERREUR: erreur de syntaxe à la fin de l’entrée Où : fonction PL/pgSQL users_tables(), ligne 26 à RETURN QUERY I do not understand why ! In fact i launched the query inside the loop and it works fine I do not see any logical error in… Read More plsql postgresql : SQL Error [42601]: RETURN QUERY