postgres change data type of view column from unknown to text

postgresql view sqldatatypes
Angga Saputra picture Angga Saputra · May 16, 2013 · Viewed 11.2k times · Source

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.

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · May 16, 2013

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.

Related questions

Equivalent of "describe table" in PgAdmin3

Question asked and answered: As many of us know, PostgreSQL does not support describe table or describe view. As one might find from google, PostgreSQL uses \d+ instead. However, if one accesses PostgreSQL using PgAdmin (I am actually using PgAdmin3) …

Read More
Execute Trigger on View?

I am not too familiar with database triggers and/or views. I am currently using PostgreSQL and HSQL; although the database is not too important. I am just wondering if any database offers something like this: I have an (example) …

Read More
Generate id row for a view with grouping

I'm trying to create a view with row numbers like so: create or replace view daily_transactions as select generate_series(1, count(t)) as id, t.ic, t.bio_id, t.wp, date_trunc('day', t.transaction_time)::date transaction_…

Read More