Why postgresql pg_dump exports a view as a table?

Question

I am using PostgreSQL 9.3 pg_dump tool for extracting only the public schema definition using:

pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql

but when I check schema.sql one of our views appears in a CREATE TABLE statement instead of a CREATE VIEW statement.

But, if I pg_dump the specific view using:

pg_dump -s -t myview -h host -U postgres --dbname=db > ./schema.sql

then schema.sql contains the actual view definition.

So, why is this happening? Thank you guys!

Answer 1

Internally, a view is just a table with a rule, so this makes sense.

See here: https://postgresql.org/docs/9.5/static/rules-views.html

Views in PostgreSQL are implemented using the rule system. In fact, there is essentially no difference between:

CREATE VIEW myview AS SELECT * FROM mytab;

compared against the two commands:

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

because this is exactly what the CREATE VIEW command does internally. This has some side effects. One of them is that the information about a view in the PostgreSQL system catalogs is exactly the same as it is for a table. So for the parser, there is absolutely no difference between a table and a view. They are the same thing: relations.