Postgresql – Update with limit and offset
Simplified scenario: I want to update one table A (10 million rows) with a value from another table B (20 mil rows). The two tables are linked with ids. It took more than 7hrs to update the whole thing in…
Ubuntu – Improving Performance of Large Table Inserts in PostgreSQL
I am inserting data from the tmp_details table into the details table using an INSERT query in chunks. I am also modifying the data while inserting it. The query is taking a lot of time to execute. My read IOPS…
Postgresql – Supabase/Postgres – move nested jsonb attribute values into distinct fields – not working
I'm trying to run a Postgres SQL call to migrate data from a jsonb field to separate fields for each attribute, and for some reason the process is not working for the nested fields { "basis": "relative", "defined": "2023-06-21T10:21:16.979", "absolute":…
Postgresql – How to insert sort direction into SQL query dynamically
I have this at the end of my SQL query: ORDER BY CASE WHEN :sortField = 'creationDate' THEN update_.creation_date ELSE update_.default_time_field END, CASE WHEN :sortDirection = 'DESC' THEN DESC END; However, this gives me a compile error (the 'DESC' refers…
Postgresql – How to fetch from cursor over single column of composite type into a variable of that type in PL/pgSQL?
Given: CREATE TYPE my_struct AS ( a int4, b text, c float4 ); DO $$ DECLARE my_cursor refcursor; my_value my_struct; BEGIN OPEN my_cursor FOR SELECT (1, 'a', 10.0)::my_struct; FETCH my_cursor INTO my_value; -- ERROR! END $$ Postgres raises an error…
Postgresql – How to design a table which is referenced by two other tables in postgres
I have a table named urls: id name 1 url1 2 url2 This urls table is referenced by two other tables documents and doctypes. documents: id reference 1 kl/dkj/192 2 kl/dji/982 doctypes: id name 1 document1 2 document2 documents and…
Postgresql – How does PotgreSQL round double precision numbers?
I understand how double precision works (exponent, mantissa, precision depending of the value, not exactly accurate numbers...) and I'm currently writing a blog post on this subject. But when using it in PostgreSQL I found that PostgreSQL round these values.…
Postgresql ERROR: cannot accumulate arrays of different dimensionality
I have a postgres query I'm trying to aggregate an array of arrays with different dimensions, a simplified form of the query can be written as: SELECT n, ARRAY(SELECT UNNEST(a) FROM (SELECT ARRAY_AGG(a) AS a) a) AS a, SUM(q) AS…
PostgreSQL comparisons
I am confused why here the result is TRUE select 'Clark' in ('Dave', null, 'Clark'); But here it's NULL (instead of evaluating to TRUE) select 'Ava' not in ('Dave', null, 'Clark'); Can somebody please explain?
Postgresql – I get this error – ImportError: DLL load failed while importing _psycopg: The specified module could not be found. what can I do about it?
I'm working on a Flask project with SQLAlchemy and PostgreSQL. When I try to run the Flask app, I encounter the following error: Error: While importing 'app', an ImportError was raised: Traceback (most recent call last): File "pathtoprojectapp.py", line 8,…