2

I check pg_dump doc, and there seems to be an option that allows to export only views. But is there a way to dump a database without all the views?

1 Answer 1

2

There's no option that excludes all views. But there are workarounds.

If you isolate all your views in a single schema (as in create schema views_only...), you can use

  • either the -n or --schema=public argument to select only the public schema (might not do what you want),
  • or the -N or --exclude-schema=views_only.

If you follow a strict(ish) naming convention, you can use

  • either the -t or --table=something to select only the tables,
  • or the -T or --exclude-table=something to exclude the views.

Be careful with this. In the relational model, base tables and views share a namespace for a reason--they're supposed to be interchangable. SQL generally honors that part of the relational model. Some people use prefixes for database objects, so "we know whether an object is a view, a table, or a stored procedure that returns a table". If you build views with names like "view_wibble", and later decide to replace that view with a table, you have the distasteful options of naming your table "view_wibble", or rewriting application code, stored procedures, other views, etc to use "tbl_wibble" instead of "view_wibble".

But you can use these command-line options more than once. So you can name each of the views you want to exclude. The is probably the best idea.

pg_dump ... --exclude-table=view1 --exclude-table=view2 --exclude-table=view3 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.