All Questions
Tagged with postgres-16 or postgresql-16
91 questions
3
votes
1
answer
188
views
How to create Index CONCURRENTLY on parent of partitioned table in PostgreSQL 16 / PolarDB-PG 16? [closed]
I have been using PolarDB for PostgreSQL 14 (PolarDB-PG 14), where I was able to run CREATE INDEX CONCURRENTLY on the parent of a partitioned table without errors.
Recently, I upgraded to PolarDB-PG ...
- 49
2
votes
1
answer
237
views
What is the recommended collation for a postgresql citext column?
I'm using postgres 16, and I have a number of tables where I need to treat their display ID columns as case insensitive, and also handle LIKE queries with wildcards (ex: P%123%). In order to handle ...
- 2,031
3
votes
1
answer
74
views
"invalid transaction termination" when executing dynamic sql
I am trying to do some transaction management in calls invoked by dynamic sql (EXECUTE).
The following works:
CREATE or replace PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
...
- 373
0
votes
0
answers
97
views
Postgres not finding data that is clearly present
This is the 2nd time this has happened to me in a few months. The first time was with Postgres 14, and this time with 16.
I can't make any sense out of this. If I query, the account_token, then paste ...
- 418
1
vote
1
answer
47
views
postgresql merge into transaction logs
When I use the MERGE INTO statement in PostgreSQL 16 with the WHEN MATCHED THEN UPDATE SET option, will a transaction log only be created on difference, or even if it's the same value? Specifically, ...
- 10.5k
1
vote
1
answer
126
views
PostgreSQL: is the CTE guaranteed to be evaluated before INSERT?
I need to upsert a row by primary key and return the previous value before the update.
I know I could do this with two separate queries — a SELECT followed by an INSERT ... ON CONFLICT ... DO UPDATE, ...
- 13
-1
votes
1
answer
128
views
connection failed: :1), port 5432 failed: could not receive data from server: Connection refused
I got this error when im trying to get access to my postgreql db by pgAdmin.
[tag:connection failed: :1), port 5432 failed: could not receive data from server: Connection refused]
Run this brew ...
2
votes
1
answer
88
views
Postgresql SELECTs went from 0.6s to 10+s after killing a (different) long running SELECT query
We are running a PostgreSQL 16.2 server on a dedicated physical machine with local storage. This database has been stable and fast for years. We started seeing significant and increasing slowdowns on ...
- 21
0
votes
1
answer
69
views
ZEOSLIB, Delphi error: "close all lob streams before closing the resultset"
I am getting this error when exiting my application. I am using delphi 7 with ZEOSLIB 8, PostgreSQL database ver 16.
The Delphi statement I use for loading blobs from database into bitbutton blobs is ...
- 6,577
1
vote
1
answer
96
views
How to improve Postgres pg_trgm for text similarity and make more similar text rank higher?
I am using Postgres's pg_trgm for finding similarity of alphanumeric text based on trigram matching. The text being searched on is a title column containing various titles of news articles.
SELECT ...
0
votes
2
answers
762
views
Could not create postgis-3 extension in postgresql-16 on Ubuntu 22.04
I was recreating my database with fresh test data, so I dropped / created it.
I installed Postgres 16 / postGIS 3 successfully on Ubuntu 22.04 a month ago, but then after I dropped my database, the ...
- 39
0
votes
0
answers
66
views
Update join effect not visible in first run of select
I have this very strange behavior using pglite (PostgreSQL version 16) in Firefox, where I run an UPDATE JOIN, and then a SELECT which the first time around doesn't reflect the change made by the ...
- 10.9k
1
vote
4
answers
287
views
Switch ASC / DESC in ORDER BY with a CASE construct?
In PostgreSQL, I am trying to have SQL which orders by multiple columns. The first column will always be priority DESC NULLS LAST. The second one needs to be dynamic based on some conditions (for ease ...
0
votes
1
answer
114
views
What does `+` (plus) means next to enum elements [duplicate]
I'm playing with Postgresql ENUM types, and noticed something I can't find explanation for:
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | ...
- 6,801
0
votes
1
answer
726
views
AAD roles on Postgres flexible server
Happy Friday, fellow IT people!
I am creating infrastructure for Azure Postgres flexible server along the roles required for authentication via terraform. Below is the contents of my main.tf file:
...
