24 questions
0
votes
1
answer
65
views
Postgres sql Foreign constraint ON DELETE RESTRICT ON UPDATE CASCADE
There are 2 tables 'users' and 'address'.
create table address (
username varchar(255) not null,
address1 varchar(255),
primary key (username)
)
and
create table users ...
- 94
1
vote
1
answer
66
views
postgres Error [42P17] ERROR: generation expression is not immutable
ALTER TABLE my_table
ADD date date GENERATED ALWAYS AS ((timezone( 'Asia/Kolkata'::text,
COALESCE( timestamp_with_timezone_1, timestamp_with_timezone_2 ) ))::date) STORED;
I get the ...
- 1,026
0
votes
2
answers
147
views
How to fail if more than X number of requests are trying to get pg_advisory_lock?
I'm looking for a simple and nice and blocking way to get an exclusive lock:
SELECT pg_advisory_lock(hashtext(lock_id));
Is there a way to fail if more than X number of attemptors are trying to get ...
- 101k
0
votes
1
answer
73
views
Why does using `LIMIT` in this CTE give different results compared to using `LIMIT` in a subquery that refers to that CTE?
We have this query:
WITH
childObject AS (
SELECT
JSONB_BUILD_OBJECT(
'childObjectId',
(
SELECT
values_table.value::text
FROM
...
1
vote
3
answers
132
views
Is there a way to prevent ROW_NUMBER's ORDER BY from having to repeat the same thing?
I currently have this SQL:
select ROW_NUMBER() OVER (order by priority DESC NULLS LAST,
created_at DESC) as index
, id
, title
, ago(created_at)
,...
1
vote
1
answer
53
views
Grouping by a date in a timestamp depending on time zone
This is my query:
SELECT COUNT(*)
, DATE(created_ts)
FROM users
GROUP BY DATE(created_ts);
if the user selects a time range, such as
2024-12-17 00:00:00-2024-12-17 15:59:59
but a problem arises ...
- 11
0
votes
0
answers
64
views
pg_basebackup stuck and does not complete due to EAGAIN (Resource temporarily unavailable) on Primary and Secondary(replica) does not finish
I am trying to create a replica using pg_basebackup and every time it gets stuck at a random percentage (80 to 96) percentage. The primary is looping in the getting "recvfrom(10, 0x7ffe06388c27, ...
- 670
0
votes
1
answer
36
views
How to have a row always on top even when using OFFSET?
I'm using Postgres.
I want to have a row always on top (example alias = 'abc') in a query regardless of the offset being used.
Right now, I'm doing
ORDER BY
CASE
WHEN alias = 'abc' THEN 0
ELSE ...
0
votes
0
answers
73
views
Mass updating Postgres rows in-place to avoid primary key index scans
I've got a large table (25M rows, 30GB) that needs to have a large amount of foreign keys set to a new table that I've just added. I've prepared a temporary table that holds the primary key ID of the ...
- 1,106
0
votes
1
answer
327
views
Postgres Text Search extremely slow
Using PostGres 15.5
My table looks like this:
CREATE TABLE IF NOT EXISTS directory_network_entity (
id varchar(70) NOT NULL,
country_code_2 varchar(2) NOT NULL,
country_name varchar(50) NOT NULL,...
- 2,001
0
votes
0
answers
39
views
Could not find driver on insert for a failed job
I can't seem to find the reason for the below error log:
[2024-03-07 16:28:32] local.ERROR: could not find driver (Connection: postgres, SQL: insert into "failed_jobs" ("connection"...
- 1
0
votes
0
answers
56
views
PEM auto discovery not working for community postgres 15
We’re using postgres community version 15 on suse linux 15.3 . We have enabled system config file and started the database. But while we try to auto discover the server in Postgres Enterprise Manager ...
- 339
1
vote
1
answer
344
views
Execution plan for postgres view combining 2 partitioned tables is accessing all partitions
I have two partitioned tables with similar range partitions (where date_key is a date in integer form -- like 20230901 for Sep 1st, for example:
create table header (
date_key int,
header_key ...
- 58
1
vote
1
answer
947
views
Postgres: is there an exhaustive list of causes that make `CREATE INDEX CONCURRENTLY` end in an `INVALID` state?
Besides these potential, documented causes:
a deadlock
hitting statement_timeout
for a unique index: a uniqueness violation
for expression and partial indexes: expression evaluation errors
Are there ...
- 353
0
votes
1
answer
190
views
How do i prevent updated row from shifting or changing to last row in Postgres using C code?
I'm new to Postgres and i have been creating table's using C program, so now I have run into a issue where a updated row is moving or shifting to the last row of the table and I have to prevent this ...
