Select with cast(Numeric as decimal) works, but view creation does not

Asked

Viewed 211 times

1

Good morning to all!

I currently work with postgresql and am doing a database migration, and need to create a view of my old database tables. Well, I need the numerical columns to be rounded 15.3 but I’m finding a problem I couldn’t figure out.

select works:

select cast(15.2547 as decimal(15,3)) as quantidade_medida_estatistica

View creation does not work:

create or replace view teste as select cast(15.2547 as decimal(15,3)) as quantidade_medida_estatistica

The error returned is:

ERROR: cannot change column data type of view "statistical quantity_measured" from Numeric(15,4) to Numeric(15,3)

I appreciate all your help!

sql postgresql view cast

  • Are you creating even so "failed" ? If it is this do with 15.254 or 15.255 , No being this (creating in column) try with a round .... roundcast 9.... ,3) as ....

  • Thanks @Motta ! Wouldn’t be flunked even not rs. It was for example only. Your solution works too, but one guy explained my problem here [http://stackoverflow.com/a/39642666/5453988]. Basically the problem is that I use create or replace view, in which case a drop and create view would apply. Thank you!

  • Answer your question not to stay open. Only when reading everything is known that it has already been solved.

1 answer

0

I found the solution and explanation for my problem in answering this topic: https://stackoverflow.com/a/39642666/5453988 .

Basically, I cannot perform a CREATE OR REPLACE VIEW by changing the type of a column. For these cases it is necessary to perform a DROP view and then a CREATE view.

Browser other questions tagged sql postgresql view cast

You are not signed in. Login or sign up in order to post.