Currently IMPORT FOREIGN SCHEMA does not seem to support IF NOT EXISTS so how to solve this case? If I have a script that runs on every reboot for example I would get
ERROR: relation "xxx" already exists
Is there a way to overcome this?
Add a comment
|
1 Answer
Use the EXCEPT clause to exclude those tables that are already there:
IMPORT FOREIGN SCHEMA xy EXCEPT (xxx, yyy, zzz) FROM ...
Alternatively, delete all foreign tables first. Creating them again shouldn't take too long.
8 Comments
rik
nice thought but not very helpful. If your existing foreign imported schema contains tens of thousands of tables (I work with a couple that have more than 80,000 tables) then spelling out each table you want to exclude by name isn't really practical. As to your second suggestion to just delete all foreign tables first; if you do that you'll have to cascade delete any views, materialized views, etc. that reference those tables and then recreate them all over again. So once again, not practical. That command desperately needs a IF NOT EXISTS option.
Laurenz Albe
@rik I think that if you have a requirement like that and you have materialized views on top of those foreign tables, you are lost anyway. But I won't object your patch to introduce
IF NOT EXISTS.rik
Laurenz: not sure why I would be 'lost anyway'. If I have a schema with 70,000 foreign tables, 100 of the existing tables have views built on them that would be lost if were to just delete the tables. If I then want to run IMPORT FOREIGN SCHEMA to catch 1000 new tables that were added to the foreign schema, I'm not sure why I would be lost anyway? Right now the only option that exists is to create a temp schema, IMPORT everything there, use pg/psql to do a comparison and only IMPORT ones that don't already exist, deleting the temp schema when I'm done. Long way around the barn.
Laurenz Albe
I see what you mean, and you have a point. You can suggest it to pgsql-hackers! My workaround for that would be to find all foreign tables that already exist and build an
EXCEPT clause from them as suggested by my answer. That can be a single statement in psql if you use \gexec.rik
Postgresql is written in what C? Haven't touched that in decades, perhaps I should dust the cobwebs out of my brain and take a crack at submitting a patch to add that functionality in..... (ꉺ⍸ꉺ)
|