Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,496 questions
3
votes
1
answer
105
views
MERGE causes unique constraint violation when source contains key multiple times
Given two tables counter and cnt_source defined like this:
create temporary table counter (key bigint, count bigint);
create unique index counter_key_uniq on counter (key);
create temporary table ...
- 31
0
votes
1
answer
51
views
Why does READ COMMITTED allow Phantom Reads in PostgreSQL?
When writing SQL queries against a large PostgreSQL database, I am seeing phantom reads despite setting the transaction isolation level to READ COMMITTED. Can you provide a concrete example of a multi-...
- 13
1
vote
1
answer
32
views
Granting Monitoring privileges to a user in PostgreSQL
I am creating a new user and granting them the pg_read_all_stats privilege so they can monitor all pg_stat_* views. However, when I monitor pg_stat_activity through this user, other users' operations ...
0
votes
0
answers
34
views
PostgreSQL logical replication failing with "permission denied for schema" despite operating as the superuser
On a PostgreSQL 14 database, I've got a logical replication publication of 108 tables across 10 schemas. For some unknown reason, the subscriber fails to replicate tables in one of the schemas (call ...
- 175
0
votes
1
answer
30
views
PostgreSQL:Why promoting standby while primary is online causing WAL fork?
I have been testing PostgreSQL 17 AYSNC replication and failover on Ubuntu VMs. Suppose I have two VMs: A as the primary and B as the standby. If I promote B while A is offline, and later configure A ...
- 700
0
votes
0
answers
49
views
How to use the same subquery in both queries for UNION
I have an SQL query with a subquery, like this:
SELECT field1,
json_agg(json_object('key', field2, 'total', "total") order by field2) as "my data"
FROM (
SELECT field1,
...
- 101
0
votes
1
answer
32
views
Enabled always statement level trigger on logical replication target does not seem to fire
Overview
I have the following setup using Azure PostgreSQL flexible server v17:
a main database containing a certain table included in the table list for a logical replication publication
a replica ...
- 1,193
0
votes
0
answers
48
views
PostgreSQL pg_dump fails with "server closed the connection unexpectedly" during COPY operation
Problem Summary
My automated PostgreSQL backup script using pg_dump was working fine until a few days ago. Now it consistently fails with a connection error during the COPY operation on the alternate ...
- 101
0
votes
1
answer
23
views
Postgres database migration. pg_restore seems to hang
I'm attempting to migrate (users, structures, triggers, data - everything) a postgres 17 database on a windows 10 machine to a new windows 11 server.
The new machine has a default installation of ...
- 1,313
0
votes
1
answer
50
views
Logical replication betweet different schemas
I just discovered that logical replication doesn't work between different schemas, like you cannot publish schema1.table1 in server1 to schema2.table1 in server2.
In my setup, I have multiple servers ...
0
votes
1
answer
83
views
Efficiently sorting by values in a separate table
I have a PostgreSQL 17 database that unfortunately requires queries that are very dynamic and hard to optimize. The part that I want to ask about are queries that need to order the results by some ...
- 117
2
votes
1
answer
86
views
Why does Postgres not allow SERIALIZABLE transactions on read-only replicas?
I get the following error when trying to send queries to a Postgres server in hot standby:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
COMMIT;
And the error:
SQL Error [...
Carl Mastrangelo
0
votes
2
answers
70
views
postgresql 17, xml xpath queries not work
We need to process XML data, and struggle with this. I followed some XML with PostgreSQL tutorials, and all was fine. Then I wanted to import XML files, around 30-40kB, exported from a diagram Editor. ...
0
votes
0
answers
17
views
postgres and size of table [duplicate]
I want to detect the size of a table in postgres. 2 approaches:
size of datafile on fisk
pg_table_size()
according the below I get hugely different results (77M vs 279GB) and am guessing ...
- 293
3
votes
1
answer
123
views
Postgres Temp table performance
We are converting a database from SQL Server 2022 to Postgres 18.
Our system has a lot of code in the database and a lot of queries that use local temp tables a lot more than CTE's. This is due to the ...
- 31
2
votes
1
answer
62
views
JSONB subscripting initializes numeric keys as arrays instead of objects
I'm using PostgreSQL 16+ and working extensively with jsonb columns using JSON subscripting paths (as described here: PostgreSQL docs – jsonb subscripting).
I've run into an issue when updating nested ...
- 21
0
votes
0
answers
45
views
Fast query got "cancelling statement" on replica
I'll say in advance that I've seen similar questions on this topic, but I just couldn't find an answer because they concerned long queries, while mine, on the contrary, executes the query in less than ...
- 101
0
votes
0
answers
53
views
PostgreSQL logical replication: do I need to truncate a replicated table when recreating a subscription?
I’m testing logical replication in Postgresql 15 to replicate tables across several servers. Some of the subscribers may be offline for months, which eventually invalidates their replication slot on ...
0
votes
1
answer
87
views
How to clean the pg_wal folder when it is not done automatically?
I have two PostgreSQL16 clusters (one cluster per server) each with one and the same database.
They are interconnected via a bi-directional logical replication, with one being the main one and the ...
- 101
0
votes
1
answer
63
views
How to I apply default privileges in Postgresql when changing ownership?
The problem (short version)
I have to import data from files to PostgreSQL, and this has to be done on the server side. Files are uploaded to the PostgreSQL server and then imported to the database by ...
- 395
2
votes
1
answer
225
views
UNNEST in SELECT
PostgreSQL do the join with an UNNEST result table as a INNER JOIN.
Where is this described in the documentation? Why not a LEFT JOIN?
Example:
CREATE TABLE products (
id INT,
tags TEXT[]
);
...
- 123
0
votes
1
answer
48
views
Postgresql giving different result when comparing timestamp with date in different time zone
When running the following query, I would expect it to give the same result independently of the session time zone but instead it gives different result depending on the session:
select '2025-09-22' &...
- 15
0
votes
1
answer
35
views
WAL record action code dictionary
I'm exploring the module wal2json for the first time. I've set up the replication slot using the ReadMe examples.
When peeking the replication slot, every data value begins with an "action" ...
- 5,951
2
votes
1
answer
101
views
Design of a table that references several columns in two other tables
Is it possible to create a table that is built from an inner join of two other tables with additional columns, but where the rows are kept updated when the inner join is re-run?
Currently using ...
0
votes
1
answer
68
views
postgres query optimization for batch fetches with 100M+ rows
Table Definition
CREATE TABLE public.table_1 (
id bigserial NOT NULL,
generation_date date NOT NULL,
"namespace" varchar(16) NOT NULL,
item_id int8 NOT NULL,
...
- 101