I’ve the following table in postgresql id product customer price amount 1 Television Alfred 12.00 1 2 Radio Richard 20.00 3 3 Mobile Richard 40.00 5 Is there some way via SQL to duplicate the line n-times in case the amount is greater than one? This is how the table should look like after running the query: id product customer
Tag: postgresql
Sequelize raw query update array of objects as replacements
I am using sequelize (postgres) and I need to properly escape a query like this: Sample input.pets: Does anyone have an idea how to achieve this with replacements? I have found a thread on github which suggested something like this: However, a 2d array is being used here not an array of objects. Is there a way to access individual
Postgres, get all items from one table which ids are not in another tables JSON array column?
I have two tables: cars table which contains: electric_cars How to select all records from the cars table which are not in the non_valid_cars array of ids in the electric_cars column with id cars_id? Also, I am using Laravel Framework, but I will translate a query into the framework. Thank you so much for your help. Answer You can use
postgresql – condition met in a time interval
I have the following table and I want to create the condition_met column. The condition_met column is my expected output. Condition_met is TRUE when type_id = 34 and within 5 seconds after this type_id, the client_id of type_id = 34 becomes also prospect_id. To say it differently: for each type_id = 34 the client_id that performed action type_id = 34
Calculating win streak from list of fixtures
I have a table fixtures with a list of matches played between two players in each, identified by their ID. I’m trying to use the table to calculate rolling winning/losing streaks for each player, but I’ve been having issues with my query below (see more on SQL Fiddle): Below is the desired result, with records partitioned by player_id and ordered
How to select cumulative counts by group over time across in postgres
I have cumulative counts for two groups over time in this format: Date Group Cumulative Count 1/1/2020 A 1 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 B 2 And I’d like to reshape this data into this format: Date Group Cumulative Count 1/1/2020 A 1 1/1/2020 B 0 1/2/2020 A 3 1/2/2020 B 1 1/3/2020 A 3 1/3/2020 B 2
How to transform a postgresql select with join to a json object?
I want to transform the result from a select with joins into a json object. I mean this query: should output this: (there are more fields than these. I’m just making the example simple) I found a solution this way: But I think there should be a much better way to do this. Imagine that chat_messages had a lot more
Foreign key constraint : alternative for one to one relation with split tables
For tables like this: Book AudioBook PaperBook Is there other way to ensure that PaperBook and AudioBook will not have same book_id without type column on them (remove FK constraint book_id + type references Book and type on childs? Example without fk constraint where AudioBook and PaperBook can have book_id=1 at the same time: Book AudioBook PaperBook Answer approach one:
Changing a column value to several records in a consecutive fashion using clever statements in SQL
I have a Postgres table that has an order view field in her, I’m using Nodejs with express This order view is of type INTEGER and has the constraints of NOT NULL and UNIQUE Inserting a new record may bring problems with that, because, if the existing records have view orders of, say, [1, 2, 3, 4, 5], and the
How to insert result of select query (with default if return no rows) as part of Postgres Function?
For my Nextjs website, I am configuring the backend to automatically add user information to a public.profiles table whenever a row is inserted into the auth.users table (automatically done through Supabase’s authentication). I have a public.college_emails table that contains the names of over 1700 colleges and the email tag associated with them. When a user creates an account with their