Search code examples
postgresqldatabase-schemapostgresql-9.2database-permissionssearch-path

Why does a PostgreSQL SELECT query return different results when a schema name is specified?

I have a PostgreSQL database table with 4 columns - labeled column_a, column_b, etc. I want to query this table with a simple select query:

select * from table_name;

I get a handful of results looking like:

column_a | column_b
---------+---------
'a value'|'b_value'

But when I use this query:

select * from schema_name.table_name;

I get the full result:

column_a | column_b | column_c | column_d
---------+----------+----------+---------
'a value'|'b value' |'c value' |'d_value'

Columns c and d were added at a later date, after initial table creation. My question is: Why would the database ignore the later columns when the schema name is left out of the select query?

Solution

  • Table names are not unique within a database in Postgres. There can be any number of tables named 'table_name' in different schemas - including the temporary schema, which always comes first unless you explicitly list it after other schemas in the search_path. Obviously, there are multiple tables named table_name. You must understand the role of the search_path to interpret this correctly:

    The first table lives in a schema that comes before schema_name in your search_path (or schema_name is not listed there at all). So the unqualified table name is resolved to this table (or view). Check the list of tables named 'table_name' that your current role has access to in your database:

    SELECT *
FROM   information_schema.tables 
WHERE  table_name = 'table_name';

    Views are just special tables with an attached RULE internally. They could play the same role as a regular table and are included in the above query. Details: