Skip to main content

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
0 votes
0 answers
26 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
47 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
41 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
43 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
80 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
242 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
  • 339
-1 votes
2 answers
45 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
18 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
38 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
12 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
39 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
39 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
235 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
17 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
106 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
  • 339
0 votes
0 answers
38 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
31 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
213 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
17 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
120 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
  • 339
0 votes
1 answer
19 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
  • 339
2 votes
0 answers
21 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
36 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
1 answer
46 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. https://explain.dalibo.com/...
Mark Storey-Smith's user avatar
0 votes
1 answer
77 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
19 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
27 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
39 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
1 vote
2 answers
79 views

Does a transaction have to fit in memory?

I am loading some data into my postgres database using a python script. These data are all in huge csv files, so I read them lazily and call INSERT statements on batches of data. However, these data ...
Him's user avatar
  • 133
1 vote
1 answer
21 views

Logical replication security risk with run_as_owner = false

The docs say If the subscription has been configured with run_as_owner = true, then no user switching will occur. Instead, all operations will be performed with the permissions of the subscription ...
Felipe's user avatar
  • 305
1 vote
1 answer
34 views

How to simulate 'out of shared memory' caused by max_pred_locks_per_transaction?

Im using PostgreSQL 13.3 on x86_64-pc-linux-gnu, and Im trying to find the root cause of : ERROR: out of shared memory HINT: You might need to increase max_pred_locks_per_transaction. So I test by ...
MiH's user avatar
  • 125
0 votes
1 answer
103 views

Using dbeaver and postgresql can I declare a variable INTERVAL to pass into a sproc?

I have a PostgresSQL table that has a field for TIMESTAMP, and an associated stored procedure that takes an INTERVAL and calculates the difference from now() + in_interval. I want to use dbeaver to ...
eignhpants's user avatar
0 votes
1 answer
33 views

Difference between Authentication, session, outer and current user in postgres

I know about the users in postgres but don't have correct idea about them. The difference I know is Authenticated user - The user who enters his credentials to login into postgres database. Session ...
Ayush Vatsa's user avatar
0 votes
0 answers
28 views

Grant permission for a user to create objects in a PostgreSQL schema is not working

I have recently upgraded to PostgreSQL 15 and observed that a db user say X (not a owner of schema) is no longer able to create a table in public schema. From the below release notes looks like it is ...
John Wilkinson's user avatar
0 votes
1 answer
79 views

Postgres: WALWrite Waits - What's the bottleneck?

We are seeing Commits excessively waiting on WALWrite during certain times of high load. Not only certain sessions, but all commits, even vacuum. By taking snapshots of pg_stat_all_tables and ...
Peter's user avatar
  • 158
0 votes
1 answer
28 views

Missing user-defined type on importing a backup with PGAdmin

I have a strange error when importing a backuped database from my prod environment to my dev environment. the scenario is as follows: Postgres 13.x PGAdmin 4 migration files are generated by dbmate ...
robkuz's user avatar
  • 103
0 votes
0 answers
25 views

Correcting over permissive postgresql, is this the right appproach?

I have inherited a PostgreSQL (13.x) legacy DB I am working with. Original creation was with single admin role w/ownership and all rights that services connected to. We are looking to make this less ...
rahlcarwash's user avatar
1 vote
0 answers
27 views

Is there a difference in casting a string to date using date() vs ::date vs date 'column' vs CAST(column AS DATE) in Postgresql

As far as I know there are the following 4 ways to cast a string to date in postgres: SELECT '2024-04-03'::date AS colon_casting , date('2024-04-03') AS function_casting , date '2024-04-03' AS ...
Mathias Hillmann's user avatar
0 votes
0 answers
30 views

Multiple but not all executions of the same Postgres SELECT query are getting locked endlessly

I am using Postgresql version 9.2.18 on Centos Version CentOS Linux release 7.3.1611 (Core) In postgres there is a select query on a single table which is getting executed multiple times per minute. ...
user2710961's user avatar
0 votes
1 answer
20 views

writing query with multiple join on the Base Table PostgreSQL

I have a table, Company, and I am trying to query that table with many associations, Company has one plan_info with foreign_key: :company_id, has many customers with foreign_key: :provider_company_id, ...
Junaid Farooq's user avatar
0 votes
0 answers
19 views

What is the return value of an AFTER FOR EACH ROW trigger function?

Is it necessary to return NEW or is NULL sufficient? Has it any meaning to return anything in an AFTER trigger?
ceving's user avatar
  • 339
0 votes
0 answers
48 views

Relationship between /etc/localtime and Etc/UTC in PostgreSQL

I run our company's DBMS in containers, and when creating a container, the ansible deployment role mounts the host file /etc/localtime along the same path into the container (although, as far as I ...
strafer's user avatar
0 votes
1 answer
10 views

How to format an array of identifiers?

I would like to write a function to define triggers. I would like to call it this way: CALL make_mutable('mytable', '{"mycolumn3", "mycolumn4"}'); I think I need a dynamic ...
ceving's user avatar
  • 339
1 vote
0 answers
33 views

Does PostgreSQL trigram search support collations?

I'm building a trigram search using PostreSQL extension pg_trgm. I want the search to be case insensitive and to ignore accents. I've tried to achieve that by using this collation: CREATE COLLATION &...
Draex_'s user avatar
  • 111
0 votes
0 answers
25 views

What is the proper way to index status history tables in Postgres

We are using PostgreSql and have multiple tables which are laid out approximately like this. A table for some entity in our system apple id | created_at | updated_at | user_id An enum table with ...
Ethan McCue's user avatar
0 votes
0 answers
30 views

How to create a PostgreSQL collation ignoring all accents

I'm trying to develop a trigram search using pg_trgm PostgeSQL extension. The search should be case insensitive and ignore accents in Slovak language. I've tried to achieve this by creating a custom ...
Draex_'s user avatar
  • 111