DROP VIEW IF EXISTS fails if a TABLE of that name exists, and vice versa
(1) By Richard PArkins (rparkins) on 2021-01-16 22:30:29 [source]
-- Script started
DROP TABLE IF EXISTS "xxx" ;
-- No error
DROP VIEW IF EXISTS "xxx" ;
-- No error
DROP TABLE IF EXISTS "yyy" ;
-- No error
DROP VIEW IF EXISTS "yyy" ;
-- No error
CREATE TABLE "xxx" (col1, col2, col3) ;
-- No error
CREATE VIEW "yyy" AS SELECT * FROM "xxx" ;
-- No error
DROP VIEW IF EXISTS "xxx" ;
-- Error: use DROP TABLE to delete table xxx Unable to execute statement.
DROP TABLE IF EXISTS "yyy" ;
-- Error: use DROP VIEW to delete view yyy Unable to execute statement.
I don't think that either of these error messages is justified. DROP VIEW IF EXISTS
should succeed if no view of that name exists.
https://www.sqlite.org/lang_naming.html
says "When searching database schemas for a named object, objects of types that cannot be used in the context of the reference are always ignored." A DROP VIEW statement drops a view, and a table name cannot logically be used there.
If I try to create a view and a table of that name exists, it will fail, and it I try to create a table and a view of that name exists, it will also fail. IMHO it's perfectly reasonable to DROP either of them which exists as I do at the start of the script. This is part of a set of tests which create lots of tables and views, and I really don't think that I should have to remember which of them I have created earlier.
Should you argue that at the end of each test I can drop any views and tables that I have created, I can't do that in the script because I need to look at the result of the test first.
(2) By Stephan Beal (stephan) on 2021-01-16 22:40:37 in reply to 1 [link] [source]
DROP VIEW IF EXISTS "xxx" ;
This topic generated a fairly long discussion last summer over in /forumpost/daec33be27.
(3) By Richard PArkins (rparkins) on 2021-01-22 12:09:22 in reply to 2 [link] [source]
Thanks for the link. I will post in that thread.