1

I need to tune this statement to return the id if row exists or insert row if not exists based on 2 parameters:

WITH d(t, e) AS ( VALUES (1, current_timestamp)),
  t AS (SELECT id FROM attendance, d WHERE start = e and user_id = t),
  i AS (INSERT INTO attendance (user_id, start) 
           SELECT t, e FROM d WHERE t NOT IN (SELECT id FROM t))
SELECT t,e FROM d WHERE t IN (SELECT id FROM t);

1 and current_timestamp needs to be parametric. I will use this from nodejs. I have just used pgsql function because when I supplement timestamp like '2016-08-18T14:51:42.333Z' it is saying that I need to convert it.

Current state is that with current_timestamp function, it will insert the new row but not return it's id.

This is what I have constructed with help of this thread.

Thanks for solutions.

Community
  • 1
  • 1
greengold
  • 1,184
  • 3
  • 18
  • 43
  • 1
    Have you seen this? - [SELECT ⇒ INSERT](https://github.com/vitaly-t/pg-promise/blob/master/examples/select-insert.md). It has some bearing on your question, as far as [pg-promise](https://github.com/vitaly-t/pg-promise) is concerned, plus some useful links at the end ;) – vitaly-t Aug 18 '16 at 17:34
  • that is very handy! thanks vitaly-t! – greengold Aug 19 '16 at 22:34

0 Answers0