You fix your (demonstrably incorrect) "whole stack" a single time and it's done. Perhaps a note in the documentation wouldn't hurt to clarify this edge case?Īs Larry pointed out, this sounds very much like a one-time problem which has simply struck a nerve for you, not a recurring thing which is a frequent/recurring hindrance. Sqlite> CREATE VIEW v1 AS SELECT * FROM t1 Use ".open FILENAME" to reopen on a persistent database. sqlite3Ĭonnected to a transient in-memory database. It says nothing about views, so there may be an issue (documentation or otherwise) if we try with drop table: %.
On the other side, the DROP TABLE docs say: The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist. Which implies (no guarantee) it doesn't return an error. If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op.
DROP TABLE IF EXISTS POSTGRESQL FULL
The full line in the DROP VIEW documentation says: If the specified view cannot be found and the IF EXISTS clause is not present, it is an error. What I did not understand is how you could not already know whether you had a table or a view. (I sympathize with that view, even if I don't agree with it.)Ĭan you describe some scenario, likely to be repeated, where this in fact creates more work than the following pseudo-code? drop view if exists SomeViewOrTable īTW, I understood your initial complaint. These considerations leave me wondering how your conundrum arose, and to question whether it is an ongoing, real difficulty or simply an unpleasant surprise coupled with insistence that your expectation was "correct". If you do not intend to recreate a view, and just intend to remove some grunge from a database, that is very much a one-off kind of problem, unworthy of lament such as "complicates a lot!". But I cannot square such a desire with uncertainty as to whether a table of that name already exists. I assume you want a view to be dropped because you are creating another one with the same name. (This is why I do not see documentation clarity on this as being more useful than pedantic.)
I hardly ever, perhaps never, have cause to convert tables to views or vice versa, and have never been faced with your difficulty. I have used "drop X if exists SomeName" a lot in development, always to ensure recreation of an X entity that might already be present, as an X entity. I suspect the additional "clarity" would consume brain cycles that would be put to better use elsewhere. Whether the additional verbiage would dilute the rest with less detriment that the rare confusion you have suffered is unclear.
DROP TABLE IF EXISTS POSTGRESQL CODE
Don't forget to adjust your code depending on your needs.The documentation could be more clear about this odd case. Even if your data bigger which cannot be partitionable for annually, you can do that monthly like partitioned_table_2021_01. Just a side note, if you want to partition your table for each year, your table suffix should be year like partitioned_table_2021. RETURN CONCAT('Executed query: ', (drop_query)) Īnd for the execution, you can run the below code SELECT drop_old_partitioned_tables('public', 'partitioned_table', '10') SELECT 'DROP TABLE IF EXISTS ' || string_agg(format('%I.%I', table_schema, table_name), ', ')ĪND table_name <= CONCAT(partitioned_table_name, '_', suffix) - It will also drop the table which equals the given suffixĪND table_name ~ CONCAT(partitioned_table_name, '_\d') partitioned_table_99 CREATE OR REPLACE drop_old_partitioned_tables(schema_name TEXT, partitioned_table_name TEXT, suffix TEXT) Like partitioned_table_00, partitioned_table_01.
Let's assume our partitioned table name is partitioned_table and we have a number suffix which we increase each time.
0 kommentar(er)
