pg_dump from view has no data in postgresql 10

Question

pg_dump --table=export_view --data-only --column-inserts mydb > export_view.sql

pg_dump (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg18.04+1)

Export specific rows from a PostgreSQL table as INSERT SQL script and the postgresql documentation (https://www.postgresql.org/docs/10/app-pgdump.html) suggest it is possible to pg_dump from a view with the --table flag. If I export from the table directly I get the expected result (ie, data is exported). If I select from the view in psql I get the expected result. However whether I create a view or a materialized view and then try and pg_dump, I get only the normal pg_dump headers and no data. A commenter (https://stackoverflow.com/users/2036135/poshest) also appears to have faced the same issue in the above SO question, with no solution given.

If I CREATE TABLE blah AS SELECT x, y, z FROM MYTABLE then I can export fine. If I CREATE VIEW blah AS SELECT x, y, z FROM MYTABLE then the export is empty.

What am I doing wrong?

Interosculate

Answer 1

As @bugmenot points out, version 13 (and above?) - the current at the time this answer is written - indeed has clarification on what gets dumped:

As well as tables, this option can be used to dump the definition of matching views, materialized views, foreign tables, and sequences. It will not dump the contents of views or materialized views, and the contents of foreign tables will only be dumped if the corresponding foreign server is specified with --include-foreign-data.

(emphasis added).

So the answer (to myself) is: "You are not doing anything wrong, except that you incorrectly interpreted the documentation for Postgres <=12. What you want to do is not possible."

Interosculate

Answer 2

Views do not store data, they provide a dynamic view onto it. When you include views in your dump, you will only get the view definition.

Chalaza

Thanks, I know what a view is (though materialized views do actually store data, so if you're going for the pedagogical angle, you should probably include that restriction). There is absolutely no logical reason why pg_dump couldn't include data from the result of calling a view though, and the documentation referenced fails to make this clear at all. – Interosculate

Didn't mean to sound condescending, sorry if that seemed the case. The confusion seems to be about what pg_dump is dumping and what a database "is". The latest version of the docs has gotten a note about views btw: postgresql.org/docs/current/app-pgdump.html – Chalaza