4

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?

2 Answers 2

7

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.

1

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

enter image description here

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.