when does CREATE VIEW not create a view? - Mailing list pgsql-hackers
From | Brook Milligan |
---|---|
Subject | when does CREATE VIEW not create a view? |
Date | |
Msg-id | 200008221840.MAA06211@biology.nmsu.edu Whole thread Raw |
Responses |
Re: when does CREATE VIEW not create a view?
Re: when does CREATE VIEW not create a view? |
List | pgsql-hackers |
I am trying to create a view and have run across a, to me, bizarre occurance. One CREATE VIEW statement creates the view fine; changing the name of the view and repeating the same statement does not. This has nothing to do with conflicting names as appropriate DROP commands are issued first. To be specific, here are the queries and the results: -- precipitation_xxx_verify view created fine (see below) drop view precipitation_xxx_verify; create view precipitation_xxx_verify as select p.id, p.verified, w.name, w.country, w.state, w.county, p.date, p.precipitation / 2.54 as precipitation, -- 2.54 mm/inch p.inserted_by, p.inserted_on, p.verified_by, p.verified_on from precipitation_data p, weather_stations w where w.id= p.weather_station_id and verified != true; -- precipitation_english_verify view is not created as a view (see below) drop view precipitation_english_verify; -- XXX - fails because a view is not created (see below) drop tableprecipitation_english_verify; -- XXX - why not a view? create view precipitation_english_verify as selectp.id, p.verified, w.name, w.country, w.state, w.county, p.date, p.precipitation / 2.54 as precipitation, -- 2.54 mm/inch p.inserted_by, p.inserted_on, p.verified_by, p.verified_on from precipitation_datap, weather_stations w where w.id = p.weather_station_id and verified != true; \d precipitation_xxx_verify \d precipitation_english_verify View "precipitation_xxx_verify"Attribute | Type | Modifier ---------------+-----------+---------- id | integer | verified | boolean | name | text | country | text | state | text | county | text | date | timestamp | precipitation | float8 | inserted_by | name | inserted_on | timestamp | verified_by | name | verified_on | timestamp | View definition: SELECT p.id, p.verified, w.name, w.country, w.state, w.county, p.date, (p.precipitation/ 2.54) AS precipitation, p.inserted_by, p.inserted_on, p.verified_by, p.verified_on FROM precipitation_datap, weather_stations w WHERE ((w.id = p.weather_station_id) AND (p.verified <> 't'::bool)); View "precipitation_english_verify"Attribute | Type | Modifier ---------------+-----------+---------- id | integer | verified | boolean | name | text | country | text | state | text | county | text | date | timestamp | precipitation| float8 | inserted_by | name | inserted_on | timestamp | verified_by | name | verified_on | timestamp | View definition: Not a view It seems that the problem is with the word "english" as part of the view name. Variants of the name that lack it (e.g., replacing xxx above with eng, englih, etc.) seem to work fine, but variants that include it (e.g., replacing xxx with english, eenglish, englishh) suffer as above. Is there something special involved in handling view names that would preclude such names? Any explanations for this behavior are welcome. Thanks for your help. Cheers, Brook
pgsql-hackers by date: