Assume a table json_table with a column data (jsonb). A sample value would be {“a”: [{“b”:{“c”: “xxx”, “d”: 1}},{“b”:{“c”: “xxx”, “d”: 2}}]} I used to run SQL queries like the following: SELECT …
Tag: postgresql
How to compute duration between two times in postgresql when the end time is sometimes past midnight
I have to compute the duration of events in postgres based on start_time and end_time, both are in the HH:MM:SS format. Simply end_time – start_time works: create table test_date as select sum(…
SELECT on JSON operations of Postgres array column?
I have a column of type jsonb[] (a Postgres array of jsonb objects) and I’d like to perform a SELECT on rows where a criteria is met on at least one of the objects. Something like: — Schema would be …
Finding revenue based on shared ID’s, SQL
I barely know how to ask this question or if this is possible in SQL. I work for a sales company and reps will frequently share accounts with eachother under “Split Codes”. I have a table that …
Slow query when joining with a materialized view
I’m trying to diagnose a slow query I have against a materialized view in Postgresql (10.12). In brief, I have two tables activity and task plus a materialized view, activity_status_view that is …
Exclude blank columns from XML
I am trying to get Table Rows to XMLs. I am able to do it in the following way and the only issue I have is that it also generates XML tags for blank columns. create table test_niks_01(x int, y …
Is it possible to create an ENUM of smallints in postgres 11.x?
I’d like to create an enum TYPE to constrain a pl/pgsql function to accept an argument that’s 1, 2, or 3. Enum types seemed like the way to go about this. I’ve tried creating my enum as below: Answer According to the PostgreSQL documentation, an ENUM type is always going to be a fixed value: An enum value occupies four
Get the row where the sum of a value matches a condition
I have a table with the columns: date (timestamp) num (integer) Looks like this in CSV: I want different queries to figure out the following: A: The earliest date that the sum of num is >= 1 B: The earliest date that the sum of num is >= 2 In the sample data the output would be A: 2018-02-07 and
Join one table with two other ones by id
I am trying to join one table with two others that are unrelated to each other but are linked to the first one by an id I have the following tables Then I inserted the following data Obs: The admin does not has an invite The group has an approved invitation with status 2 (because the member ‘other’ joined) The
Postgresql: UPSERT / INSERT INTO defining a conflict
this is my first post on the platform. I hope this image explains what I am trying to do. I am trying to merge table B into table A. If the condition is met that (A.cell = B.cell AND A.object = B.objet) then I want to update A.cost with the corresponding cost from table B. If the condition is not