TAGS :Viewed: 23 - Published at: a few seconds ago

[ Get default column value when referencing view SQL / Postgres ]

How can I get the default value for a column if I can only reference a view, which is a composite of multiple tables? Rather, is it even feasible?

Disregard permissions for the time being. How to get the default value for a column from a table directly is discussed here, however that is insufficient because if I query for the default value of the column in the view, it reasonably returns nothing/null.

Answer 1


First you need to find out, which table the column originates from. You can query the system catalogs:

SELECT definition
FROM   pg_views
WHERE  viewname = 'my_view'
AND    schemaname = 'public';

Or get the view definition in psql with:

\d+ my_view

Or just look it up in the object browser in pgAdmin.
Then you can proceed from there with the techniques you already found:

Or, to get an actual value, not just the expression to compute it: