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

Started by Klaus Hofeditz ]project-open[over 11 years ago4 messagesgeneral

klaus.hofeditz@project-open.com

over 11 years ago

Guillaume Lelarge

guillaume@lelarge.info

over 11 years ago

In reply to: Klaus Hofeditz ]project-open[ (#1)

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

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";

Klaus Hofeditz ]project-open[

klaus.hofeditz@project-open.com

over 11 years ago

In reply to: Guillaume Lelarge (#2)

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

<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hi Guillaume splendid - tx! ./k 
 
On 17/11/2014 22:53, Guillaume Lelarge wrote: 
</div>
<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>>
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";
</blockquote>
 
</body>
</html>

Thomas Kellerer

spam_eater@gmx.net

over 11 years ago

In reply to: Klaus Hofeditz ]project-open[ (#3)

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

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" 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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general