Thread: Can't drop a view ("view does not exist") that has an entry in "INFORMATION_SCHEMA.views"

Hi guys,
    I need to drop a view in order  to alter a type of a column:
    numeric(12,1) ->  numeric(12,2):

    ERROR:  cannot alter type of a column used by a view or rule
    DETAIL:  rule _RETURN on view "TransTasksCube" depends on column
    "billable_units"

    Trying to delete it, I get:

    projop=# drop view TransTasksCube;
    ERROR:  view "transtaskscube" does not exist

    FYI: VIEW also shows up in table INFORMATION_SCHEMA.views:

    projop=# select table_name from INFORMATION_SCHEMA.views where
    table_name like '%Trans%';
       table_name
    ----------------
     TransTasksCube
    (1 row)

    Appreciate your help!
    Klaus

    --

      <span style="font-size:8pt; font-family:'Verdana'; color:#666666;
        text-decoration:none;">
        Klaus Hofeditz
        Co-Founder ]project-open[

        Web: http://www.project-open.com
        http://www.project-open.org
        LinkedIn: <a

href="http://www.linkedin.com/pub/klaus-hofeditz/2/604/871">http://www.linkedin.com/pub/klaus-hofeditz/2/604/871
        XING: <a
          href="https://www.xing.com/profile/Klaus_Hofeditz">https://www.xing.com/profile/Klaus_Hofeditz
        Twitter: https://twitter.com/projop

Le 17 nov. 2014 22:49, "Klaus Hofeditz ]project-open[" <klaus.hofeditz@project-open.com> a écrit :
>
> Hi guys,
> I need to drop a view in order  to alter a type of a column: numeric(12,1) ->  numeric(12,2):
>
> ERROR:  cannot alter type of a column used by a view or rule DETAIL:  rule _RETURN on view "TransTasksCube" depends on column "billable_units"
>
> Trying to delete it, I get:
>
> projop=# drop view TransTasksCube;
> ERROR:  view "transtaskscube" does not exist
>
> FYI: VIEW also shows up in table INFORMATION_SCHEMA.views:
>
> projop=# select table_name from INFORMATION_SCHEMA.views where table_name like '%Trans%';
>    table_name
> ----------------
>  TransTasksCube
> (1 row)

You need double quotes because of the upper case letters:

drop view "TransTasksCube";

Hi Guillaume  splendid - tx! ./k

      On 17/11/2014 22:53, Guillaume Lelarge wrote:

    <blockquote
cite="mid:CAECtzeWLZ2tJDg=-Ax3Rj_LpAhtVaVaAbi1Gh+VCxVEAnu2KiA@mail.gmail.com"
      type="cite">
      Le 17 nov. 2014 22:49, "Klaus Hofeditz
        ]project-open[" <<a moz-do-not-send="true"
          href="mailto:klaus.hofeditz@project-open.com">klaus.hofeditz@project-open.com>
        a écrit :
        >
        > Hi guys,
        > I need to drop a view in order  to alter a type of a
        column: numeric(12,1) ->  numeric(12,2):
        >
        > ERROR:  cannot alter type of a column used by a view or
        rule DETAIL:  rule _RETURN on view "TransTasksCube" depends on
        column "billable_units"
        >
        > Trying to delete it, I get:
        >
        > projop=# drop view TransTasksCube;
        > ERROR:  view "transtaskscube" does not exist
        >
        > FYI: VIEW also shows up in table INFORMATION_SCHEMA.views:

        >
        > projop=# select table_name from INFORMATION_SCHEMA.views
        where table_name like '%Trans%';
        >    table_name
        > ----------------
        >  TransTasksCube
        > (1 row)
      You need double quotes because of the upper case
        letters:
      drop view "TransTasksCube";

Klaus Hofeditz ]project-open[ wrote on 17.11.2014 23:10:
>> > Hi guys,
>> > I need to drop a view in order  to alter a type of a column: numeric(12,1) ->  numeric(12,2):
>> >
>> > ERROR:  cannot alter type of a column used by a view or rule DETAIL:  rule _RETURN on view "TransTasksCube"
dependson column "billable_units" 
>> >
>> > Trying to delete it, I get:
>> >
>> > projop=# drop view TransTasksCube;
>> > ERROR:  view "transtaskscube" does not exist
>> >
>> > FYI: VIEW also shows up in table INFORMATION_SCHEMA.views:
>> >
>> > projop=# select table_name from INFORMATION_SCHEMA.views where table_name like '%Trans%';
>> >    table_name
>> > ----------------
>> >  TransTasksCube
>> > (1 row)
>>
>> You need double quotes because of the upper case letters:
>>
>> drop view "TransTasksCube";
>>
>

> Hi Guillaume  splendid - tx! ./k

Here is a detailed explanation on why you need this:

   http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

But in general you should avoid quoted identifiers completely.