1

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

2
  • 3
    If you dump a view, the definition of the view is dumped, not its content. You can use \copy to export the data of the view Sep 30, 2020 at 20:13
  • The view doesn't contain any data, it is base on the table
    – BongSey
    Oct 1, 2020 at 3:07

1 Answer 1

3

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.

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.