I have table similar this one instance value type ins_1 31 “A” ins_1 81 “B” ins_2 72 “A” ins_3 9 “B” ins_3 9 “C” … and I need select only instance(s) which has double type (A,B). The expected result will be: [“ins1”]. Answer The typ…
Tag: postgresql
Insert date now + interval 2 days
for some reason I am getting a syntax error: From what I’ve read this should be the correct syntax. Answer The interval value needs to be enclosed in single quotes: The specification ‘2 days’ as a single string is a Postgres extension to the SQL standard. A SQL standard compliant way of writ…
PostgreSQL query get all entries with many-to-many relationship with another table
I have a table posts which has a many-to-many relationship with tags, the pivot table is called posts-tags. I want to be able to retrieve all posts by a list of tag id’s. Imagine With tag id’s [1, 2, 3], I should get back [{id: 1, text: “foo”}] With tag id’s [1], I should get bac…
Get distinct members for an array of group IDs
I have 3 tables: Scorecard Group Scorecard_Group (Joint table) I am trying to write a query that fulfills the following purpose: Get the list of distinct scorecard ids WHERE Scorecard_Group.groupId IN (Pass array of groupIds) Get all Scorecard.”name” where Scorecard.”id” IN (Array of s…
PostgreSQL – Adding up json values
This is my json column in pgSQL and I need to add up all of the “sum” amounts using pgSQL. So the query should return “50” using the data below. I did find something like this (Get aggregate sum of json array in Postgres NOSQL json data) that kinda works if I had my values in array, bu…
Implement Scan and Value functions in golang
I am trying to store some golang objects in SQL database and have implemented the scanner and value interface as follows: Is there a way that I can pass the parameter to the Value() function by pointers, as I am getting a HugeParam error that the attr passed to the Value() function is too big, when trying to …
How to use column derived from SELECT subquery elsewhere in SQL query
I have two database tables that look something like this: Context: Booking_Statuses is used to retain a history of the various stages that a booking can go through, such as PENDING or ACCEPTED, so I can construct a timeline of events for the booking. One Booking has many Booking_Statuses. I’m using Post…
Get all the names were total purchases are >70% of all the purchases per client
I have information about places and purchases in a table, and I need to find the name of all the places where, for all the clients who purchased in that place, the total of their purchases is at least 70%. I’ve already found the answer on python, I’ve sum the number of purchases per client, then t…
Combine multiple selects for statistics generation into on result set
Hey Pros, I am far away to have good knowledge about SQL, and would ask you to give me some hints. Currently we aggregate our data with python and I would try to switch this when possible to. (SQL (Postgresql server) My goal is to have one statment that generate an average for two seperates column’s for…
JdbcTemplate SELECT query to get unique rows (each row with unique ID is present once)
Anyone willing to help me out with one jdbctemplate query? Need to get only rows with unique id’s but there are duplicates, because other columns, such as date have different values. I need to get the max date preferably and the resultset should not have any duplicates. 😡 output: This almost works, but …