Here is my use case. I am using Postgres 13.3 I have a people table under a draft schema that has multiple columns.. email and created_at. The email should have been unique but it was not. If I alter the table to be unique, there were duplicate emails inserted before and I don’t want to lose the data by…
Tag: postgresql
Go SQL, scanning a row as a slice?
closing note: This question does not ask “how can I find the number of columns returned?” which is len(row.Columns()). It is asking a Go-related question concerning the rows.Scan() function. How can I extract all the columns in a row into a slice or equivalent? For example I would like to extract …
sql sales data grouped by year in separate columns in postgresql
I have two input tables: analysis (an_id, an_name, an_cost, an_price, an_group) orders (ord_id, ord_datetime, ord_an) # orders of analysis (sales) For every analysis_id I need to show the amount of orders for years 2020 and 2019. Expected output: an year2019 year2020 1 32 41 2 29 28 3 42 37 4 26 35 5 32 34 lo…
Foreign key relationship with boolean values
I’m working on a feature. I’ve three different car types (Sedan, Hatchback, SUV): I’ve 6 features in total. Out of 6, 4 features are their in every car. The second car category has 5 and the third has all the 6 features. Here where I’m getting stuck: I’ve to send all the 6 featur…
How to get data for administrative weeks of a month?
What I want to do is to get the data of each “administrative” week (Monday to Sunday), that is if a month starts a Wednesday,the next week when I would launch the query, I would want the data from Wednesday, Thursday, Friday, Saturday, Sunday of the past week. This would get me data of week 1, and…
Add Column to Result of Postgres Dynamic Query
I have a table that looks like this: I rearranged it to this: Right now, the order of the systems is alphabetical but I created a mapping table in which it orders the priority of the systems Is there a way to adjust the order such that it reflects the priority and is as such: Answer You may find the
Order and limit data based on JSONB array
I have such a PostgreSQL table Now I would like to get all sorted val and have the limitation for each query (for pagination). For example 1st query 2nd query Answer You need to unnest the array, sort the result then apply offset and limit: Instead of limit 5 you can also use the standard compliant fetch firs…
How can I obtain the primary key for a row associated with the minimum value in a subset specified in a group by?
I have been working on some database queries and am running into some issues with trying to obtain a primary key for a row where an aggregated value was obtained. I am using Postgresql v10. Take for example the following table: instructor_schedule_id instructor_name course_name start_date 1 bob databases 2015…
SUBSELECT (SQL)
I have to tables. The first table (customers) shows data about the customers (id, firstname, lastname). The second table shows data about the orders (timestamp and customer id). So, i wanted to see the last order by a customer in my table “customers” and did a SUBSELECT and it worked. But, there a…
Getting the outer range of a grouped column of range types in Postgres
I have a table with a column name consisting of text values that I want to group by. Additionally I have a table value consisting of numerical range values (of range type). I want to group by the name column and get the outer bounds for each of the groups. Here is an example: Parameter table Wanted output is …