PostgreSQL lead() issue with camelCase column

Question

So I'm trying to query a time card table whose structure is like this

employeeId | clockInTime| clockOutTime
-----------+------------+--------------
   555     | 1462797450 | 1462785465 
   555     | 1462883850 | 1462871850 
   111     | 1463056650 | 1463044650 <== skip this
   555     | 1463143050 | 1463131050 <== get this
   555     | 1463229426 | 1463245655 <== but not this

What I'm trying to do is select all rows between two values but also the next row after that group of rows for that employee regardless of the value

This is my query

select "clockInTime", "clockOutTime", lead("clockInTime",1)
from "timeCard"
where "clockInTime" between 1462797450 and 1462883850
and "employeeId" = 555

but I get this error:

error: function lead(bigint, integer) does not exist

But when I remove the double quotes from lead() I only end up getting this because my column names are camelCase:

error: column "clockintime" does not exist

I'm using node.js and the node-pg client.

Answer 1

You did not "remove the double quotes from lead()". The error message reveals that you actually removed the double quotes from "clockInTime":

error: column "clockintime" does not exist

Consider:

• Are PostgreSQL column names case-sensitive?

The long and the short of it: Don't use caMelCase identifiers with Postgres if you can avoid it:

---

As for the task you describe:

select all rows between two values but also the next row after that group of rows for that employee

The OVER clause was missing from the window function lead() like @Gordon pointed out. But even with the syntax error fixed, lead() (or any other window function) does not seem like the right approach to get what you ask for. It adds a column to every row in the result, while you want to add a row to the set.

I suggest UNION ALL and ORDER BY / LIMIT 1 to add the "next" row to the result set:

SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" BETWEEN 1462797450 AND 1462883850

UNION ALL
(  -- parentheses required
SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" > 1462883850
ORDER  BY "clockInTime"
LIMIT  1
);

A multicolumn index on ("employeeId", "clockInTime") would make this very fast, even for big tables.

If "clockInTime" is not defined unique, you may want to add more expressions to ORDER BY to get a deterministic result in case of ties.

Parentheses are required to add LIMIT or ORDER BY to an individual leg of a UNION query. Example:

• Get n grouped categories and sum others into one

If you want the leading rows sorted as well:

(
SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" BETWEEN 1462797450 AND 1462883850
ORDER  BY "clockInTime"
)
UNION ALL
(
SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" > 1462883850
ORDER  BY "clockInTime"
LIMIT  1
);