I have a table like below: city1 city2 dist New York Berlin 7900 Berlin New York 7900 Oregon Ohio 5700 Montreal Rio 5700 Ohio Oregon 5700 Rio Montreal 5700 Moscow Tokyo 4200 Tokyo Moscow 4200 Both row 1 and row…
I need to display the departure and arrival airport codes with their names from another table. But I don't know how to do this in one request select flight_no, a.departure_airport, b.airport_name from flights a join airports_data b on a.departure_airport =…
I have 2 tables: video_real device_id | video_definition 1 | 360 1 | 480 (2 rows) video_all device_id | video_definition 1 | 360 1 | 480 1 | 540 1 | 720 1 | 1080 I want to return device_id…
I have a table named member. I want to create a function that inserts a new email if it doesn't exist in the table, and then return a boolean. CREATE OR REPLACE FUNCTION add_member(member_email TEXT) RETURNS BOOLEAN AS $$ BEGIN…
Say I have two tables, Students and Scores: Students ID|Name|Gender 1|Ryan|M 2|Joanna|F 3|Shelly|F 4|James|M 5|Ron|M Scores ID|StudentID|Score 1|1|85 2|3|71 3|5|92 4|3|55 5|4|63 I want to get the names of all students, but I only want passing students for the boys.…
I have a query select * from table1 where column1=ANY(@some_array) @some_array - parameter that has array of values. It could contain null, which is correct according to my business logic. Unfortunately, query returns nothing if @some_array=[1, NULL] Documentation says: expression…
select sum(case when '%sys%' like any(series_arr) or 'displays' = any(lob_arr) then revenue else 0 end) as sys_displ_revenue from mytable Basically, when I run this query, the term '%sys%' like any(<array column>) seems to get ignored, and only the 'displays' revenue…
I am trying to formulate a query in PostgresSQL, my table looks like this: Master_Account Sub_Account Cost 123 777 15 123 888 10 123 999 20 456 111 10 456 222 -15 789 789 10 I am trying to select…
I have a Postgresql database with the following tables and relations (fiddle): page: id, name book: id, page.id chapter: id, page.id, book.id section: id, page.id, chapter.id I'd like to create a (materialized) view that is able to associate every page…
Question When working with SQL, I am familiar with preparing SQL statements, and then either committing them if nothing goes wrong, or bailing out if something does (which to my understanding leaves the database unchanged). I am now however in…