21 questions
0
votes
1
answer
216
views
Why doesn't index creation work any more in Postgres v17 and v18?
Considering the following SQL code:
create table test (a int, b int) ;
create type tst as (a int, b int) ;
create function tst (a int, b int) returns tst[] language sql immutable as
$$ select array(...
- 7,129
0
votes
2
answers
198
views
PostgreSQL 17.5: "canceling statement due to conflict with recovery or timeout" when creating logical replication slot on replica
I'm trying to create a logical replication slot on a GCP managed PostgreSQL 17 read replica but getting intermittent failures with two different errors. The replica was recently upgraded from ...
- 193
1
vote
0
answers
165
views
Orphaned pg_temp_# and pg_toast_temp_# schemas in postgresql 17.5 slow down ORM model update
I am running PG 17.5 Windows using the EDB community distribution. I am using Entity Framework ORM with Devart's dotConnect for PostgreSQL. The issue I am about to describe did not occur under PG 13....
- 841
4
votes
2
answers
145
views
PostgreSQL create materialized view fails when create table with same code works
I am using PostgreSQL 17 (with PostGIS extension) installed on a Ubuntu 24.04 system. I have a very strange problem where a certain query works as a straight query (or to create a table), but the same ...
- 885
3
votes
3
answers
163
views
How to create group column based on ids and linked ids in PostgreSQL
I have a table containing incident ids. Each incident can be linked to another one because they have been stated as similar. The table contains two columns:
incident_id the id of the incident
...
- 701
0
votes
1
answer
78
views
Create an immutable version of CONCAT()
I tried to create an immutable CONCAT(). For this, I tried to use the following SQL queries.
Option 1
CREATE FUNCTION immutable_concat(VARIADIC text[])
RETURNS text
LANGUAGE internal IMMUTABLE ...
- 752
0
votes
1
answer
215
views
Oracle style global temporary table in PostgreSQL
While migrating database from Oracle to PostgreSQL, I came across "Global Temporary Table" which I need to migrate equivalent to PostgreSQL.
Document say's
Optionally, GLOBAL or LOCAL can ...
- 7,355
0
votes
1
answer
221
views
Call multiple REINDEX CONCURRENTLY commands
I detected several indexes that need reindexing, then I created a simple script to call REINDEX CONCURRENTLY for them
reindex index concurrently public."chp_id2015";
reindex index ...
- 670
0
votes
0
answers
20
views
I am trying to uninstall postgres but it is showing that it is open in another folder how to find that folder?
I am trying to uninstall postgres but it is showing that it is open in another folder how to find that folder?
I am just trying to uninstall it so I can config it with my odoo server for database ...
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
3
answers
207
views
Remove first and last element from an array
I can remove easily the first element from an array:
SELECT (ARRAY[1,2,3])[2:];
{2,3}
What is the simplest way to remove the last element as well?
The size is dynamic and is not known at compile time.
- 10.8k
1
vote
1
answer
53
views
Convert string back to array of tuples
I have an array of tuples in PostgreSQL 17.
I couldn't get SELECT...INTO...FROM to work for some reason:
SELECT ARRAY[(1,2)] AS a INTO temp_table;
ERROR: column "a" has pseudo-type record[]
...
- 10.8k
0
votes
0
answers
30
views
Python Postgres pull as byte object vs str
I'm doing a basic Python Flask application with a Postgres database using psycopg2. I'm having trouble setting up the user registration password hash as it appears I'm working with a string vs byte ...
- 399
3
votes
2
answers
120
views
Why is DELETE FROM vehicle_data WHERE NOW() > expires_at; failing in my stored function?
I have prepared a DB Fiddle for my question and I will also show my complete code below -
I am trying to store data in a vehicle_data table and each data record has an expires_at column:
-- Create ...
- 23.4k
-2
votes
1
answer
109
views
How to migrate 5TB of data from PostgreSQL 13 on Oracle Linux 7.9 to PostgreSQL 17 on Ubuntu 24? [closed]
I need to migrate a PostgreSQL database (~5TB in size) from version 13 running on Oracle Linux 7.9 to version 17 running on Ubuntu 24. The database includes critical data and downtime must be ...
- 57
1
vote
1
answer
83
views
Can MERGE activate multiple WHEN MATCHED AND conditions
Can multiple WHEN MATCHED AND ... conditions be activated within a single SQL MERGE?
MERGE INTO translation t
USING ( SELECT d.iddiagram
, rt.uuid_translation
, rt.context
...
- 10.8k
1
vote
1
answer
2k
views
PostgreSQL Error invalid DSA memory alloc request size. Pg 17 configuration
2 weeks after migrating to Postgres 17.2 we started getting an error on a query that worked flawlessly for years on Postgres 14. I suspected this could be related to the configuration parameters of ...
- 1,047
2
votes
2
answers
600
views
how to install Citus extension for postgresql-17
How to install Citus extension for postgresql-17? I have been looking for it on many search portals.
My intention is to install Citus extension for Postgres-17 version, any help much appreciated.
I ...
- 31
2
votes
1
answer
466
views
MERGE RETURNING into temp table without a CTE
Is something like this possible without having to use WITH?
Can CREATE TEMP TABLE temp AS be used instead of creating the temp table manually?
CREATE TEMP TABLE temp
( action text,
winery_id ...
- 119
5
votes
1
answer
4k
views
PostgreSQL 17: pg_upgrade on Windows Server 2019: could not load library "$libdir/adminpack": No such file or directory
Installed PostgreSQL 17 from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.
Switched off postgres 13 and 17 Windows services.
Gave read and write permissions to both bin and ...
17
votes
4
answers
54k
views
How to ignore errors with psql \copy meta-command
I am using psql with a PostgreSQL database and the following copy command:
\COPY isa (np1, np2, sentence) FROM 'c:\Downloads\isa.txt' WITH DELIMITER '|'
I get:
ERROR: extra data after last expected ...
- 8,143