I have a table master_data_approval_table in my postgres DB having field approval_value (type Jsonb) which contains below structure: We want only those records where each object of jsonb array strictly matches the given condition "status": "Approved" Expected O/P: I tried…
I have installed postgreSQL 14 on windows 10 and after opening pgAdmin this inscription "Loading pdAdmin4..." lasts forever I tried to change version of postgres to 15, but it didn't help I tried to add "Content Type=text/javascript" and "ContentType=text/javascript" to…
I have data in the following form select t.name_field, t.value_field from ( values ('name_1', 'val_fld_1'), ('name_2', null), ('name_3', 'val_fld__3') ) as t(name_field,value_field); From this data, I need to get a json string of the following form: {"name_1" : [{"value" :…
CREATE TABLE IF NOT EXISTS schema1.table1 ( ID bigint NOT NULL, eid bigint NOT NULL, sID bigint, oname character varying COLLATE pg_catalog."default" NOT NULL, obtype integer NOT NULL, parentid bigint, pageorder integer, obsubtype integer NOT NULL DEFAULT 0, pstate integer…
I'm learning PostgreSQL and I'm trying to understand the details of how to choose how to scan this database. I have postgtresql 14.2 and run the following code: DROP TABLE IF EXISTS t_test; CREATE TABLE t_test (id serial, name text);…
I am adding some columns to a table, and want a generated column that combines them together that I'll use for a unique index. When I try to add the column, I get the error ERROR: generation expression is not…
I have this basic PostgreSQL query: SELECT AGE('2021-01-21', '1942-11-20'); Which returns an interval in days: output: 28531 days, 0:00:00 I am using PostgreSQL version 14, according to the docs, AGE() should return a symbolic result in years, months and days…
I am restoring a postgres database via .backup file from one postgres-14 instance to another: pg_restore -h localhost -p 5432 -U postgres -d mydatabase -v "mybackupfile.backup" The backup fails, complaining about pg_restore: error: could not execute query: ERROR: function public.uuid_generate_v1()…
Trying to format the number as per the given format and culture. Given: -4059587.225000, --Value '#,##0.00;(#,##0.00)' --Format 'en-US' --Culture Will have many patterns, the given one is for example. Expected output: (4,059,587.23) In SQL Server we have format() function, what's…
I've recently switched from V11 to V14 of PostgreSQL, and some of my CTE queries, that used to take a few milliseconds, now takes a few seconds. The minimum query to reproduce this issue is the following : WITH MyTable…