Postgresql – Can you generate a Liquibase changelog XML file from SQL code?
I have some SQL scripts that I'd like to convert into Liquibase XML changelogs. Is this possible?
Postgresql – Fastest way to filter latitude and longitude from a sql table?
I have a huge table data where sample data is like below. I want to filter few latitude and longitude records from the huge table and I am using In clause to filter list of lat,lon values but when I…
Postgresql – Picking only one key value pair in jsonb column based on a condition
I am trying to get the source and target where source is 270 and the column is jsonb and values like [{"source": 270, "target": 285}, {"source": 427, "target": 285}, {"source": 285, "target": 285}] The expected output is {"source": 270, "target":…
SQL Window Function over sliding time window – Amazon web services
I have the following data: country objectid objectuse record_date 2022-07-20 chile 0 4 2022-07-01 chile 1 4 2022-07-02 chile 1 4 2022-07-03 chile 1 4 2022-07-04 chile 1 4 ... ... ... ... 2022-07-26 peru 3088 4 2022-07-27 peru 3088…
Postgresql – SQL Union and special sum
I'm new to SQL queries so hopes this question isn't stupid. I got two tables like this: Table 1: Name Value Count global g 1 domain x 2 domain y 1 agg ba 1 Table 2: Name Value Count global…
Postgresql – make numeric values homonyms fractions in sql table
i have a table like this: ID num_A num_B 1 1 168 2 1 4 2 5 24 2 6 24 3 1 36 So, num_A and num_B represent a fraction. That means for ID=1, i have 1/168, ID=2 --->…
Postgresql – Get certain rows, plus rows before and after
Let's say I have the following data set: ID Identifier Admission_Date Release_Date 234 2 5/1/22 5/5/22 234 1 4/25/22 4/30/22 234 2 4/20/22 4/24/22 234 2 4/15/22 4/18/22 789 1 7/15/22 7/19/22 789 2 7/8/22 7/14/22 789 2 7/1/22 7/5/22…
Postgresql – Get unique values by multi column
I got that table CREATE TABLE test ( P_IP varchar(255), MAC varchar(255), R_IP varchar(255), dates TIMESTAMP ); Every column can be duplicate. Each row is report by timestamp I wanna get the latest row unique mac by timestamp. and if…
Postgresql – How to use upsert with Postgres
I want to convert this code in Postgres to something shorter that will do the same. I read about upsert but I couldn't understand a good way to implement that on my code. What I wrote works fine, but I…
Postgresql – How to use WHERE IN with psycopg?
I have a table like this: id topic_id 2 '1' 3 '1.1' ... ... 21 '4' and I would like to get a list of ids corresponding to their topic_ids. I can query the database directly just fine: SELECT id…