Postgresql – Export Materialized View from postgresql as a table

materialized-viewpostgresql

I have a materialized view I created in Postgresql. I was wondering is there a way to pg_dump the materialized view as if they were tables, so when I import them into another database it sees it as tables rather than a view created from other tables?

Best Answer

I don't think there is a single-step way to do this. Are you trying to do this with one MV, or with a whole database full of them?

To get the proper CREATE TABLE statement with all the column types, you could do this:

CREATE TABLE dummy1 (like mv1 including indexes);

And then use pg_dump -t dummy1 > mv1.sql to get the CREATE TABLE statement.

To get the data, you would do:

\copy (select * from mv1) to mv1.txt

You would then have to stitch the two files together with a name change from 'dummy1' to 'mv1' in the first file, to get the definition and the data to be combined in one replayable file.

Related Question