In the case that I have the same example data as in this question and additionally declare the following two functions: CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_price(_symbol text, _time_of timestamptz, _start interval, _duration interval) RETURNS float8 LANGUAGE sql STABLE STRICT PARALLEL…
Let's assume that we have two tables users and messages with the following fields: users: id, name, email messages: id, content, user_id I want to delete a user but keep all data in messages. Is there a way to do…
Starting from question : [https://stackoverflow.com/questions/78580133/which-query-is-executed-when-var-dumppg-fetch-allpg-query-paramsdb-select][1] and the last answer to it naturally arises the question what is the result of "select cast(2.000 as varchar);" ( don't execute it before answering on your own). Where in the docs is it explained…
I have a SQL query where the list of strings is repeated twice: ('foo', 'bar', 'baz', 'bletch'). What is the most maintainable method to avoid such repetition and make this code more DRY? I am not optimizing query performance, only…
I want to write a Postgres function that looks up the value of a column in a given table. Example setup: CREATE SCHEMA IF NOT EXISTS example; CREATE TABLE example.price_table ( symbol text NOT NULL, time_of timestamptz NOT NULL, price…
I'm working on a project where I need to store latitude and longitude in coordinates field in a PostgreSQL database. I've decided to use the Point data type in PostgreSQL to represent these coordinates efficiently. However, I'm unsure about the…
I'm writing retry logic for handling various exceptions that can arise interacting with PostgreSQL using JDBC. I want to retry for some exceptions but not for others, based on the SQL state (error code) of the exception. According to the…
I'm encountering a problem in a Spring Boot application using Hibernate, where OneToOne relationships involving entities with composite keys are not mapped correctly in a PostgreSQL database. Specifically, Hibernate maps the same entity instance to two properties in a parent…
I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed. id uuid default uuid_generate_v4() not null primary key, fk_id uuid…
I'm working with PostgreSQL and have a script that attempts to create a sequence named hibernate_seq and set its starting value: CREATE SEQUENCE IF NOT EXISTS hibernate_seq; SELECT setval('hibernate_seq', (SELECT MAX(NEXT_HI) + 1 FROM HIBERNATE_ID_GENERATION)); However, I want to achieve…