I’m using postgreSQL version 10.3 and I have a table like this: I want to make numerous INSERT and make sure there are no duplicates. So I thought about using INSERT INTO … ON CONFLICT DO NOTHING;. The idea is to insert the record only if the values on the columns first | second | third | fourth |…
Tag: postgresql
Rails/PostgreSQL – Query to update all dates adding 1 day
I want to execute an update_all query to update a datetime column adding 1 day to the current value of this column on all rows. I could achieve that using .each and .update, but it would be more …
“sql: no rows in result set”
I am handling user auth data posted to my Go backend through an HTML form. I am building on some boilerplate to learn Go better. My problem is what the following func returns: func (ctrl …
Calculate Profit Based on First-In, First-Out Pricing By Date Of Sale
How can I find the sales margin every Day via SQL, assuming they are sold in the order they were purchased? Answer Please try this solution – group by sale_date
PostgreSQL json_array_elements with array indexes (keys)
Simple query works fine: But I also want to retrieve array keys somehow so the output would be like: UPD Seems like row_number() is a p̶r̶o̶p̶e̶r̶ solution, but I cannot figure out how to use it further. Lets say i have ‘posts’ table, each post contains an array of related comments in JSON format:…
Window functions filter through current row
This is a follow-up to this question, where my query was improved to use window functions instead of aggregates inside a LATERAL join. While the query is now much faster, I’ve found that the results are not correct. I need to perform computations on x year trailing time frames. For example, price_to_max…
Can we define a GROUP_CONCAT function in PostgreSQL?
I have several lines of SQL code for my legacy database with GROUP_CONCAT statements, as in: In PostgreSQL, I can do the same with: I would like to reuse the old SQL as much as possible. So I need to define a GROUP_CONCAT function that internally calls string_agg. Is this possible? EDIT: The linked question i…
Finding a single element in postgres json array
I have a table that has a Json typed column and I am trying query that table based of values that are contained in that json column. Here is the relevant DDL: Here is what a roles value would look like: I want to be able to perform a query like this: So I’ve tried this: And it is giving
PostgreSQL: append elements to jsonb array only if element is unique
I created a PostgreSQL (v10.0) table with a jsonb-array column as follows: Then I want add new animals to the first row as follows: However, I want to append only those elements that are not yet in the array. In this case only [chicken, 2]. Answer probably simplest would be:
SQL: Split time interval into intersects of one hour
I am new to SQL, where I am trying to split time intervals into multiple rows of one hour, together with an indicator of how many minutes the interval overlaps each hour. My data: What I would like: What would be the easiest/fastest way to do this? Answer Try something like this: demo Result for sample data