Using pg_dump on a view always produces an empty file

Question

I want to export a subset of a tables rows using pg_dump (using powershell on windows). The simplest way seems to be to create a view with an appropriate WHERE clause. However, when I run pg_dump on the view the resulting output files never has data in it (it has several rows of SET statements, but no actual data.)

I can successfully run pg_dump on tables without any problems, and creating an appropriate view isn't a problem, I can select from the view using the same account that I'm running pg_dump from so I don't think permissions are an issue. pg_dump isn't producing any errors.

I've been using this command to create the view...

CREATE OR REPLACE VIEW viewA 
AS
(
  select * from tableA
  WHERE blah blah blah....
);

And this command to do the dump

$env:PGPASSWORD='own'
.\pg_dump.exe -U myuser -d mydatabase -t viewA -f mydumpfile 

This works (i.e. it runs without error) but mydumpfile never contains data.

If I do something very similar, but creating a table instead....

CREATE TABLE tabB 
AS
(
  select * from tableA
  WHERE blah blah blah....
);

Followed by an almost identical pg_dump on the table...

$env:PGPASSWORD='own'
.\pg_dump.exe -U myuser -d mydatabase -t tabB-f mydumpfile 

Then I get a dump file with the correct data.

Is there any way of using pg_dump against a view. The postgres documentation suggests it should be possible

Answer 1

Is there any way of using pg_dump against a view. The postgres documentation suggests it should be possible

This is not an answer to your question per-se, but the PostgreSQL documentation is certainly very ambiguous there, so I submitted a patch to clarify what will be dumped (in particular, only the definitions of views), which has been accepted and will be reflected in the current documentation soon.

As mentioned in the comments, the only way to extract the data generated by a view is via psql's \copy command, or possibly the COPY command itself.