Questions tagged [postgresql]
PostgreSQL is an open-source, relational database management system (RDBMS) available for all major platforms, including Linux, UNIX, Windows and OS X. Mention your version of Postgres when asking questions. Consider dba.stackexchange.com for questions concerning the administration or advanced features.
postgresql
178,736 questions
0
votes
0
answers
6
views
Different sorting results between PolarDB-PG 14 and 16 with same collation settings
I recently upgraded my database from PolarDB-PG 14 to PolarDB-PG 16, and I'm experiencing inconsistent sorting behavior despite having identical collation and ctype settings (en_US.UTF-8 in both ...
- 1
Best practices
0
votes
0
replies
14
views
jOOQ: Is it ok to create DSLContext per HTTP request?
From a performance perspective, does it make sense to create a new dslContext per HTTP/API request? I need to set some request specific session variables (like user_id), for dslContext but I'm worried ...
- 2,361
-3
votes
0
answers
33
views
PostgreSQL pg_dump generates invalid migration scripts - DROP RULE creates forward references
During automated database deployment with pg_dump/pg_restore, I encounter
a dependency ordering issue that breaks CI/CD pipelines when using ON_ERROR_STOP=on.
Problem: pg_dump executes DROP RULE for ...
- 1,443
2
votes
1
answer
52
views
How to safely quote a fully qualified table name as a string literal for casting in psycopg2 / psycopg?
I'm looking to generate this sort of SQL dynamically and safely in psycopg2 / psycopg:
'"my_schema_name"."my_table_name"'::regclass
where ...
- 27.4k
-3
votes
1
answer
65
views
Spring Batch metadata tables are not automatically created in Spring Boot 4
I am trying to set up a Spring Batch project using Spring Boot 4.0.0
The application starts successfully, but the standard Spring Batch meta-data tables (e.g., ...
Best practices
0
votes
0
replies
36
views
Should long free-text notes be stored/searched in Apache AGE graph nodes or PostgreSQL tables?
I’m building an app that stores long, unstructured notes like:
“February 6th, 2026 – This is a note about Bob Maynard. He likes the color green…”
These notes can be fairly long and I need to:
Store ...
- 11
Tooling
0
votes
3
replies
50
views
Connect to PostgreSQL from Visual Studio Code?
I have started putting together a C# asp .Net Core REST web API using SQLite for a simple start. I like the way it goes, but want to try out postgresql, as it has JSON support.
However I am not able ...
- 907
Best practices
0
votes
3
replies
46
views
Replica DR drills
I have set up a postgres asyncronous streaming replica of our database server as part of our Disaster recovery solution, now it is read only and we must do drills as part of preparing for disasters
...
- 1
Advice
1
vote
2
replies
61
views
How to accelerate UPDATE on table with HNSW index in PolarDB-PG?
I’m using Alibaba Cloud PolarDB for PostgreSQL and using the pgvector extension to store embeddings. I noticed a performance regression during executing UPDATE after I add a HNSW index.
Performance
I ...
- 41
-3
votes
0
answers
36
views
Postgres Restore on Partition Table with Trigger [closed]
so I'm asking here about the behavior of pg_restore, we have a situation here when restoring the database using pg_restore is changed on partition table.
context :
the trigger was executed before ...
3
votes
0
answers
34
views
PolarDB-PG IMCI Query don't support TimeZone?
I am using IMCI of PolarDB for PostgreSQL.
My table creation statement is like this:
...
- 21
Advice
0
votes
3
replies
51
views
Statement execution after thread interruption
Will Statement execution be canceled if the thread that is executing it is interrupt()ed (...
- 2,847
Advice
1
vote
8
replies
79
views
Using advisory locks to coordinate database activity
I have several different jobs that can run throughout the day to read data from various sources and update my database.
I want to ensure that two jobs are not trying to update the database at once. ...
- 33.8k
6
votes
1
answer
115
views
How do I confirm that JdbcTemplate is fetching the records into the result set in batches of 1000 when setFetchSize(1000) is set on the statement?
How do I confirm that JdbcTemplate is fetching the records into the result set in batches of 1000 when setFetchSize(1000) is set on the statement?
I am using PostgreSQL JDBC driver here.
Below is my ...
- 496
-5
votes
0
answers
58
views
Go background job/channel truncates saved data after completion [closed]
I have two background jobs created in go (one with context, and the other without).
The context job runs every 8 seconds to parse log files and saves an email based on domain logic to the outbox ...
- 1
0
votes
3
answers
94
views
Prevent overlap in foreign keys [closed]
I have 3 tables that represent three states of a process. The first table stores information about an attempt. The next one stores information on failed attempts, the last one on successful attempts. ...
- 3,216
0
votes
0
answers
43
views
DROP RULE references non-existing objects [closed]
I have a PostgreSQL database with tables, views, custom types, and collations.
I export this DB with pg_dump:
...
- 1,443
Advice
2
votes
2
replies
58
views
PolarDB standby logs "consistent recovery state reached" but never becomes "ready to accept read-only connections"
I have a PolarDB PG14 physical standby created from a base backup and configured for streaming replication. WAL replay appears to be progressing, but the standby never becomes available for read-only ...
- 1
1
vote
1
answer
39
views
virtualxid lock during reindex operation
I am trying to do reindex concurrently on a table, but when I see pg_stat_activtity it shows in Lock state with ...
- 1,313
Advice
0
votes
0
replies
9
views
Apache Superset: How to order a Line Chart by Week/Year without ISO calendar shifts or alphabetical sorting issues?
I am struggling to correctly display a time series (Area Chart) in Apache Superset where the X-Axis represents weeks in the format WW/YYYY. I have two main issues ...
Best practices
0
votes
5
replies
71
views
Feedback on data model normalisation for school project on service to match users based their preferences, and how to model verification status?
I am looking for feedback on the normalisation of my physical data model which I designed for my school project for a service to match users based on their preferences.
The data model is supposed to ...
-1
votes
0
answers
39
views
Spilo HA cluster with mTLS: BOOTSTRAP_POSTGRES_DATABASES not creating databases and SSL certs not used [closed]
I’m setting up a Spilo (Postgres HA) cluster inside Docker with mTLS for both Postgres and Etcd. I’m facing two issues:
Databases specified in ...
- 647
-2
votes
0
answers
30
views
Temporal is not running in Docker [closed]
Below is my Docker Compose script. However, Temporal only showing "Waiting for PostgreSQL to startup" when I execute docker compose up.
...
0
votes
0
answers
37
views
PrismaClient throws "Invalid invocation" for findUnique/create
I am building a Node.js/Express backend using TypeScript, Prisma, and PostgreSQL (Supabase) and encountering a weird issue where this.prismaClient.user.findUnique ...
Advice
0
votes
2
replies
28
views
Parallelized Access to TypedTuplestore data structure in PostgreSQL
I am working on a C-UDF for Postgres, where i would like to somehow read from a passed Typed-Tuplestore in parallel. Either pthreads or worker-processes is fine, as long as the access isn't combined ...
- 673
0
votes
1
answer
60
views
Drizzle TypeError: Cannot read properties of undefined (reading 'query')
I'm trying to seed my new Database with drizzle-seed, but I get this error that didn't said to me much.
All that I get is a persitent.
...overriding system value ...
- 9,274
Best practices
0
votes
2
replies
39
views
Updates with temporal foreign keys
I'm playing around with Postgres 18 temporal keys. At the moment, I have this schema:
...
- 307
3
votes
0
answers
154
views
0
votes
1
answer
65
views
How to implement SQLAlchemy 2.0 transactions in FastAPI routes while maintaining test isolation with Savepoints?
I'm using FastAPI with SQLAlchemy 2.0 (Async). I want to follow the best practice of wrapping my route logic in transactions. However, I'm struggling to make this work with my Pytest suite where I use ...
2
votes
3
answers
111
views
Split a record and insert its parts into tables linked with FK, in a single query
I've got this situation in which I need to copy and split account information:
...
- 3,216
-2
votes
0
answers
59
views
Echo , Golang , Postgresql , Docker Different Results [closed]
I just start learning Golang and I'm currently using Echo.
I have an endpoint :
- base/api/wallet/cardnumber -> get card details
and I execute "go run .\main.go" and the API provide ...
- 49
Advice
0
votes
1
replies
19
views
How to resolve Npgsql Exception: "bad protocol version" during SSL handshake on macOS with .NET 6 and Let's Encrypt?
I am trying to connect to a PostgreSQL 17.5 database from my Mac using .NET 6. The connection works perfectly from Linux pods in Kubernetes, but on my Mac, I get the following error:
Npgsql....
- 2,673
Advice
0
votes
1
replies
20
views
How to test Prisma schema changes/migrations without breaking the shared development database?
I’m part of a dev team using a shared PostgreSQL instance for our development environment. Recently, I had to remove a field from the DB for a specific feature. I ran the migration, but the task ended ...
Advice
0
votes
1
replies
30
views
How to model and track monthly snapshot data in PostgreSQL when no natural key exists?
Problem
I’m loading a monthly snapshot dataset into PostgreSQL where the source does not provide a stable natural key for one of the entities.
The dataset is similar to the Brazilian CNPJ sócios (...
- 15
0
votes
0
answers
56
views
How do I dump a vector table to a destination database with pgdump and psql?
I am trying to migrate between DB instances using the pg_dump util, but running into some odd behavior where it tries to create the types and operators belonging to ...
- 1,076
Best practices
0
votes
1
replies
19
views
Synchronizing SAP MaxDB tables to PostgreSQL (near real-time or scheduled) without replacing MaxDB
I’m working on a BI architecture where SAP ECC is running on MaxDB (on-prem).
The goal is not to migrate or replace MaxDB, but to replicate/synchronize selected tables into PostgreSQL for analytics ...
Advice
1
vote
9
replies
72
views
Does SQL window function ORDER BY inherit from parent query ORDER BY?
In SQL, if I omit the ORDER BY clause from a window function's OVER() clause, will it use the parent statement's ...
- 5,080
1
vote
0
answers
90
views
Is it possible to add additional conditions to SQL queries using EclipseLink's built-in tools?
Our project uses the PostgreSQL database, managed via EclipseLink.
Horizontal scaling is planned by migrating to the sharded Postgres Pro Shardman.
Currently, the primary key of each table is a unique ...
- 85
0
votes
1
answer
56
views
Different between tsvector and tsquery [closed]
I just find out tsvector and tsquery in PostgreSQL but even I already read the description in the documentation I still not ...
- 31
0
votes
0
answers
51
views
Cascading PostgreSQL function calls - Pass by Reference vs Value [duplicate]
I understand that passing values to SQL & plPGSQL functions involves passing by value instead of reference.
Just wanted to clarify that if I pass jsonb or huge multiple arrays to a function (say ...
- 286
Best practices
0
votes
4
replies
43
views
Postgres: Best design pattern for "Exclusive Arc" (Polymorphic) relationships? (Nullable FKs vs. alternatives)
I am designing a database schema in PostgreSQL for a notification system. I have several distinct "parent" entities that can trigger a notification log:
...
Advice
2
votes
3
replies
61
views
Ensuring adjacent ranges in PostgreSQL
I am creating a database with temporal validity using date ranges in PostgreSQL 18.1. I want to place constraints which force the date ranges to be non-overlapping and adjacent (= there are no gaps).
...
- 307
-1
votes
1
answer
78
views
PostgreSQL syntax error near placeholders in query string literal on GO [duplicate]
I'm trying to execute a prepared statement with two placeholders.
The query is the following:
CREATE SCHEMA
IF NOT EXISTS $1
AUTHORIZATION $2;
However, the ...
Best practices
1
vote
7
replies
102
views
Why is PostgreSQL COPY significantly slower than SQL Server BULK INSERT?
I’m trying to understand why PostgreSQL COPY is much slower than SQL Server BULK INSERT in my scenario (or every scenario?).
I ...
3
votes
0
answers
103
views
Python - Client getting stuck when sending async generator to server over httpx
I've got this client script:
...
- 51
1
vote
2
answers
76
views
How to call a PostgreSQL bulk upsert function in TypeScript/BunJS
I'm working on a database to store information from .warc files, which are being parsed by a program I wrote in BunJS. The problem is that inserting data into the database takes a long time to insert ...
- 105
Tooling
0
votes
10
replies
56
views
Programming Language Recommendation for Saas
What is the Lowest deployment cost programming language for Saas (For Business Software POS, ERP, Inventory Management) Web Applications. With Data Handlings and Reports generations. Database is ...
Advice
0
votes
2
replies
33
views
Is there a way to automatically back up a PostgreSQL log table when it is updated or deleted?
I have a log table in PostgreSQL (people_log) that tracks changes in my main tables.
The problem is, the log table can grow very large, so I cannot keep all the ...
- 53
4
votes
1
answer
98
views
Blob management for Postgres database with Spring Boot, JPA and hibernate
We want to migrate an Oracle database with BLOB type for some columns to a Postgres database and to have our Spring Boot application working with it (we use JPA and hibernate).
I saw that BLOB from ...
- 401
2
votes
1
answer
91
views
Using PostgreSQL with Symfony 5
I am starting to learn Symfony 7 and how to use PostgreSQL.
I have succeeded in creating a database with PostgreSQL.
By adding that in .env:
...
