I have a table named t1 and it has 3 columns c1,c2,c3.In t1 there are rows which have duplicate values of c1 but different values of c2 and c3.Now, I wanted to create a psql view table which checks for duplicate c1 and updates the row where it takes the c2 value from the row which has highest value of
Tag: postgresql
get name of table in function
I need get name of table inside function: SELECT f(name) FROM table; how can i get table’s name (table) in function f?
Calculate account balance history in PostgreSQL
I am trying to get a balance history on the account using SQL. My table in PostgreSQL looks like this: so the user with id number 2 currently has 40 dollars in his account. I would like to get this result using sql: Is it possible to do something like this using sql in postgres? Answer To get a rolling
Pivot data row to header using SQL in Postgresql
I get this result using SQL request: NBR_TYPE Count ———————– 0021 20 0022 19 0023 13 But i want result like this : 0021 …
Two composite foreign keys with one column in common
I am trying to map the following ER Model into PostgreSQL and I am having trouble with the Issue_Label table. It requires the key from the Issue table, which is id and the key from the Label table, …
PostgreSQL 11 – using format to assign to variable
I have been awake for well beyond my schedule and I have been stuck with this issue for a long time, I don’t even know what I am looking for to solve, but I wish to use format to insert values that I’ll be using for column names, and then executing it… but it keeps giving me errors no matter
How do I make an aggregate on an integer with a grouped column, for which I only want some included?
I have a table prices holding all prices that some products have had: The first entity for a product_id is it’s initial sales price. If the product is then reduced, a new entity will be added. I would like to find the mean and total price change per day across all products. This is some sample data: The…
Postgres Like to Full Text Search
I attempting to create a query to find the name other in my sql database. I have a basic like search as follow and would like to use a full text search instead. Like Query SELECT g.*, COUNT(*) OVER(…
Limit the random value of timestamp from january to yesterday date
How can i limit the randomize timestamp on my query from 2020-01-01 up to this date or the date of yesterday. This is my current code that i’m experimenting and trying to use and play around but it keeps inserting data with a year 2019.. Answer This expression will produce a random date/time between Jan…
PostgreSQL11 xpath query not working properly
When I execute below query in Postgres 10.12, it works properly. Output: But When I execute same query in Postgres 11.7, it is not working. What is the solution to fix this issue? Answer This is caused by this change: Correctly handle relative path expressions in xmltable(), xpath(), and other XML-handling fu…