I’m trying to create a many-to-many relationship between two tables: ‘accounts’ and ‘transactions’, but with a catch. Every transaction must be related to two accounts, but each of those accounts must be specified as either the sender or receiver. Likewise, each account should relate to all the transactions they are involved in. Is there a best practice database design pattern
Tag: postgresql
Postgres add identity results in ERROR START value (0) cannot be less than MINVALUE (1)
Tried to run this because I want the auto generated IDs to start at 0 and got error: START value (0) cannot be less than MINVALUE (1) I’m not good at PostgreSQL yet so not sure how to get it to increment starting at 0. Answer All Identity columns create a sequence in the backend. In your sequence_option of your
PostgreSQL / express rookie – create new table or just a new column in existing table? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question This will probably get closed for being opinion based but hopefully I can get a comment or two. I’m building a social media app (Express/Postgres/React)
SQL – return rows in partition based on max value
I have below dataset with comments which row must be returned. Logic is: return value for max kg_to within partition of each country. Current working code: Question: Shorter code would be better, any ideas on how to improve it? Answer Use distinct on: Or window functions: Note: I also don’t see how your code could be retrieving duplicates, unless you
PostgreSQL group by column with aggregate
I need to group by id and select the task with min/max seq as start and end But this results in But I do not want group by seq Answer One method uses arrays: Another method uses window functions with SELECT DISTINCT:
Select from many to many relations table
Given these tables: I would like to query scenarios with all the connected suites. results: **scenario** **suites** loginScenario loginSignup, endToEnd, smoke addToCart …
How to escape several with in the query?
I need to refactor my query, but I have no idea, how to do this. I can see several duplicates of using the same logic, but I continue to repeat this manner of querying from query to query, and I feel, that this kind of querying becomes my main frame of thinking of SQL and I don’t want this. Could
Using update inside of PL/pgSQL
To begin with, I am a newbie to SQL and PostgreSQL. It might be a silly beginner’s mistake. ^This isn’t working provides me with an error SQL Error [42601]: ERROR: syntax error at or near “update” Where: PL/pgSQL function temporary_function_for_getting(text,bigint,json) line 13 at RETURN QUERY . The update query works fine by itself not sure what i am doing wrong.
Selecting single row if it matches another
I have a scenario of this type: I want to select only one row in such a scenario. How can I achieve this? Answer You could use distinct on with least() and greatest(): An alternative is not exists: Finally: if all of the rows are duplicated, then a simple where condition is sufficient:
How can i find all linked rows by array values in postgres?
i have a table like this: I want to find out which rows are in a group together. In this example 1,2,4 are one group because 1 and 2 have a common element and 2 and 4. 3 and 5 form a group because they have a common element. 6 Has no common elments with anybody else. So it forms