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 I do not
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 2009-05-20 2009-05-22 Tom Vile Bodies by Evelyn
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 books table is updated, it will update
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 table for all the tenants: (The actual
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 divisible by 7. There
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 name from user_master it always return None without any error even though values are present. Need help with rectifying the error or any other way of extracting values from json
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 sqlStatement as the below (and
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: