People of Stack Overflow! Thanks for taking the time to read this question. What I am trying to accomplish is to pivot some data all from just one table. The original table has multiple datetime entries of specific events (e.g. when the customer was added add_time and when the customer was lost lost_time). Th…
Tag: postgresql
How to iterate rows with known start position and mixed column ordering, exactly as if walking the corresponding index?
I have a given, unmodifiable table design which resembles: I simply want to iterate rows from this table in the order c1 asc, c2 desc, starting at a known position, e.g., (‘foo’, 5). The query must be efficient, i.e. the query planner must be able to take advantage of the unique index and do an in…
How to select column 1 value if exists else column 2 value in PostgresQL?
I want to merge two columns in PostgresQL query by the following rule: select (column_1 or column_2) as column_3 from my_table Is there a way to achieve it? Though quite clear, I want to prefer column_1 value as column_3 but if it is null, I would like column_2 value as column_3. Sorry if this sounds naive, t…
Updating a specified row on a table with information from another table using a trigger and function on PostgreSQL
I have a database for a petshop for class that contains the table Animals with IDs and the dates of the last consult requisition for each animal, amongst other irrelevant columns and the table Requisition which contains the animals’ IDs and requisition dates. And I need to create a trigger that will upd…
What is wrong with my stored procedure SQL query on PostgreSQL?
I tried many times with or without the semicolon but still shows me the error message. ERROR: syntax error at or near “INSERT” LINE 13: INSERT INTO Advertisement(ad_id, business_id, requested_on… ^ SQL state: 42601 Character: 712 I am sure the table constraint and sequence are all correct bu…
SQL subqueries PostgreSQL 12
I’m having a table similar to this: first last date pos john doe 18-03-2021 harris potter 10-06-2021 john doe 10-05-2021 harris potter 14-06-2021 jessica potter 14-06-2021 kermit foster The use case is as follow: The pos column correspond to a positive covid test The date column correspond to the vaccin…
PostgreSQL – Find number of installs each day given a table of installs and uninstalls
I have a table for machine installations in this way: I need a query that returns the number of installed machines per day. Like this: I know that given a date, say ‘2020-01-03’, I can get the number of installed machines as follows: However, I do not know how to query in such a way I can get the …
Convert list of dictionaries into dictionary in Postgres jsonb
I have an actions_table looking like: I would like to aggregate by profile_id and jsonify as: The closest I could get to is: By means of the query: How do I get to my desired result from here (convert list of dicts into dict), or how do I fix my original query? CAVEATS I need a general answer, I might
Generate dates in-between date and date after
Based on the table below I want to generate the dates in between, by dividing the revenue by the number of days from the date in the current row and the row below. Example: Revenue in US on 1 January 2020 is 120. The next revenue date for the US is 5 January 2020. There are 4 days in betweeen.
disaggregate 3d time periods into daily
table_1 shows the aggregation of the revenue by different geographies I would like to disaggregate the 3d period to daily: table_2 as a next step, I would like to also change the table_2 format into table_3: From table_1 to table_2, I am not sure. From table_2 to table_3, I am thinking of: With the code above…