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 another Table having all the profile fields values named ProfileFields profilefieldid Value 1 Company 2 DateOfBirth 3 ProfileLink The information jsonb column can only have keys present in the ProfileField Table. You
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. Therefore i made these Query: The result is
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 once per unique metric_id in buckets, so that the performance is improved
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, when
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)) which contains all the informations releated to client’s domicile in several columns to
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 left array is contained by the right
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 existing, that’s
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 return most of the columns
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 …