I have a query that can be seen on this fiddle and I’m relatively new to PSQL, having a few months of experience. DB Fiddle The link to the third party is to show the result of it otherwise would be difficult to visualize it here. This query collects and returns some metrics joining 3 tables about candidates being in
Tag: postgresql
Get distinct sets of rows for an INSERT executed in concurrent transactions
I am implementing a simple pessimistic locking mechanism using Postgres as a medium. The goal is that multiple instances of an application can simultaneously acquire locks on distinct sets of users. The app instances are not trying to lock specific users. Instead they will take any user locks they can get. Say, for example, we have three instances of the
How to rotate a two-column table?
This might be a novice question – I’m still learning. I’m on PostgreSQL 9.6 with the following query: My query returns the following dynamic rows: locales count en 10 fr 7 de 3 n additional locales (~300)… n-count I’m trying to rotate it so that locale values end up as columns with a single row, like this: en fr de
How to separate column values by condition (pivot) to fill one row
I have two tables that I’d like do a full outer join where the resulting view separates the values table into two separate columns with one row for each name_id. I have made one approach with a CASE expression to select by type and then use it with pandas to fill in the values and return distinct name_ids. Name Table
How to obtain row for minimum date for a value that is equal to the value in maximum date, per group?
I am trying to obtain row for a query.. where row value1, value2 in minimum end_date is equal to the max end_date, per group of id’s. My current query only obtains the row value1, value2 from the max end_date in the query result: This result obtains the most recent record, but I’m looking to obtain the row where value1 &
Execute same query multiple times with different parameter postgressql
I don’t know how to easily explain what i need to do but i’ll try. Lets say i have a user table with 5 users. instead of just do a select * from users i need to do this different and more difficult. I need to build a query that for each row in users table runs a query with
Full outer join not giving the answer I need
I am using PostgreSQL and am having difficulty with getting a series of queries that combine the data from two tables (t1, t2) t1 is studyida gender age a M 1 a M 2 a M 3 b F 4 b F 5 b F 6 c M 13 c M 14 c M 15 and t2 is studyida studyidb
How do I make an SQL query in Postgres that finds total balance and applies credit fees for the year?
I am totally new to Postgres and I cant find any example of what I am trying to do… I have a table of transactions for a year: amount | date Positive transactions for incoming money. Negative transactions for credit payments. And I need to return a total for the year that also reflects charging a $20 fee for every
How to overload the equality operator for primitive types (int, real…)?
Is it possible to overload the existing equality operator in PostgreSQL to give a new definition of equality between two values of type int or real? I think it’s against the rules of overloading but I wonder if there’s a way to do it anyway. I tried this: But when I use the equality operator in a query I don’t
When do Postgres transactions fail?
I was wondering at what point a transaction actually fails in Postgres. By this I mean stop working, and return an error. Is it after the first failed query? Does it try all queries and then return failure? Hopefully it fails fast on first failed query, but I don’t know for sure. For instance, if I have a transaction with