postgres change data type of view column from unknown to text

Question

I just create a new view as follows

CREATE OR REPLACE VIEW gettreelistvw AS 
 SELECT "CRM".groupid, 'pointrewarding'::text AS applicationid, "CM".menuid, "CM".menuname, "CM".levelstructure, "CM".moduleid, "CM".haschild, "CM".installed
   FROM core_capabilitymap "CRM"
   JOIN core_menus "CM" ON "CRM".menuid::text = "CM".menuid::text;

ALTER TABLE gettreelistvw

when i execute this error appear

ERROR: cannot change data type of view column "applicationid" from unknown to text

although I already cast the value of applicationid column to text. it's still recognized as unkown datatype

'pointrewarding'::text

The alternative method of postgres conversion also didn't work.

CAST('pointrewarding' AS TEXT)

How to solve this problem.

You should check this on the StackExchange DBA : dba.stackexchange.com/questions/586/… — Red15
– Red15, Commented Feb 21, 2014 at 14:36

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-05-16 11:45:45Z

16

If you want to change the data type of a view's columns, you have to drop it, then create it.

Version 9.2 docs

CREATE OR REPLACE VIEW .... 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.

Emphasis added.

answered May 16, 2013 at 11:45

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Timur Shtatland Over a year ago

This is still correct as of Version 15.

Collectives™ on Stack Overflow

postgres change data type of view column from unknown to text

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related