Count consecutive ocurrences SQL – PostgreSQL
I am trying to count the number of consecutive weeks an employee went to work. So I have this table that has whether jon or andy went to work on certain weeks (I have all week of the year). I…
Postgresql – How can I find a president whose wife is older than 30 and occured only once (no duplication in pres_id)
original question: Determine, for all presidents who married only woman older than 30, id and the sum of children number in all their marriages? issue: The issue is that the pres_id 44 has three columns with 3 different presidents and…
Postgresql – Add jsonb property of type boolean
I'm trying to add a property to an existing jsonb column (column "data"). I want have my jsonb document to log like this { // ... existing properties "Filed": false // new property } I tried UPDATE "doc" SET "data"…
How does postgresql cast float to numeric?
I was wondering how Postgresql converts floating point (float4) values to NUMERIC. I chose 0.1 as a testing value. This value is not accurately representable in base2, see https://float.exposed/0x3dcccccd for a visualization. So the stored value 0x3dcccccd in hex for…
Postgresql – flyway version migration issues from 6.3.1 to 8.5.x using docker container
Team, We have working flyway docker image (flyway community edition version 6.x) setup for postgresql 11.x version working. After upgrading our postgresql version to 14.x and now the existing flyway migration scripts (with updated community edition version 8.x) were failing…
Postgresql – Use Query result as element of another query
I have this two queries, SELECT ST_AsText(geom) FROM areasTable WHERE "Name" ILIKE 'Kachina'; Let's say that it returns a polygon value of: POLYGON((-XXX.XX XX.XXX, -XXX.XX XX.XXX, -XXX.XX XX.XXX, -XXX.XX XX.XXX)). I then use that value to do another search. SELECT…
Postgresql – How do you build a jsonpath query referencing a column in Postgres?
This works fine: jsonb_path_query(p.blah::jsonb, '$[*] ? (@.fruit == "banana") ') but if I want to compare the JSON "fruit" property to a value from a column, I cannot get Postgres to parse it, like so: jsonb_path_query(p.blah::jsonb, '$[*] ? (@.fruit ==…
Low performance of Common Table Expressions in PostgreSQL V14
I've recently switched from V11 to V14 of PostgreSQL, and some of my CTE queries, that used to take a few milliseconds, now takes a few seconds. The minimum query to reproduce this issue is the following : WITH MyTable…
Postgresql – Total distance for points in same table sql
I do have the following three tables within postgres ShipmentTrip id,shipment_id, type,status,lat,long 1, 1, pickup, whatever, 25, 75 2, 1, dropoff, whatever, 27, 76 3, 2, pickup, whatever, 25, 75 4, 2, dropoff, whatever, 27, 76 Shipment id,...,driver_id Driver id…
Postgresql – Getting error in postgres query No operator matches the given name and argument types
Reason why code is causing error when groupHds is null? I'm getting this error, groupHds is list of string and ddi.group_hd is string column. Error: Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea Hint: No operator…