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'
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?
source to share
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
. Obviously there are multiple named tables
. You must understand the role to interpret correctly:
The first table is in the schema that precedes
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
internal attached . They can play the same role as a regular table and are included in the above query. Details:
source to share