How can I capture different columns into different variables like so (note this is only pseudocode so I am assuming it will cause errors. Example taken from here) create or replace function get_film ( …
Tag: postgresql
select linked rows in the same table
I’m creating a branching dialog game, and used a dialog tool that outputs JSON with a link and a link_path to connect dialogs together. I’ve parsed and inserted this structure in PostgreSQL. I want to …
Find first available value that doesn’t exist
I want to create table for book chapters where pk will be book_id and chapter_internal_number. I’m not sure how find next free chapter_internal_number value for new chapter insert (chapter can be deleted and it’s chapter_internal_number value should be reused). How to find first chapter_internal_n…
How to properly store a file location to a postgresql table?
I am very new to postgresql. I don’t plan to study it fully in-depth right now as I have other priority topics to finish. But now I need to know some basics of postgresql for the perfect completion of …
Postgres join and count multiple relational tables
I want to join the 2 tables to the first table and group by a vendor name. I have three tables listed below. Vendors Table VendorOrders Table OrdersIssues Table The expected output is to count how many orders belong to a vendor and how many issues belongs to a vendor order. I have the below code but it’…
Generating counts of open tickets over time, given opened and closed dates
I have a set of data for some tickets, with datetime of when they were opened and closed (or NULL if they are still open). We would like to generate a table of data showing the total count of tickets that were open through time, grouped by date. Something like the following: Note that I am not sure about how
Counting Business Days PostgresSQL
I am attempting to do something like this: SELECT t.id, t.startdate, t.enddate, FOO FROM table Where “Foo” is some sql mumbo-jumbo to calculate the business days between startdate and …
Postgres Query to retrieve rows which has all the flag value as false
I have User to Role table with Many to Many relation. I need to retrieve all the roles which are not privileged(false) to any users. select distinct r.rolename,ur.privileged from pam.”role” …
PostgreSQL: sum of days in range
I have a table as follows: Table “public.fish_schedule” Column | Type | Collation | Nullable | Default ——–+————————+———–+—…
SQL Query for max value from subsets
I have a table bls_jobs with the following columns: city, state, occ_title, jobs_1000, and loc_quotient I am trying to retrieve the highest loc_quotient for each city (each city has several occ_titles, and each occ_title has a loc_quotient) Currently, I can use this query: Which does return what I’m loo…