21 questions
0
votes
1
answer
193
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,139
0
votes
2
answers
114
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
126
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....
- 821
4
votes
2
answers
105
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
153
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
73
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 ...
- 754
0
votes
1
answer
174
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,335
0
votes
1
answer
178
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
17
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
76
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
193
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
44
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
25
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
113
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.3k
-2
votes
1
answer
101
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