I have a table in Postgres that looks something like this: The algorithm is if any single record has reversed = true or status = foo, all records with the same group_id should be filtered. If group_id is null, which is possible, I’d like to keep those records regardless of reversed or status The closest…
Tag: postgresql
SQL iterate UPDATE from python list
Do you guys know how to UPDATE a database from a python list without using cur.executemany? Using cur.executemany I have problems in API calls or something. What is another way to solve this below? Thank you! I know below is crazy ugly, but I did that. Answer Not an answer but I don’t want to edit your …
how to find consecutive user login across week
I’m fairly new to SQL & maybe the complexity level for this report is above my pay grade I need help to figure out the list of users who are logging to the app consecutively every week in the time period chosen(this logic eventually needs to be extended to a month, quarter & year ultimately but …
difference between use of NOT in SQL
Is there a difference between: SELECT …. WHERE id NOT IN ( … some values … ) and SELECT …. WHERE NOT id IN ( … some values … ) I had problems with the first one if id had null values. In this case the rows with the null values were not considered in the result set but if
Subtraction of two sums grouped by asset type?
I have a table, like this: I need to sum all amounts which type is Credit and all amounts which type is Debit for user_id = 1 grouping by asset_type respectively. And then subtract sum of debits from sum of credits for each asset_type. E.g. For example table and user_id = 1 it would be: 100 usd (no debits for
How to create empty temporary table?
Need to create empty temporary table, but that not depends any query result yet, but I need only structure. (then, according to some logic, will be different data inserted into this). throws syntax error So is this way (which looks bit “tricky”) only correct way: ? Answer As documented in the manu…
How to declare the elements of an array non-nullable?
In the following simple table It’s possible to insert null as an element of things: But I’d like to not allow this. Changing the definition to the following, however, Only prevents this which is not what I want. (I still want to allow that.) So how can I declare not the column, but the elements of…
Order by match on multiple columns (and with mutiple conditions)
My issue is I want to order by match on a specific search string. This is the logic: If the title, author or isbn are the same as search, show these books first If the title, author or isbn are the like %search, show these books second If the title, author or isbn are the like search%, show these books
How to select by two fields using IN operator
I tried query like SELECT id FROM events WHERE iid = 2 AND target_id = 3 OR iid = 3 AND target_id = 5 OR… How to short this? Also tried to use IN operator but it get wrong logic SELECT id FROM …
Combining data from one column into one with multiplication
I am trying to find a way to add up amount with the same ID and different units but also perform multiplication or division on them before adding them together. The column time describes the amount of time spent doing a certain task. There are four different values the time column can have which are: – …