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
– Caius Jard, Commented Jun 23, 2019 at 4:11
Left join is behaving like inner join. Not sure what I'm doing wrong. See update for left join result — Cybermonk
– Cybermonk, Commented Jun 23, 2019 at 4:41
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. — user330315
– user330315, Commented Jun 23, 2019 at 5:02

Caius Jard · Accepted Answer · 2019-06-23 05:04:38Z

3

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

edited Jun 23, 2019 at 5:04

answered Jun 23, 2019 at 4:59

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cybermonk Over a year ago

Thanks that works. Thanks for the tips too. I noticed my mistake and used and ( date(attendance.intime) = '2019-06-23' or date(attendance.intime) is null ) on where. That yields the same result too in case anyone is interested.

Caius Jard Over a year ago

It does indeed though there can be a subtle difference if the attendance.intime column can be naturally null for other reasons rather than being artificially null because there was no match - the rows where the column is null (but other values are filled) will show up too whereas in the ON version they won't. If an example would help left me know and I'll post one

Cybermonk Over a year ago

Here's a gotcha on my suggestion. If there's an attendance record for a student on date other than queried date, but not in current date : (date(attendance.intime) = '2019-06-23' or date(attendance.intime) is null) in where clause ignores it. Whereas your answer picks it up correctly

Gordon Linoff · Accepted Answer · 2019-06-23 13:27:51Z

1

I think you just want a left join:

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

edited Jun 23, 2019 at 13:27

answered Jun 23, 2019 at 4:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Cybermonk Over a year ago

Left join is behaving like inner join. Not sure what I'm doing wrong. See update for left join result

Gordon Linoff Over a year ago

@Cybermonk . . . That would happen if you had put filtering conditions in the WHERE clause rather than the ON clause.

Collectives™ on Stack Overflow

Union Join on postgresql with timestamp column

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related