I have a user table users containing id , name and information of type jsonb User Table id name information 1001 Alice {“1″:”Google”,”2″:”1991-02-08″} 1002 Bob {“1″:”StackOverflow”,”3″:”www.google.com”} I have anot…
Tag: postgresql-13
How do combine two Queries or is it only one?
Question: How do combine two Queries or is it only one? Example: I have two exact similar tables my PostgreSQL Database. info_table and info_table_dump with the exact same columns date_at, name, color and length . Now i want to know if there are entries in info_table_dump that do not exist in info_table. Ther…
How can I replace this correlated subquery within a function call?
Given the following tables buckets points And the following query Output How can I remove the correlated sub-query to improve the performance? Currently ~280,000 points * ~650 buckets = ~180,000,000 loops = very slow! Basically I want to remove the correlated sub-query and apply the width_bucket function only…
SELECT after specific row with non-sequential (uuid) primary key
With the following table: How could I retrieve n rows after a specific id, ordered by inserted_at ? Answer I want to retrieve n rows after a specific id, ordered by inserted_at. I am expecting something like this: For this, you want one additional index on users(inserted_at).
how can i loop insert query?
I have a problem like this: I need to optimize the application, with db (postgreSQL), the table looks like this: I have more than a thousand such voters, and I need to put all of them in the database, but among them there are several duplicates that could vote several times (from 2 to infinity), and I need to…
PostgreSQL: `VIEW` returns no rows with `SPLIT_PART()` columns
Problem Description: Hi everyone, I’m having some troubles querying on a VIEW whose columns are, in part, the result of SPLIT_PART() function on og table’s column; I created the VIEW as it follows: My intention was to divide the structured attribute (Clients.Address defined as a string VARCHAR(255…
Test ARRAY whether it contains ALL or NO elements of given ARRAY
Suppose we have an array: With the operator <@ I can query whether the left-operand is a sub-array of the right-operand: The above works greatly but now let’s take the following case: In this case the above will return false as expected. Do we have an operator which says the following: either the lef…
Trigger before insert in PostgreSQL and except some columns
I’m trying to write a trigger for my table. This table has 50 columns and 3 of them are timestamp type. In the future I will insert new rows and they can be duplicate of existing, so I need to compute hash of each row. My idea is to compute row’s hash in each insertion and check it’s existin…
Find closest match to value in another table
I have a table_a with many rows and columns for each timestamp in PostgreSQL 13. I’m trying to find the row where the value in column X is closest to a benchmark value obtained from another table. This second table has only a single benchmark value for each timestamp. For each timestamp, I need to retur…
H2 vs PostgreSQL generated column with function
I’m trying to setup a generated column which will also take null checks into consideration when subtracting values. In PostgreSQL I did: ALTER TABLE session ADD COLUMN duration INTERVAL GENERATED …