Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
2 votes
1 answer
82 views

When to rerun CLUSTER?

Currently using Postgres 14. Running CLUSTER sometable USING someindex; is great, but is there a query to determine how "unclustered" the table has become after many updates and deletes? ...
RonJohn's user avatar
  • 647
0 votes
0 answers
93 views

Patroni failed to get list of machines from etcd

I am running a patroni cluster (3.4) on linux with an etcd cluster. Normally the cluster runs perfectly fine but sometimes I get some errors saying Request to etcd server failed (ReadtmeoutError, ...
mymarcelsql's user avatar
-1 votes
0 answers
48 views

Migrating Planetscale MySQL to Free PostgreSQL Host [closed]

I'm working on migrating a project's database from Planetscale MySQL to Supabase PostgreSQL. I've explored Supabase's migration tools but found the process a bit complex. I have backups of my ...
user avatar
0 votes
0 answers
12 views

Basic setup with Cloud SQL and IAM authentication

Let's say I have a database instance, two databases db1 and db2, and two users user1 and user2@PROJECT_ID.iam (BUILTIN and CLOUD_IAM_SERVICE_ACCOUNT). In other words: resource "...
x-yuri's user avatar
  • 367
0 votes
1 answer
42 views

What is the difference between CURRENT_TIMESTAMP::DATE and CURRENT_DATE in PostgreSQL? [closed]

I created an immutable function as below: CREATE OR REPLACE FUNCTIONGetCurrentDate() RETURNS DATE AS $p$ BEGIN RETURN CURRENT_TIMESTAMP::DATE; END; $p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER; ...
Jitendra Loyal's user avatar
0 votes
1 answer
36 views

In a trigger check if one of 24 columns are updated (postgres)

I have a table with 50 columns. I need to make a trigger to do something, i can do that, but i need to do whene 1 of 24 columns are updated. The problem is i have to do 24 times if NEW.column <> ...
RkassaTeam Breda's user avatar
1 vote
2 answers
45 views

Insert missing rows after counting by group

This is similar to Postgresql group by and count() problem, missing row with 0 result but different in genesis and details. I have a table of temperatures in Celsius and dates and I use ...
Charlie Clark's user avatar
3 votes
1 answer
24 views

Optimizing retrieving up to X rows in secondary table per parent row in a primary table

I'm looking to find the best general approach to retrieving up to a given number of records in a secondary table for each record previously retrieved in a primary table. Technical Situation For the ...
Corentin Demachy's user avatar
1 vote
1 answer
44 views

Postgres: Analyze problem pg_log_backend_memory_contexts

(PG14.1) Below is the output of pg_log_backend_memory_contexts for a PID for which pg_proctab() says that rss is 216MB. I added a few dots to make reading large numbers easier and I anonymized a few ...
Peter's user avatar
  • 158
0 votes
1 answer
30 views

Does long running query cause replication delay in PostgreSQL which is in 'idle in transaction' state?

In my system I found same query called more than 30 times 'idle in transaction' state. Is there any way to to identify the reason of 'idle in transaction'? Should I terminate those query manually ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
2 answers
50 views

High number of loops in within a nested loop Index Scan

Query SELECT organizations.lf_id, organizations.eb_id FROM organizations JOIN organization_lists_organizations ON organizations.id = organization_lists_organizations.organization_id WHERE ...
andrepcg's user avatar
  • 101
0 votes
0 answers
29 views

How can I transfer specific filtered data from one database(server1) to another database(server 2) in postgresql?

For Example, I have a PostgreSQL database with multiple schemas and each table under the schemas has one common column like city. I want to transfer the database entry for city like Pune from one ...
akshaycredmango's user avatar
1 vote
1 answer
43 views

Limiting the number of rows that can be inserted for a given WHERE clause

In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions? I have a table event_attendance with ...
Luke Hutchison's user avatar
0 votes
0 answers
16 views

Postgres SELECT in form suitable for copy-paste to INSERT in psql

In psql, I'd like to SELECT a single record in a format that's suitable for copy-pasting as an INSERT in another unrelated/disconnected database with the same schema. For example, I want to do this: ...
Jim Stewart's user avatar
0 votes
1 answer
89 views

pg_upgrade fails on pg_restore: error: connection to server at "localhost" port 5433 failed: FATAL: password authentication failed for user "postgres"

I have a PostgreSQL 13 installation on a Windows 10 server (on port 5432) that I want to migrate to a new PG 16.2 installation. To this end, I downloaded the Windows x86-64 installer for PostgreSQL 16....
s.k's user avatar
  • 372
0 votes
1 answer
35 views

With postgres table partitioning by list (of varchar), will the varchars be stored physically in each row?

When having a table that gets partitioned by list, using a varchar field (or text field) that contains long strings, will those strings be physically stored in each partition and hence increasing the ...
valenterry's user avatar
0 votes
0 answers
32 views

Why my postgresql perfomance getting worse after changing default parametes

I changed my postgresql server parameters to tune it for a query but I get worse result than default settings.Here is my changed parameters and explain result: # pgTune suggested settings ...
mahdi's user avatar
  • 1
-1 votes
1 answer
35 views

Query postgres db in a Docker container from another container using peer auth

I have two Docker containers with a Postgres db in each. I am exposing the Postgres socket of each container to the other one, using Docker volumes, like so: docker run --rm -itd --name=containerone -...
Borislav Zlatanov's user avatar
0 votes
0 answers
25 views

Use COPY in PostgreSQL to insert huge amount of data to one table and populate another table at the same time using IDs

I have the following 2 tables in my database: CREATE TABLE IF NOT EXISTS segments ( id SERIAL, segment TEXT NOT NULL, lang_id INTEGER NOT NULL REFERENCES languages(id), ...
Panos's user avatar
  • 1
0 votes
1 answer
23 views

How to get Google Cloud SQL Auth Proxy working on Windows (WSL)?

I am trying to run Google's Cloud SQL Auth Proxy on my Windows 11 machine under WSL (Windows Subsystem for Linux). I downloaded the Cloud SQL Auth Proxy 64-bit executable and run it and get these ...
rlandster's user avatar
  • 375
0 votes
0 answers
35 views

invalidating slot "a" because its restart_lsn 6093/6BB6F1B0 exceeds max_slot_wal_keep_size

we use google cloud sql (version is PostgreSQL 15.5), the config as follows: config, and max_slot_wal_keep_size = -1, but slot always become lost wal status (restart LSN become null) when running for ...
user avatar
1 vote
1 answer
42 views

What does psql do on connect (that could be taking 1 to 10 minutes)?

In some cases psql is taking ~1 to 10 minutes to connect to PostgreSQL, with no queries that it seems to be running while doing so (according to pg_activity that I think queries pg_stat_activity) I'm ...
Michal Charemza's user avatar
0 votes
3 answers
79 views

What index can be created to optimise this query?

I've got the below SQL query that runs extremely slowly. As for this query, this is due to the "ORDER BY" statement, since Postgres is scanning the changes table by "counter" which ...
laurent's user avatar
  • 191
0 votes
1 answer
162 views

Postgresql: could not determine polymorphic type because input has type unknown

The following query works: select t.oid, t.typname, case t.typcategory when 'C' then json_object('{}') when 'E' then (SELECT array_to_json(array_agg(enumlabel::text)) FROM pg_enum WHERE ...
ceving's user avatar
  • 317
-1 votes
2 answers
39 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
Björn Morén's user avatar
0 votes
0 answers
17 views

Inheritance in Postgresql and column key references

What happens to downstream rows depending on a PK (via col id) when that PK row changes ID, but not it's value? Think a row deleted and recreated, etc.) There is a post here that I was reading for ...
Yumi Koizumi's user avatar
2 votes
1 answer
131 views

How to query for aggregated streaks?

My goal is to detect price trends for cryptocurrencies. For that I'm getting OHLC pricing data for multiple currencies vs. multiple quote symbols (e.g. candles for Bitcoin/USD, Bitcoin/EUR, Litecoin/...
Sascha Mayr's user avatar
2 votes
2 answers
35 views

Does a serializable transaction around a single statement have any effect compared to an implicit transaction?

I'm doing some performance auditing of our codebase and have noticed that we always run SQL statements within a serializable transaction. I've also noticed that many of these transactions only perform ...
ocharles's user avatar
  • 208
0 votes
0 answers
11 views

pgBackRest - optimal policy / setup for RPO of 1 hour

I need a bit of advice. I have a postgres 14 database and a requirement for the DB, to be to recovered with RPO of 1 hour (acceptable data loss is max of 1 hour). Database size is 300 GB, backup size ...
Wojtas.Zet's user avatar
0 votes
1 answer
59 views

Extremely slow query whenever using ORDER BY, even when an index is present

I've been trying to debug a particularly slow query that never completes (it takes forever and eventually timeout), and found out that it's down to the ORDER BY statement: if it's there, it never ...
laurent's user avatar
  • 191
1 vote
1 answer
28 views

Is it possible to change the sequence backing an identity column?

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and ...
John Denniston's user avatar
0 votes
1 answer
37 views

postgresql query IllegalArgumentException in Yellowbrick DB [closed]

I'm writing Pl/pgSQL joining query in Yellowbrick DB. I have join 6 tables at a time. It was working fine. Suddenly I observe my query is throwing error: "IllegalArgumentException" Checked ...
Art Bindu's user avatar
  • 101
2 votes
2 answers
233 views

How to make a Data Modifying CTE abort if an update only partially succeeded?

I am wondering if it is possible to add some kind of check to make a data modifying CTE abort if an update is partially successful. Attached is a minimal query that represents the problem. I would ...
cynodon's user avatar
  • 23
0 votes
1 answer
16 views

granting privileges to user in postgresql

I'm trying to setup a new project with a Postgresql 15.5 database. I have a running instance. If I create a new database psql --command "create database \"tagger-db\"" create a ...
marcosh's user avatar
  • 243
2 votes
1 answer
103 views

How to catch a particular exception?

It is possible to catch an exception in plpgsql: EXCEPTION WHEN unique_violation THEN But how to check for a violation of a particular constraint? The following example creates two keys: one for a ...
ceving's user avatar
  • 317
0 votes
0 answers
28 views

Crunchy Postgresql Backup configuration

I am trying to set up a backup for minio S3 but failing on the job I would say that it probably a certificate issue I am using Gardner cloud and I have S3 storage provided from the cloud provider via ...
boncalo mihai's user avatar
0 votes
1 answer
30 views

DBMS for multiple DB Enviroment Management

I have moved into a DBA role where I now manage multiple types of DBs. I used to manage only MS SQL and MySQL, but now I am managing MS SQL, Azure SQL, MySQL, MariaDB and PostgreSQL. I am currently ...
Elene Roos's user avatar
0 votes
0 answers
29 views

Need an accurate way of comparing FLOAT datatypes from two different database architectures

I am doing a data load from MS SQL Server into Aurora PostgreSQL. Many of our tables use the float data type (whether or not this was a wise decision is outside the scope of this post, since the ...
Swechsler's user avatar
  • 143
-1 votes
1 answer
87 views

PostgreSQL: How is the uuid datatype sorted?

I have a system which uses the proposed UUID7 format for its uuid values. UUID7 has timestamp encoded into into its value. Does PostgreSQL document how ORDER BY clauses work with the UUID data type? ...
Justin Lowen's user avatar
0 votes
1 answer
16 views

Postgres server migration and version upgrade

We are migrating a 2TB server with a very large amount of BLOBs from an on-premises server to a cloud server. During the migration, we'd also like to update the Postgres version from 14 to 16. Now due ...
Frange0's user avatar
2 votes
2 answers
101 views

How to add an interval to a tsrange?

I tried this: select tsrange(now()::timestamp, now()::timestamp + make_interval(days => 3)) + make_interval(mins => 5); But I get the error that tsrange + interval does not exist.
ceving's user avatar
  • 317
0 votes
1 answer
16 views

Why does make_interval exist?

Is there a performance difference between these expressions? select CURRENT_TIMESTAMP + INTERVAL '5 minutes'; select CURRENT_TIMESTAMP + make_interval(mins => 5); I am wondering why make_interval ...
ceving's user avatar
  • 317
2 votes
0 answers
17 views

Postgres uses wrong index when using collation

I have a table with 1000 records. I created two indexes with different collation. create INDEX test_en on webshop.address (tenant_id, city collate "en-x-icu"); create INDEX test_de on ...
Florat's user avatar
  • 21
0 votes
1 answer
34 views

Why does Postgres give different EXPLAIN ANALYZE results when using now() vs current_timestamp?

At my job we've recently had to optimize a query and kind of stumbled into the answer to make the query faster but can't explain why it worked and it's bothering me. The situation is something like ...
Becuzz's user avatar
  • 213
1 vote
0 answers
33 views

Inaccurate estimates for geospatial types

This is my first dabble with Postgres and geospatial. Looking at a slow query which includes ST_Intersects(a.geography, b.geography). The optimiser estimates are 2500x out. Before I turn to ...
Mark Storey-Smith's user avatar
0 votes
1 answer
61 views

Postgres: WALWrite Waits and synchronous_commit

It seems clear that setting synchronous_commit OFF on sessions - on which the super rare drawback (allegedly-committed transactions being lost in case of a server crash) - is fine, increases the ...
Peter's user avatar
  • 158
1 vote
1 answer
17 views

Postgres - trigger function not working with generated columns as input

Before I post the DDL for my table, trigger function, and trigger, I'll just ask a simple question - Can a trigger function work with generated columns assigned to variables in an AFTER INSERT event? ...
Nicholas Lombardi's user avatar
1 vote
0 answers
24 views

Slow inserts into Azure PostgresSQL database

I have a simple table on what is basically a completely fresh database: create table import_test_uuid ( id integer not null generated by default as identity primary key, guid uuid ); I am ...
mohamedmoussa's user avatar
0 votes
1 answer
37 views

PostgreSQL - Selecting from different schemas in the same database

Will creating a view Table in schema 2 that pulls data from Table on schema 1 , affect the database or cause any other issues? my code: CREATE VIEW "test"."Excavation" AS SELECT * ...
wasfy mohammed's user avatar
0 votes
3 answers
62 views

How to avoid database deadlocks?

Let's say we have two tables user and book. On application level to ensure data integrity and avoid race conditions we lock entity at the beginning of transaction. Question 1: Let's say on application ...
ZiiMakc's user avatar
  • 135