How to find nearest entries before and after a value in Postresql-CodePudding

Similar to this question: How to find the first nearest value up and down in SQL?

I have a Postgres DB Table named prices structured as:

id	column_1	column_2	column_3	date_col
1	1.5	1.7	1.6	1234560000
2	0.9	1.1	1.0	1234570000
3	11.5	23.5	17.5	1234580000
4	8.3	12.3	10.3	1234600000

I'm trying to select either the row that matches exactly to an input date:
Example #1: input query for date_col = 1234580000 would return...

id	column_1	column_2	column_3	date_col
1	1.5	1.7	1.6	1234580000

or if that date does not exist, then retrieve the entries immediately before and after:
Example #2: input query for date_col = 1234590000 would return...

id	column_1	column_2	column_3	date_col
3	11.5	23.5	17.5	1234580000
4	8.3	12.3	10.3	1234600000

I attempted to mess around with the code from the similar question, but I am a bit stuck and have resorted to trying to get the original query date -> check if the DB returned anything in Python, then I create a broad range for the DB to return, send the second query, and then iterate over the returned result in Python. If the result still doesn't exist then I make the range larger... which I know is the wrong way, but my brain is too smooth for this haha

Current code that works when the entry does exist, but does not work when the entry does not exist:

SELECT *
FROM prices, (SELECT id, next_val, last_val
             FROM (SELECT t.*,
                          LEAD(t.id, 1) OVER (ORDER BY t.date_col) as next_val,
                          LAG(t.id, 1) OVER (ORDER BY t.date_col)  as last_val
                   FROM prices AS t) AS s
             WHERE 1234580000 IN (s.date_col, s.next_val, s.last_val)) AS x
WHERE prices.id = x.id OR prices.id = x.next_val OR prices.id = x.last_val

based on the accepted answer this seemed to work:

SELECT * FROM (SELECT * FROM prices WHERE prices.date_col <= 1234580000 ORDER BY prices.date_col DESC LIMIT 1) AS a UNION (SELECT * FROM prices WHERE prices.date_col >= 1234580000 ORDER BY prices.date_col ASC LIMIT 1)

CodePudding user response：

I guess the simplest solution would be a UNION:

  SELECT *
  FROM prices
  WHERE date_col <= 1234580000
  ORDER BY date_col DESC
  LIMIT 1
UNION
  SELECT *
  FROM prices
  WHERE date_col >= 1234580000
  ORDER BY date_col ASC
  LIMIT 1