8

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!

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.