Why does PostgreSQL SELECT query return different results when specifying schema name?

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 am getting several results similar to:

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

      

But when I use this query:

select * from schema_name.table_name;

      

I get the complete result:

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

      

The columns c

and d

were added later, after the initial table was created. My question is, why does the database ignore the later columns if the schema name is not specified in the select query?

+3


source to share


1 answer


Table names are not unique across a database in Postgres. Different schemas can have any number of tables named "table_name" - including the temporary schema, which always comes first unless you explicitly list it after the other schemas in search_path

. Obviously there are multiple named tablestable_name

. You must understand the role to interpret correctly: search_path

The first table is in the schema that precedes schema_name

yours search_path

(or schema_name

is not there at all). Thus, the unqualified table name is allowed for this table (or view). Check the list of tables named "table_name" that your current role in your database has access to:



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

      

Views are just special tables with an RULE

internal attached . They can play the same role as a regular table and are included in the above query. Details:

+5


source







All Articles