Is there any way to export only the views from a Postgres schema?
I'm using Postgres 8.4.
Thank you.
Add a comment
|
2 Answers
There's no direct flag to do this, but using our favourite query-the-schema-to-generate-a-command technique:
select string_agg( '-t ' || quote_ident(nspname) || '.' || quote_ident(relname), ' ' )
from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relkind = 'v' and not (nspname ~ '^pg_' or nspname = 'information_schema');
This will generate a string that can be used with a pg_dump command, e.g.:
-t media.duplicated_component -t adv.advert_view_distribution
Which you could then splice into a command line directly:
pg_dump $(psql -c "select string_agg(...etc...)" db) db
1 Comment
Nobu
If you want to add materialized view, use
relkind in ('v', 'm').If you have every view prefixed by certain prefix, you can use this command:
pg_dump -s -t 'prefix*' dbname > db.dump
or you can use -t switch as many as possible with names of views.
See manpage for pg_dump, on the end are examples.
3 Comments
Edward Anderson
Note that this exports the views, not the data that the views return. This is probably obvious to most, but it wasn't to me at first. To export the data returned by a view, see stackoverflow.com/questions/1745105/…
kez
You'd probably favour dropping the
-s and still using pg_dump, instead of going down the COPY route discussed on that question
Learner
I get error message
pg_dump: error: no matching tables were found even after supplying wildcard!