91 questions
2
votes
1
answer
121
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 ...
- 1,881
0
votes
0
answers
55
views
Postgres Docker won't setup properly [duplicate]
I'm not really sure what's the issue here. I wrote a rather "generic" docker-compose.yml for Postgres, but neither won't it do its own setup properly, nor can any of the other containers (...
- 1,661
3
votes
1
answer
71
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
84
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
41
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
95
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
107
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
82
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
61
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,567
1
vote
1
answer
80
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 ...
1
vote
1
answer
658
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 ...
- 41
0
votes
0
answers
62
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.8k
1
vote
4
answers
226
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
97
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,791
0
votes
1
answer
604
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:
...
1
vote
1
answer
455
views
Use MERGE with CTE
Does Postgresql allow use of cte as source of the MERGE instruction ?
CREATE TABLE IF NOT EXISTS graph
(
idgraph SERIAL PRIMARY KEY,
from_table varchar(128)
);
WITH cte2 AS
(
SELECT 'tag'...
- 10.8k
0
votes
0
answers
167
views
PostgreSQL pg_dump Fails: Password Authentication Error with Special Characters in Password
I am trying to execute a pg_dump command from PostgreSQL using a stored procedure. My setup uses EXECUTE format to dynamically construct the command. Here is the relevant code:
'COPY (SELECT 1) TO ...
- 131
0
votes
3
answers
2k
views
ERROR: must be owner of table, but I am superuser
I have an postgres 16 DB in Docker container with volume. I wanted to hide standard postgres user and changed it's name to super (as example). Now I try to make something with any tables (DDL ...
1
vote
1
answer
166
views
How to set replica identity full for a table having different owner?
I have two owners in my postgresql database e.g. testdb, I have some tables of owner1 and some tables of owner2. I am facing issue in setting replica identity full for tables of owner1 using owner2 ...
- 11
1
vote
0
answers
356
views
PostgreSQLConfigurationExtension does not define or inherit an implementation extractParametersFromConfiguration(java.util.Map)'
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/springframework/boot/autoconfigure/flyway/...
- 11
1
vote
1
answer
222
views
Error while setting up pgactive extension
I am following the AWS's documentation to setup pgactive extension and have an active-active setup on two AWS RDS Postgres instances. Following all the steps successfully till step 11 where it asks to ...
- 449
1
vote
1
answer
67
views
Is the order of result_types in a Postgres prepared statement guaranteed to match the column order?
Basically as the title says, I'm trying to figure out if the order of elements in the array in pg_prepared_statements.result_types is guaranteed to match the column order of the results when you do ...
- 483
0
votes
1
answer
176
views
How to display current_timestamp in UTC along with timezone in postgresql?
Iam using PostgreSQL 16 and running the following query.
select (current_timestamp AT TIME ZONE 'UTC');
which is returning UTC time without timezone appended.
2024-10-04 18:44:59.34674
How to ...
- 1,167
1
vote
1
answer
126
views
Is it safe to set the statistics target to zero for numeric columns?
I have some PostgreSQL tables with columns of type:
timestamp without time zone
interval
text
numeric
For example assume a table like:
CREATE TABLE IF NOT EXISTS table_foo(
"timestamp" ...
- 301
1
vote
0
answers
487
views
How do I make my psql client version the same as the server version?
I have PostgreSQL installed on my MacBook running macOS Sonoma version 14.5
I installed it using the Homebrew package manager.
I think I have two versions installed:
brew list
postgresql@14
...
- 721
0
votes
1
answer
60
views
Find procedure name in all other procedures
procedure 1:
create or replace procedure pro_1()
as
$$
begin
raise info 'pro1';
end
$$
language plpgsql;
procedure 2:
create or replace procedure pro_2()
as
$$
begin
raise info 'pro.1'...
- 7,355
0
votes
1
answer
39
views
Execute function from procedure, the name of function stored in table column
Table condition: This table contains function name in the column fn_name
create table condition(id int,name varchar(50),fn_name varchar(50));
Data:
insert into condition values(1,'check id','...
- 7,355
-1
votes
1
answer
97
views
Large text data making remote query extremely slow
I have a table that has a column that stores html in a text column.
The database is remote. If I try to say pull the first 500 records with the html column it will take 25 minutes for the query to ...
- 15.1k
0
votes
1
answer
115
views
Execute anonymous code from stored procedure
We have a table which consist of anonymous code/token which needs to be executed from procedure.
Here is the sample data:
Table:
create table tbl_tokens (id int,tokens text);
Record:
insert into ...
- 7,355
0
votes
0
answers
443
views
Troubleshooting SSL Connection Issues in PostgreSQL 14 with OpenSSL 3.2 and 3.3
I source-compiled OpenSSL version 3.3 and compiled PostgreSQL 14 with it. When I tried to enable encryption and authentication via SSL in PostgreSQL with ssl_mode=on, I encountered errors related to ...
- 41
-1
votes
1
answer
203
views
Timescaledb high resource consume
I’m working with a TimescaleDB instance that has 10 CPU cores and 10 GiB of RAM, currently hosting approximately 9 million rows, which is continually growing. I'm encountering resource limitations and ...
- 97
-2
votes
1
answer
66
views
postgresql function not raising exception
Im trying to test out pg_input_is_valid & pg_input_error_info and it doesnt seem to work.
From the below example i expect it to raise a message that 56.7899999 does not meet the criteria. Any ...
- 1
0
votes
1
answer
4k
views
How do I create a "GENERATED ALWAYS AS IDENTITY" or "GENERATED BY DEFAULT AS IDENTITY" column using the pgAdmin GUI in PostgreSQL?
I'm trying to figure out how I can add a GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY without needing to write SQL code.
I know I'm able to add them using code like this:
ALTER ...
- 4,464
0
votes
1
answer
393
views
Creating a serial ID sequence that is unique across all tables in an SQL database AND doesn't require redundant entry creation/edits?
I'm building a database for recording extremely detailed information about individual items in a collection that spans a number of different media types (books, CDs, DVDs, etc). Every item in the ...
0
votes
0
answers
166
views
Unable to connect to postgres database running in oracle cloud
I run postgres in a docker container on oracle cloud. I opened the port with iptables and via the oracle cloud web interface.
Somehow I can still not connect to the database using Datagrip. I also ...
- 177
0
votes
2
answers
369
views
How to avoid circular relations in postgres using cycle detection
I'm trying to use CYCLE detection in PostgreSQL to avoid circular nesting of groups but cannot figure it out.
I have the following PostgreSQL 16 table:
CREATE TABLE groups (
id serial PRIMARY KEY,
...
- 158
1
vote
1
answer
955
views
PostgreSQL: Query with ORDER BY, LIMIT, and OFFSET consistently returns the same record at the end of the result set regardless of OFFSET value
Our team has sat on this for a while and struggling to understand what is going.
When ordering by created_at and all the created_ats are the same for the records we see the same record appearing as ...
0
votes
1
answer
490
views
How can I avoid "ERROR: there is no unique constraint matching given keys for referenced table" on referencing combination primary key on Postgres?
I have the following table definition in the schema on pgAdmin4 I'm trying to execute:
CREATE TABLE IF NOT EXISTS Reporting
(
SEQ integer,
Product character varying(30) NOT NULL,
Version integer ...
- 25
0
votes
1
answer
84
views
How to print the RAISE NOTICE output , if I am using it with psycopg2?
I'm trying to print during the loop:
WHILE (batch_offset < total_records) LOOP
-- query code
-- Get and display the number of rows processed
GET DIAGNOSTICS rows_processed = ROW_COUNT;
...
- 33
0
votes
1
answer
208
views
Error creating bean with name 'postgresMlEmbeddingModel' defined in class path resource
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'postgresMlEmbeddingModel' defined in class path resource [org/springframework/ai/autoconfigure/postgresml/...
0
votes
1
answer
57
views
How can I get a duplicate key error if the row does not match the predicate of the unique index?
I'm using Postgres v16. I've got a unique index on my invoice table:
CREATE UNIQUE INDEX unq_invoice_display_id
ON invoice (tenant_id, display_id, expiry_timestamp)
WHERE id = original_invoice_id;
#...
- 1,881
0
votes
1
answer
371
views
Using TG_TABLE_NAME in postgresql trigger function in a SELECT query
I have this plpgsql trigger function
DECLARE
prev_record daily_data%ROWTYPE;
BEGIN
SELECT * INTO prev_record
FROM ONLY TG_TABLE_NAME
WHERE base = NEW.quote AND quote = 'USDT'
...
- 97
1
vote
1
answer
51
views
Copying data from one postgres db to another
I want to copy data from one PG database to another, without moving the data to my local and back to the db -
pg_dump -U postgres source_db_name | psql -U postgres target_db_name
pretty certain this ...
- 102k
0
votes
1
answer
231
views
In Postgresql is there a way to flatten or aggregate nested JSONB arrays that can be used with generated stored columns?
So imagine I have a table with a jsonb column data (for the sake of example, not because it's a good column name) that contains an array of json objects with a key states that is an array of USA state ...
- 305
29
votes
3
answers
18k
views
Using Postgres 16 with Spring Boot 3.3.0
I just upgraded from spring-boot 3.2.3 -> 3.3.0. After the upgrade flyway refuses to connect to postgres:
Caused by: org.flywaydb.core.api.FlywayException: Unsupported Database: PostgreSQL 16.2
...
- 1,881
0
votes
0
answers
45
views
pg_restore through .bat on pgAdmin4
I am reaching out to seek assistance with an issue I've encountered while transitioning from PostgreSQL 9.4 to PostgreSQL 16. The primary reason for this upgrade was the discontinuation of PostGIS ...
- 1
0
votes
0
answers
1k
views
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory on MAC M1 with Postgresql NOT installed with brew
I have exactly this problem but NONE of the solutions in this thread helped me:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
I am ...
- 183
1
vote
1
answer
756
views
Bad query performance postgres 16
We are trying to make the step from postgres 10 to 16, we already tried a while ago to lower versions 12 or 13, but updates were added
in the machine and the poor performance we attribute to those ...
0
votes
2
answers
139
views
Complicated column dependencies in postgreSQL
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is '...
- 33
-1
votes
1
answer
77
views
Use results from one query in WHERE clause of the next query
This is how it currently works, first select query returns a list of parameters.
SELECT character_id, foe_id, location_id, date_time, damage, points
FROM events
ORDER BY date_time DESC
LIMIT 100
...
- 15