All Questions
Tagged with postgres-16 or postgresql-16
91 questions
2
votes
1
answer
95
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,871
3
votes
1
answer
65
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
1
vote
1
answer
39
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
votes
1
answer
99
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 ...
0
votes
1
answer
52
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
629
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
1
vote
4
answers
203
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
572
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:
...
0
votes
0
answers
148
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
1
vote
1
answer
155
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
1
answer
211
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
0
votes
1
answer
166
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,157
0
votes
0
answers
446
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
...
- 711
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,345
0
votes
1
answer
111
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,345
-1
votes
1
answer
176
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
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,361
0
votes
0
answers
164
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 ...
- 167
1
vote
1
answer
923
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
80
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
56
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,871
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
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,871
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
0
votes
2
answers
138
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