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

Question

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

Good morning everyone!

I currently work with postgresql and am doing a database migration, and I need to create a view of my old bank tables. Well, I need the numeric columns to be rounded 15,3 but I'm encountering a problem that I couldn't understand.

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 View column data type" statistic_measure_quantity " from numeric(15,4) to numeric (15,3)

Thank you for all your help!

1

postgresql sql view cast

Author: Lucas Ferreira, 2016-09-22

Source

1 answers

score 0 · Answer 1

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

Basically, I can't 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.