23 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
0
votes
1
answer
90
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 ...
1
vote
1
answer
86
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 ...
0
votes
2
answers
193
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 ...
0
votes
1
answer
85
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
144
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
57
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 ...
0
votes
1
answer
41
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
77
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 ...
0
votes
1
answer
352
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,...
0
votes
0
answers
41
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"...
0
votes
0
answers
62
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 ...
1
vote
1
answer
360
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 ...
1
vote
1
answer
1k
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 ...
0
votes
1
answer
199
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 ...
1
vote
2
answers
386
views
Why is returning 1 required in this Postgres code block?
Asking this question for a colleague who ran into this behaviour with PostgreSQL. Hoping somebody out there can solve this puzzle for us.
The following logs Rows updated: 3 when run:
drop table if ...