In my Postgres database I naively created a column in table Foos as a json[] that looks like this: TABLE Foos id | ... | baas -------------------------------- 1 | ... | [ | ... | { | ... | "name":…
I have a varchar field and want to remove rows which can cast only to date value 01.01.2024 1.2 1 0.6 11.11.2024 I tried this script, but it's work incorrect select * from values where value > '01.01.1970' Result: value…
I have two postgresql tables with the following data [teams] [id][team_name] 1 team 1 2 team 2 3 team 3 [matches] [participant 1][participant 2] 1 3 2 1 3 2 The matches table references the team ID. I am trying…
I want to select only those identifiers from a table which are contained in another subquery result and only top-2 rows must be used. For example you can run the query on PG: select a.rid from( values (41), (42), (43),…
Sorry for the bump, I am still searching for a method to omit rows based on specific conditions though. Is it even possible? I'm tinkering in a tool called Grafana with the aim of one day creating useful visualizations, tapping…
I need to track the sequence of sites visited by a user in their session. My data looks like this: visitor session_num site page_view_num timestamp User A 2 Site A 1 2024-03-22 11:41:10 User A 2 Site A 2 2024-03-22…
Using PostgreSQL/PostGIS, given a point position I need extract the nearest linestring from a db table and update the founded geometry adding the given point based on the point position. For these purposes the PostGIS function ST_AddPoint can be used,…
I am new to SQL and want to execute a query with joins. What I noticed is, when I have 2 rows in my table workers_send_times then I get doubled the same rows like start_time: 2024-01-01 end_time: 2024-01-02 start_time: 2024-01-01…
i'm using postgresql with spring boot and I have a json column defined like this ALTER TABLE core."foo" ADD COLUMN if not exists config json; the Foo entity is mapped like this import com.vladmihalcea.hibernate.type.json.JsonType; import org.hibernate.annotations.Type; ... @Type(JsonType.class) private Config…
Why do these two queries give different result in postgresql ? select '{"items":["foo", "bar"]}'::jsonb->'items'; select to_jsonb('{"items":["foo", "bar"]}'::text)->'items'; the first one returns: ["foo", "bar"] and the second one returns [NULL]