I have 2 tables which I want to join and return only those records which: Have the same value with other records in one of the columns. Have certain values in other column. These are my requests: List only those records which have the same value with other records in grnz column: List only those records which…
Tag: postgresql
SQL subquery to find overlapping customers
I’m working on writing a SQL query that would allow me to find customers who bought a certain variety of a product during specific sale dates and then subsequently bought a different product within a different date range. I want to use a subquery in the from clause to search for the second group of cust…
How to loop JSON array in postgres and access keys of the JSON?
I have a custom json with several key-value pairs. I want to loop over the length of the array, and access each of their keys, and insert into the table. The problem I am facing is during loop the query is unable to access the value. Answer the loop over jsonb array will look like this
Total value of warehouse active record
I have the object Tool and I need to calculate the cost of all the Tools in the warehouse. I can sum the column price Tool.sum(:price) but i need to sum (quantity * price) of all the table Tools. Thanks in advance. Answer Check out the example listed for ActiveRecord::Calculations#calculate. You can do the fo…
PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert
Table def: I have a function that inserts new rows into the database: It fails with the index Although running the relevant insert on its own with duplicate values it has no problem with it. I can’t understand what seems to be the the difference between the function and the single statement? This also w…
How to reset a session in a sql connection pool?
i have a connection pool using javax.sql.DataSource and PostgreSQL JDBC. The process is simple, I’m taking a virtual connection from connection pool, process it, then close the virtual connection (…
Postgres SQL attribution over time range
I’m using Postgres 11 and trying to figure out if I can do this in a single query. Suppose I have two tables: When I join these tables by uuid, Event2 rows should be attributed to a particular Event1.id up to when a newer Event1.id is active. For example, if I have these rows: Event1: Event2: I would ge…
How to get a continuous group of id
I’d like to increment an id by group, for each variation of an attribute with only 2 values. like this : result wanted I tried dense_rank() with window function but i only gives me 1 or 2 in grp column Answer You can use window functions to solve this gaps and islands problem. Her is an approch using la…
Using like or ilike to create SQL/ PLpgSQL function
I am trying to create function Animals (for convenience purpose) that takes first letter of an animal name and returns the whole name of the animal from the list e.g. C=> Cow, Cat… The problem I am facing is that the string after the like function is being treated as a string (I want it to be input).…
How to get data from Json to multiple column PostgreSQL
I want to extract many objects (not all object) in JSON but don’t want to type for every single thing like this: Here my DEMO show real data and case, how to extract to multiple columns and cast it to exact data type (float, integer) I refer this question, can I use json_to_record for this or other meth…