Darkrum Darkrum - 2 months ago 10
Node.js Question

PostgreSQL lead() issue with camelCase column

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

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:

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:

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
);