I am trying to parse XML using PostgreSQL. XML contains a null-value. The expected type of column is "int". But Postgres treats the value as an empty (not null) string. with data as ( select '<root> <test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <int_value…
when adding a unique index and then a PK to a partitioned table that already has that PK on a table partition, the PK creation fails with multiple primary keys for table X are not allowed Why this limitation and…
I'm using PostgreSQL 14, and during the replication setup, I forgot to include single quotes around the value of primary_conninfo. I'm puzzled as to why the server isn't starting, and I'm not finding any logs indicating an error in the…
In PostgreSQL (I use 14 and 16) I am trying to create a function that inserts a record into a table that has two columns: an identifier (integer) and a string "Some string". I want to write a loop to…
How can you get a list of values in a loop so that you can use it in the IN operator? It turns out that I need to declare the desired type, assign a list of values to the variable…
I have to create a query to show group_id and the list with subjects and number of people from this group per subject. I have table 'group' with 'entity_type', that can define person's subject and with 'group_type' that defines in…
PostgreSQL version: PostgreSQL 14.8 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit I'm trying to create a stored procedure which accepts two arguments (a and b is for the sake of simplicity, their actual names are _service_id and…
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" :…