I have a table with name and grades for some users CREATE TABLE grades (name varchar(100), grade integer); insert into grades values (‘Bob’, 12), (‘Bob’, 13), (‘Bob’, 23), (‘Bob’, 17), (‘James’, 15), (…
Tag: postgresql
Best way to restoring Audit table without existing primary keys – postgres
Table A has an audit table that I need to restore a specific column from. Rows were deleted from table A, I then regenerated them and via matching on specific times (these are appointment records) I’…
Including a column twice in a PostgreSQL index
I’m building a PostgreSQL based work-queue as described here. My queue definition differs slightly from the concept described in the article by using a target timestamp for consumption (due_time): …
Why does the DBMS say that the primary key is not present in the table
I am a CS student that is taking his first course in databases so here I am using Postgresql to create a database with the following tables: employee, project, and worksOn. So if you run the program you can see that both the employee and project table are created. However, worksOn is not because it gives me the following error:
Capture values from a table and declare as a local variable in SQL Function
I’m learning how to write functions in SQL and this might be simple but can’t seem to find what I’m looking for. I have a function as follows: CREATE OR REPLACE FUNCTION A.aggregate(r_id text) …
Accessing array elements in query postgres
I’m currently building a query to retrieve some data from my db, I need to access the documents stored in an array, this is how the table crm_company looks: I have another table for users by document crm_user which looks like this: What I want as a final result is: I just have tried somehing like the following: But I’m
Select first and nth order for each client
I’m trying to display first and fifth order_id for each user. Table looks like this: I wrote something like this: But I want to see clients who made their first orders but don’t have fifths. There should be NULL, but I can’t understand how to make it. :c Answer Put the where t2.rn = 5 condition into the left join:
Joining table which may not exist with where clause
I have two tables. Let’s say they are: I’d like to make a join to get customer name, number of orders which he/she has finished and sum of money which he/she has spent in total So I’m making this SQL query: If a customer has any finished orders then it will get everything right. But if there are no orders
SQL – Merge two rows with similar values
I have the following table called areas in a PostgreSQL server: So basically it’s nearly duplicate entries that have the same name and code, but different id, shape and created timestamp. What I want is to merge those duplicate rows, but overwrite the “shape” and “created” fields with the values of the newest entry. So eventually the above table would
how to find the difference of median price of a municipality from the current month and the next one?
How to get the absolute difference between the median house price for tat month in that municipality and the median house price for the next month in that municipality? I have a table with house data. it contains the price and municipality for each house and date it got sold. I want to get the difference of the median price