I have the table with 10TB of data. I want to perform the COUNT(1)/COUNT(*) to check the EXACT COUNT between main table and archive table. I tried following SQL's but didn't get optimal solution, queries keep running for more than…
Given min and max dates are: Min date: 2023-06-19 09:54:23.000 Max date: now()::timestamp(0)-interval '1 year' Expected output: start_date end_date ------------------------------------------- 2023-12-01 00:00:00.000 2023-12-09 23:59:59.999 2023-11-01 00:00:00.000 2023-11-30 23:59:59.999 2023-10-01 00:00:00.000 2023-10-31 23:59:59.999 2023-09-01 00:00:00.000 2023-09-30 23:59:59.999 2023-08-01 00:00:00.000 2023-08-31 23:59:59.999…
I'm attempting to call a database function that takes a single UUID as a parameter. When I do this: report_id = "5ed30f08-0de0-47f4-99e8-9aeeb8eb2dfe"; query = "select * from public.fn_func($1)"; report_nav = pq_exec_params (conn, query, {report_id}); I get this: __pq_exec_params__: fatal error:…
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…