Union Join on postgresql with timestamp column

Question

It's a simple query to fetch attendance rows for a student on a particular date.

Conditions 1. If a student has attendance for that date get it 2. If not just retrieve student columns

My query looks like

Select 0 as attendanceId, 0 as attendanceVersion, '' as inTime,'' as 
outTime,'' as summary,Student.id As 
StudentId,Student.firstName,Student.lastName 
From  Student where student.id not in 
    ( Select student.id From Student join Attendance on ( student.id = 
      Attendance.studentId )
      where  attendance.inactiveDate is null and student.inactivedate is 
      null and 
      date(attendance.intime) = '2019-06-23' )
and student.inactivedate is null  

UNION

Select Attendance.id As AttendanceId,Attendance.version,Attendance.inTime,Attendance.outTime,Attendance.summary,
Student.id As StudentId,Student.firstName,Student.lastName 
From Student join Attendance on ( student.id = Attendance.studentId ) where  
attendance.inactiveDate is null and student.inactivedate is null and 
date(attendance.intime) = '2019-06-23' 
order by student.firstname, student.id

What I'm trying to do: Select student rows without attendance and union join it with student rows with attendance.

Problem: Getting the following error

ERROR: invalid input syntax for type timestamp with time zone: ""

SQL state: 22007

Character: 51

I was hoping postgres would gracefully substitute empty literal for timezone. How to substitute empty string for timezone or a better way to do this query

UPDATE:

Select 
Attendance.id As AttendanceId,Attendance.version, Attendance.inTime,Attendance.outTime,Attendance.summary,
Student.id As StudentId,Student.firstName,Student.lastName 
From Student left join Attendance on ( student.id = Attendance.studentId ) 
where student.inactivedate is null and date(attendance.intime) = '2019-06-23' 
order by student.firstname, student.id

Produces a single row like a inner join. Guess it's because I'm joining on Studentid on attendance?!

It sounds more like you want a left join. Post up some example data and expected output please — Caius Jard
Left join is behaving like inner join. Not sure what I'm doing wrong. See update for left join result — Cybermonk
Regarding the error: '' isn't a valid constant for a timestamp value. You need to use null::timestamptz as intime instead of '' as intime in your first query to select an "empty" timestamp value. — a_horse_with_no_name

Caius Jard Caius Jard · Accepted Answer · 2019-06-23T04:59:12

Any time that you use a left joined column in a where clause the query will convert to an inner join:

SELECT * FROM
  lef 
  LEFT JOIN
  ri 
  ON lef.id = ri.id
WHERE ri.column = 'some value'

This query will work like inner; the left join will put nulls in for any row in ri that doesn't have a match, but then the where clause will take out the nulls because none of them can ever be equal to 'some value'

Null is never equal to anything

To get around this, put the predicate in the join condition instead:

SELECT * FROM
  lef 
  LEFT JOIN
  ri 
  ON lef.id = ri.id AND ri.column = 'some value'

HENCE YOUR QUERY:

Select 
  a.id As AttendanceId,
  a.version,     
  a.inTime,
  a.outTime,
  a.summary,
  s.id As StudentId,
  s.firstName,
  s.lastName 
From 
  Student s 
  left join 
  Attendance a
  on 
    s.id = a.studentId AND 
    date(a.intime) = '2019-06-23')
WHERE
  s.inactivedate is null 
ORDER BY s.firstname, s.id

Tips:

alias your tables. It cleans up your code and allows you to join the same table in multiple times
indent your code rather than just barfing it all into a massive block; helps see what parts of the query go where - i indent so that everything in a query that is at the same level of processing is at the same level of indent. Key words like select, from, on and where form block headers and then everything under them is indented
remember to always put predicates that refer to the right table in a left join (or the left table in a right join) in the ON not the where

Union Join on postgresql with timestamp column

2 Answers