I am trying to use PostGIS to undertake a number of steps within an SQL statement to basically convert a raster to points, buffer, dissolve and then determine the overall boundary i.e. concave hull based on an attribute. The following is where I am at but get an error suggesting the use of a LATERAL FROM but …
Tag: postgresql
Two rows in two columns
I have table: Owner Book Date John Vile Bodies by Evelyn Waugh 2009-05-20 Tom Vile Bodies by Evelyn Waugh 2009-05-22 Jim Moab is my Washpot by Stephen Fry 2009-05-26 Kate Moab is my Washpot by Stephen Fry 2009-06-02 How can I get table: Owner Book Pick_Date Gave_Back_Book_Date John Vile Bodies by Evelyn Waugh…
Create a trigger that updates the columns of another table (Postgresql)
I have two tables “books” and “bookOrder” and they look something like this: bookOrder orderID book name required availability 1 Harry Potter 9 yes 2 Twilight 8 yes 3 Bible 8 yes books book name quantity Harry Potter 10 Twilight 5 Bible 8 I want to create a trigger that every time the …
Running query against all schemas in Postgres
Context We do schema-based multi-tenancy in our Postgres DB. Each and every schema is associated to a different tenant and will have the exact same structure except for one schema named public. To obtain the list of all the relevant schemas, we can use: Problem We need to periodically clean up a specific tabl…
Dynamic/Customized Week Number
Let us say there is a table which stores data for student’s attendance for classes. The student only need to attend each class once weekly. Hinging on the days_diff column, we decide which weeks the student should get credited for. As usual, each week has 7 days and the end of the week (in days) is divi…
Querying JSON in POSTGRESQL returns NONE everytime
jsonb column text_results contains values in the following form : {‘Name’ : ‘john doe’ , ‘id’ : ‘123’} On querying Select text_results->>’Name’ as Name from user_master or Select json_extract_path_text(text_results::json,’Name’) as …
Error with passing in the same variable into multiple parts of SQL statement
So I have a Node.js query that should pass in these three values and update a specific column by a specific amount for a user with a specific id: The ‘sqlStatement’ looks like: As you can see, it should pass in the values such that the SQL statement looks like: I’ve also tried to write sqlSt…
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 …
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=…