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?
Add a comment
|
2 Answers
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.
Agree that there's no direct way. What I would do is the following steps:
(1) save the Materialized View as a CSV file (see screenshot below)
(2) create a new table
(3) import the saved CSV
