CODEWITHSUNDEEP
postgresqlviewcastingdecimalnumeric
Lucas Ferreira da Costa
Lucas Ferreira da Costa

Reputation: 81

Error on create view with cast numeric as decimal on postgresql

Good morning everyone!

I currently work with postgresql. Well, I need to create views where the numeric columns stay rounded 15.3 but I'm encountering a problem I could not understand.

The select work:

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

The view not working:

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

Error:

ERROR: can not change the data type of column view "quantidade_medida_estatistica" of numeric (15,4) to numeric (15,3)

Thanks!

Upvotes: 0

Views: 2110

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

This is a known "bug" in Postgres, which you can read about here.

CREATE OR REPLACE VIEW is not exactly the same as dropping the view and re-creating it. In this case the existing columns in the view need to be the same, as described in the documentation:

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

You can do what you want by dropping and re-creating the view.

Upvotes: 1

user330315
user330315

Reputation:

You didn't explicitly state that, but I guess the view already exists - at least the error message indicates that.

Unfortunately you can't change the data types of the columns of an existing view when using create or replace.

You need to drop and create the view:

drop view teste;
create view teste 
as 
select cast(15.2547 as decimal(15,3)) as quantidade_medida_estatistica;

Upvotes: 1

Related Questions