How to get the end of a day?

Take the date, truncate it, add one day and subtract one second:

select date_trunc('day', date) + interval '1 day' - interval '1 second'

You can put the logic in an update if you want to change the data in the table.

Of course, you can also add 24*60*60 - 1 seconds:

select date_trunc('day', date) + (24*60*60 - 1) * interval '1 second'

But that seems less elegant.

Many ways.

Using the column name "ts" in my examples. "date" for a timestamp column would be misleading.

1.

Cast to date. (Here we need the type date. In other expressions, date_trunc() is just as well.). Then add 1 (integer) before subtracting the interval 1 second:

SELECT ts::date + 1 - interval '1 sec' AS last_sec_of_day

2.

Add a single interval '1 day - 1 sec'. No need for two operations, Postgres interval input allows a single expression.

SELECT ts::date + interval '1 day - 1 sec' AS last_sec_of_day

3.

Simpler yet, add the desired time component to the date:

SELECT ts::date + time '23:59:59' AS last_sec_of_day

4.

However, xxxx-xx-xx 23:59:59 is not the "end of the day". The Postgres timestamp data type (currently, but unlikely to change) stores values with microsecond resolution.
The latest possible timestamp for a day is xxxx-xx-xx 23:59:59.999999:

SELECT ts::date + interval '1 day - 1 microsecond' AS last_ts_of_day

5.

Equivalent:

SELECT ts::date + time '23:59:59.999999' AS last_ts_of_day  -- or interval

This last expression should be fastest besides being correct.

6.

However, the superior approach typically is to operate with the start of the next day as exclusive upper bound, which does not depend on implementation details and is even simpler to generate:

SELECT ts::date + 1 AS next_day

7.

The actual first timestamp of the next day:

SELECT date_trunc('day', ts) + interval '1 day' AS next_day_1st_ts

All work in any version since at least Postgres 8.4. Demo in Postgres 13:

db<>fiddle here