100 questions from the last 30 days
5
votes
4
answers
206
views
UPDATE with LEFT JOIN and condition IS NULL
I have the following table definitions:
Table public.messages:
Column
Type
Collation
Nullable
Default
ip
text
msg
text
ignore
boolean
Table public.host:
Column
Type
Collation
Nullable
Default
ip
text
...
4
votes
4
answers
218
views
Sort aggregated query results by two methods simultaneously
I need to sort a query's results by two methods at the same time.
I want the first 3 records (returned) to be based on their prevalence in another table
And then I want the rest of the results sorted ...
- 443
2
votes
2
answers
180
views
How to avoid "ON CONFLICT DO UPDATE command cannot affect row a second time" error in WITH statement
I have two tables: demo at db<>fiddle
Table keywords has two columns id and v (which hold the keyword's value)
create table keywords(
id int generated always as identity primary key
,v text ...
- 12.4k
1
vote
2
answers
165
views
How to efficiently calculate an exponential moving average in postgres?
I'm trying to calculate the average true range on some time series dataset stored in postgres. Its calculation requires a 14 period exponential moving average of true range which based on the answer ...
- 67
2
votes
2
answers
91
views
Extract jsonb value with square bracket notation
I've got a jsonb column in my table that I can access through ->>'key' or ['key']. When I use the former it returns the unquoted value, but when I use the latter it behaves like -> and ...
- 1,974
Best practices
2
votes
2
replies
108
views
copying and deleting a collection of records from one postgresql table to a second table
Two tables with identical attributes exist: itemised_origin_rows and the target second table itemised_rows .
They exist because archival requirements are different: based on certain transactions they ...
- 6,335
1
vote
1
answer
112
views
Postgres Recursive Query Behavior
I have a table test with two columns, each referencing the other
ID1
ID2
2
1
3
2
4
3
5
4
Given an id, I need to traverse the chain to return all the rows traversing the chain. Below is the query I ...
- 325
Best practices
0
votes
7
replies
187
views
Best way to write an Oracle Procedure using Spring Data Jpa
I am new to JPA and learning it. Now I have an Oracle procedure in system, which now needs to be made DB Agnostic, that can run on Postgres. For that I want to write the same functionality of ...
- 503
1
vote
1
answer
85
views
What is the difference between session_authorization and set role
In Postgres, you can set current_user by:
set session_authorization = 'new_user';
and
set role 'new_user';
While I like the shorter syntax, I'm asking if there is a difference in functionality ...
- 3,152
2
votes
3
answers
120
views
Calculate difference between two values, including those only appearing once within the partition
DB<>Fiddle
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
stock_date DATE,
product VARCHAR,
stock_balance INT
);
INSERT INTO inventory
(stock_date, product, stock_balance)VALUES ...
- 5,565
2
votes
1
answer
80
views
Using pg_try_advisory_xact_lock for events processing with strict ordering
Suppose I am implementing a local Event-Driven service with internal events to decouple logic.
For database we use Postgres 17 and PgBouncer with transactional mode. It's just a single instance.
...
0
votes
1
answer
89
views
Equivalent to replace input date from User/API In PostgreSQL from Oracle
I am in the process of transitioning Oracle queries to PostgreSQL and I am trying to pass a date range to pull back the results.
The original Oracle query is below:
select
iss_id
,date_created
...
4
votes
1
answer
105
views
create unlogged table automatically on postgres startup or atleast not lose them due to a restart
I tried postgres unlogged table for using them as a cache to speed up queries and it works (50% query time got avoided). I can really recommend that to everyone.
But I restarted the server (I mean the ...
- 75
0
votes
3
answers
74
views
Postgresql: server does not support ssl, but it was required
I was learning postgres with flutter, and my app kept crashing when I tried to connect to the database. The database is a postgres db running on a docker container.
Here is the Dockerfile
FROM ...
- 11
2
votes
1
answer
72
views
SqlAlchemy Update statement binding entire row as values
I'm not sure if this is possible based off of the docs, but I have a text based query that updates all row values every time it is run. Originally this was done via Oracle and its cursor, passing in ...
- 79
-1
votes
1
answer
64
views
Adding CHECK Constraint Without Error and Duplication [duplicate]
I have implemented this code to add a check constraint after creating the table
ALTER TABLE recruitment_data.candidate_experiences
ADD CONSTRAINT chk_experience_dates
CHECK ( ...
- 31
2
votes
1
answer
52
views
EDB Postgres on OpenShift – Unable to Read Content from postgres.json Log File
I've deployed an EDB cluster on OpenShift, but I'm unable to read the contents of the postgres.json file. Do you happen to know why this might be the case, or if there's a workaround to access the log ...
- 37
2
votes
1
answer
98
views
Postgres not using indexes when checking for nulls
I have two tables. They each have around 2.3 million rows in them.
They are running on PostgreSQL 17.4
CREATE TABLE keyeddata (
category text NULL,
key1 text NULL,
key2 text NULL,
key3 ...
- 41.3k
2
votes
1
answer
83
views
System cache constantly grows when stress testing backend app
I've been stress testing my .NET application that uses PostgreSQL to find memory leaks. The app is very stable memory-wise — memory usage only depends on the number of concurrent connections.
However, ...
- 21
1
vote
1
answer
84
views
PostgreSQL UUID query issue
I have the following in a TS script that I'm running with the npm tsx package (slicing to 1 item to make it shorter, but same error when using full array):
console.log(psql(`SELECT c.id lead_uuid, ...
- 21
-3
votes
0
answers
66
views
The Rails console is trying to connect to PostgreSQL via Docker and is giving an error [closed]
I'm trying to access PostgreSQL from within my backend application using Docker, but I'm getting the following error:
User.last
#=> There is an issue connecting to your database with your username/...
2
votes
1
answer
71
views
How do I fix the error psql: /lib64/libpq.so.5: no version information available (required by psql)?
I'm working on a Zabbix installation on Centos 8 Stream, and have run into a situation where a failed installation of postgresql is interfering with a new installation. I am getting the error psql: /...
- 460
1
vote
1
answer
94
views
Avoid invalid cast inside CASE expression, in a query plan not respecting JOIN
I'm trying to get partitioned tables from a postgres DB. Our partitions have the date in them in various formats which I'm extracting like this
SELECT tab.relname AS table_name,
(CASE
...
- 1,006
1
vote
1
answer
61
views
Slow TimescaleDB lookup on indexed column (event_key) without a time filter
I'm using a self-hosted TimescaleDB instance to store logs. I have a hypertable logs that is partitioned by the timestamp column.
I need to perform a fast lookup query based on the event_key column, ...
- 11
0
votes
0
answers
73
views
How to get a deletion trigger to fire on a logical replica when a record is deleted on the source db
Due to performance concerns by the system devs and internal politics we cannot add triggers to the prod tables to capture deletions (long story, we need to to implement pseudo-CDC to a third party ...
- 3