Stack Overflow
Stack Overflow

Questions tagged [postgresql-13]

Use this tag to indicate that your question is about PostgreSQL version 13. Questions concerning database administration should go to https://dba.stackexchange.com/

368 questions
0
votes
1 answer

Postgres function to return regexp_matches result

I am trying to write some postgresql functions to help me parse a string value like "30Gi" or "25Ti". I tried this, but can't get the syntax right, and can't figure out what the return type should be for the first function. CREATE FUNCTION…
vasia
  • 1,093
  • 7
  • 18
0
votes
1 answer

Could not open extension control file POSTGRESQL

While trying to execute PostgreSQL extension in PostgreSQL 13 CREATE EXTENSION postgres_fdw; I am getting an error CREATE EXTENSION postgres_fdw "Could not open extension control file "/opt/pgsql13/share/extension/postgres_fdw.control": No such…
Saswat Swarup
  • 1
  • 1
  • 1
0
votes
1 answer

How to test PosgreSQL deadlocks with pgAdmin

I would like to test deadlocks on PostgreSQL 13 using pgAdmin 4, with different lock types and isolation levels. So far, I have tried opening two pgAdmin tabs and running different transaction blocks like these: --LOCK stats IN SHARE ROW EXCLUSIVE…
JourneyToJsDude
  • 187
  • 1
  • 3
  • 13
0
votes
2 answers

How to concurrently update PostgreSQL 13 table row without locks

I need to update a table with different measures per id and these updates will be run by different transactions. I need to know if there is a way to unlock the row on the update statement as I do not need to read data at the moment and data…
JourneyToJsDude
  • 187
  • 1
  • 3
  • 13
0
votes
2 answers

Cascade Delete Children not working as expected

I have two tables one of which is for the polymorphic relationship of different corporations and I've added foreign key references to ids to ensure that if I delete a parent all children will be deleted. With this table setup below if I delete a…
Josh Wren
  • 338
  • 2
  • 12
0
votes
1 answer

PostgreSQL: Dirtied blocks when using many OR conditions in query

I'm using PostgreSQL 13.7. When trying to optimize a query that is made of several sub-queries, I noticed that reducing the number of OR clauses in the final part significantly improves performance (800ms to 70ms). The part in question is this (only…
Alechko
  • 1,406
  • 1
  • 13
  • 27
0
votes
0 answers

RETURNING after double CTE Insert

I would like to insert some values in this way: with first_insert as ( insert into stacks(press_id,quantita_a,quantita_b,quantita_c) values($press_id, coalesce((select something),-1), coalesce((select something),-1), …
morphineglelly
  • 107
  • 7
0
votes
1 answer

Linked Service on Azure Data Factory V2 (on active directory 1) to an Azure PostgreSQL server (on active directory 2)

I'm trying to create a linked service on Azure Data Factory V2 (from my personal subscription) to an Azure PostgreSQL database hosted by company A (on a different active directory and subscription). I have already made sure that the "Allow access…
Shehryar Siraj
  • 1
0
votes
1 answer

Can't select existing value, after row update it works | [postgres:13.5-alpine3.15]

I'm using pg_admin on to select a specific user by email address. The selection looks like this SELECT id, public_id FROM public.users where email = 'user1@test-inger.com'; I know that the user exists with ID 102 with the exact string for the…
abex.co
  • 41
  • 1
  • 1
  • 8
0
votes
1 answer

What row is violating this check constraint?

I'm trying to add the following check constraint to the raffle_participant table: ALTER TABLE raffle_participant ADD CONSTRAINT ck_raffle_participant_total_purchased_le_ticket_limit CHECK (tickets_purchased_in_raffle(raffle_id) <=…
jcarrete5
  • 37
  • 2
  • 7
0
votes
1 answer

How to determine the total freespce of the db PostgreSQL

How to identify the total size of the db (Used) and the total size of the db. for the total size of the db (Used) : pg_database_size('dbName') cmmnd works. But I am not sure how to calculate the free space size of the db. (total capacity I mean) I…
Vedika
  • 1
0
votes
1 answer

Postgresql - Autoincrement ID

I am using Postgresql v13.4 and I want to update an existing and have got some problems to keep the existing ID when there is a conflict. I am creating the following table which is successfully shown in Postgresql: query = """ DROP SEQUENCE…
Andi Maier
  • 914
  • 3
  • 9
  • 28
0
votes
1 answer

How to tune vacuum process in PostgreSQL?

As per my understanding PostgreSQL autovacuum run depending on below formula. Here I found an issue is that, it could run any time when below result/value is matched. Suppose threshold=50 and scale factor = 0.2 and live tuple is 1000 , if total dead…
Sheikh Wasiu Al Hasib
  • 450
  • 5
  • 18
0
votes
1 answer

PostgreSQL 13 Partition Global Indexing Strategies

Based on my research so far, I understand that it is not possible to create an out-of-the-box global index across partitions in PostgreSQL 13. Are there any alternative strategies that can facilitate the requirement of a global index across…
Anu Karunamuni
  • 1
0
votes
1 answer

How to transcode Unicode to ISO 8859-1 with postgres 13

How can I transcode a UTF-8 string to Latin1 with PostgreSQL 13+ ? I've read this SO thread but the functions convert(), convert_from() and convert_to() no longer exist starting from Postgres 13. EDIT: the solution is given by Laurenz Albe, who…
Code4R7
  • 2,600
  • 1
  • 19
  • 42
1 2 3
…
24 25