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
\copy
to export the data of the view