Is it possible to export postgres database without views?

Question

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?

Answer 1

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 ...