Postgresql – Select one row, and if not exist, a different one
Here is the situation. Imagine a table with a few columns: ID Name Language ... 1 Bob ES ... 2 Bob EN ... 3 Bob DE ... 4 Bill EN ... 5 Jack EN ... 6 Jack DE ... 7…
Postgresql – Inserting many rows in psycopg3
I used to use execute_values in psycopg2 but it's gone in psycopg3. I tried following the advice in this answer or this github post, but it just doesn't seem to work for my use case. I'm trying to insert multiple…
Postgresql – Postgres removing jsonb object from nested jsonb array
I have a deeply nested json array. The array contains a list of json objects, I want to remove any of the objects that are matched based on a field within the object. { "name": "John smith", "items": { "unknown-key-1":…
Postgresql getting wrong estimated rows when executing count(*) partitioned table with index only scan
I have a Azure Flexible Postgresql large partitioned table version 13 and I need to run count(*) from that table and compare with oracle performance. However I have some concerns after checking explain plan as below Explain plan already used…
Postgresql – Psycopg3 – no pq wrapper available
I am facing this error when I try to run psycopg3 on AWS Lambda. [ERROR] Runtime.ImportModuleError: Unable to import module 'function': no pq wrapper available. Attempts made: - couldn't import psycopg 'c' implementation: No module named 'psycopg_c' - couldn't import…
Docker for Postgresql database with data import
I am trying to create a Docker for a PostgreSQL database and to import some data into it. I have followed the official documentation. The Dockerfile looks like this: FROM postgres:14.10 #Set env vars ENV PGDATA=/var/pgdata ENV POSTGRES_USER=postgres ENV POSTGRES_PASSWORD=admin…
Postgresql – Can I count on a simple case/switch plpgsql function to use a sane query plan?
I am trying to implement a Postgres function with optional parameters. I tried a stable PL/pgSQL function with required parameters, splitting cases along user input in an IF construct. Here is a simplified example: Setup: CREATE TABLE IF NOT EXISTS…
Postgresql – Order by case statement that extracts substring from column
I have the below SQL that takes only data from the Name column up to the point where a space or hyphen appears. I would like to order the results by the output of the case statement but can't find…
C# 11 raw string literals with ExecuteSqlRawAsync on PostgreSQL have case sensitive problem
I encountered an issue while attempting to modify my SQL query to address warnings about potential SQL injection. I aimed to parameterize the query and use raw string literals, but I received an error message instead. It seems the error…
How find one JSONB item from JSONB array in PostgreSQL
I have this table: id period_type period commission 123456 Daily 10 10 123456 Monthly 3 11 123456 Daily 5 12 123456 Monthly 1 13 123457 Monthly 6 14 123457 Daily 5 15 123457 Monthly 9 16 (Period type is int…