I am getting an Error on this even after adding an alias.... why?

Question

SELECT * FROM 

(select 
flight_id,
flight_no,
scheduled_departure,
scheduled_arrival,
cast (scheduled_departure as text) TIMINGS
from bookings.flights), as TABLE1

case when TIMINGS
between 02:00:00+05:30 to 06:00:00+05:30 then 'Early morning flights'
when scheduled_departure to scheduled_arrival 

between 06:00:00+05:30  to 11:00:00+05:30 then 'Morning flights'
when scheduled_departure to scheduled_arrival 

between 11:00:00+05:30 to 16:00:00+05:30 then 'Noon flights'
when scheduled_departure to scheduled_arrival 

between 16:00:00+05:30 to 19:00:00+05:30 then 'Evening flights'
when scheduled_departure to scheduled_arrival 

between 19:00:00+05:30 to 23:00:00+05:30 then 'Night flights'
when scheduled_departure to scheduled_arrival 

between 23:00:00+05:30 to 02:00:00+05;30  then 'Night flights'
END as TIMINGS

This is the ERROR

ERROR:  subquery in FROM must have an alias
LINE 1: SELECT * FROM (select 
                      ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
SQL state: 42601
Character: 15

Answer 1

You can get what you are looking for without CASE at all.
One thing to realize is when working with dates/timestamps converting to text is virtually always unnecessary and incorrect (as is the case here) basically just do not do it. Use the Postgres date functions.

The following gives what you are wanting. It begins creating a CTE to define time periods and a descriptive name for each period. It then JOINS that CTE with the flight table. (See demo here)

with departures( stime, etime, description)  as 
     (values ('00:00:00'::time, '02:00:00'::time, 'Night flight') 
           , ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
           , ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
           , ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
           , ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
           , ('19:00:00'::time, '24:00:00'::time, 'Night flight')
     )
select f.flight_id                                        "Flight Id" 
     , (f.departure at time zone 'Asia/Kolkata')::time(0) "Schedule Departure Time" 
     , d.description                                      "Description" 
   from departures d
   join flights    f 
     on (   (f.departure at time zone 'Asia/Kolkata')::time(0) >= d.stime 
        and (f.departure at time zone 'Asia/Kolkata')::time(0) <  d.etime 
        )
 order by  "Schedule Departure Time";

Notes:

• Timestamps always include a date and fractional seconds. Since you want just time comparisons both need to be discarded. This is accomplished with ::time(0). It does, however, round to the nearest second.
• I changed your definition for 'Night Flight'. In a timestamp, after discarding the date the resulting time,23:00:00 will always be greater than 02:00:00. The change corrects for this.
• I used at time zone 'Asia/Kolkata' to get your +3.5 hour offset from UTC (am at UTC-5). Depending on your server time zone setting you may not require this.

I am a newbie so bear with me if the format is not structured. so I got the expected output after writing this code...

'''
with flights ( stime, etime, description)  as 
     (values ('00:00:00'::time, '02:00:00'::time, 'Night flight') 
           , ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
           , ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
           , ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
           , ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
           , ('19:00:00'::time, '24:00:00'::time, 'Night flight')
      )
      select flight_id               "Filght ID"
      ,     flight_no                "Flight No"
      ,     scheduled_departure      "scheduled_departure"
      ,     scheduled_arrival        "scheduled_arrival"
      ,     flights                  "Timings"
      from flights  
      join bookings.flights    f 
     on (   (scheduled_departure at time zone 'Asia/Kolkata')::time(0) >= stime 
        and (scheduled_arrival at time zone 'Asia/Kolkata')::time(0) <  time 
        )

''' but in the modified column I just want the Text such as 'Night Flight' instead of the entire time which is '00:00:00'::time, '02:00:00'::time, 'Night flight' how can I get just the text output?

2nd I tried to filter the output using having'or where statement using this code

 with flights ( stime, etime, description)  as 
     (values ('00:00:00'::time, '02:00:00'::time, 'Night flight') 
           , ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
           , ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
           , ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
           , ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
           , ('19:00:00'::time, '24:00:00'::time, 'Night flight')
      )
      select flight_id               "Filght ID"
      ,     flight_no                "Flight No"
      ,     scheduled_departure      "scheduled_departure"
      ,     scheduled_arrival        "scheduled_arrival"
      ,     flights                  "Timings"
      from flights  
      join bookings.flights    f 
     on (   (scheduled_departure at time zone 'Asia/Kolkata')::time(0) >= stime 
        and (scheduled_arrival at time zone 'Asia/Kolkata')::time(0) <  etime 
        )
        group by 
        flight_id,
        flights 
having flights = 'Morning flight'

it is giving me an error saying this

ERROR: input of anonymous composite types is not implemented LINE 22: having flights = 'Morning flight'

(Pasting text because image is allowed in my account)

@belayer

---

Response:

• First Issue, unwanted columns: You only want want the Text such as 'Night Flight' instead of the entire time. Well each column is individually SELECTed in the query. If there are some you do not want then just remove them from the select list.
• Second issue: flights = 'Morning flight, flights refers to the entire set on rows within the CTE. The usage would be the same as if there were a table flights, you would not say table_name = string. In this case you are looking for flights.description = 'Morning flight. You will get a similar error on the line aliased as "Timings". You will need to refer to the individual columns.
• Third Issue: Filtering. The target column of HAVING is typically the result of an aggregate column. However, your query has no aggregate function, so WHERE is appropriate. (Note the structure change. You can google SQL Order of Operations. So:

    with flights ( stime, etime, description)  as 
             (values ('00:00:00'::time, '02:00:00'::time, 'Night flight') 
                   , ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
                   , ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
                   , ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
                   , ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
                   , ('19:00:00'::time, '24:00:00'::time, 'Night flight')
              )
              select flight_id               "Filght ID"
              ,     flight_no                "Flight No"
              ,     scheduled_departure      "scheduled_departure"
              ,     scheduled_arrival        "scheduled_arrival"
              ,     flights.description      "Timings"   -- ?? Guessing 
              from flights  
              join bookings.flights    f 
             on (   (scheduled_departure at time zone 'Asia/Kolkata')::time(0) >= stime 
                and (scheduled_arrival at time zone 'Asia/Kolkata')::time(0) <  etime 
                )
        Where flights.description = 'Morning flight';