1

In a view, I'm casting a timestamp without timezone to date.

Looks to me like any of these should work:

Cast("Orders"."OrderDate" as Date),
Date("Orders"."OrderDate"),
"Orders"."OrderDate"::Date,

but they all give the error 

cannot change data type of view column "OrderDate" from timestamp without time zone to date

Postgres 11
pgAdmin 4.9

BWhite
  • 713
  • 1
  • 7
  • 24
  • try this previous question: https://stackoverflow.com/questions/6133107/extract-date-yyyy-mm-dd-from-a-timestamp-in-postgresql – Paul Maxwell Aug 30 '19 at 23:21
  • Did that. It recommends the second and third approaches I tried above. Which don't work for me. Can't change data type to date – BWhite Aug 30 '19 at 23:31
  • 1
    Are you trying to change the definition of an existing view? If so, you can't change the type of a column. You will need to drop the view and create it again. – Jeremy Aug 31 '19 at 06:40
  • @Jeremy That was it. Then you have to close any open queries, refresh the list of views, reopen the queries. Can you post this as an answer? – BWhite Aug 31 '19 at 18:46

2 Answers2

1
select date_trunc('day', now());

| date_trunc             |
| :--------------------- |
| 2019-08-31 00:00:00+01 |

select date_trunc('day', now())::date;

| date_trunc |
| :--------- |
| 2019-08-31 |

db<>fiddle here

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • I get the same result you do for the first query. Trims the time, returns just the date. – BWhite Aug 30 '19 at 23:29
  • But the second query returns the same error as the other approaches. Can't convert to Date – BWhite Aug 30 '19 at 23:30
  • what version of postgres? as you can see it worked for me (click the "here" in the answer) that s a live demo. & Consider building a fiddle of your own to replicate the issue you face. – Paul Maxwell Aug 30 '19 at 23:38
1

You can't change the type of a column in a view. You will need to drop the view along with all of the objects that depends on that view and create them again.

Jeremy
  • 6,313
  • 17
  • 20