Postgresql: Table Name/Schema Confusion

Postgresql: table name / schema confusion

Postgresql defaults to lower case characters while being case sensitive with column/table names:

select * from public.Abc 

is actually:

select * from public.abc

That's because your table is called Abc, so it cannot be found.

You can override this lower case behavior via quotation marks, so "Abc" is handled as Abc.

Get the name of a row's source table when querying the parent it inherits from

To identify the source table of a particular row, use the tableoid, like you found yourself already.

A cast to regclass retrieves the actual name, automatically schema-qualified where needed according to the current search_path.

SELECT *, tableoid::regclass::text AS table_name
FROM   master.tbl
WHERE  <some_condition>;

More:

• Find out which schema based on table values
• Select (retrieve) all records from multiple schemas using Postgres
• How does the search_path influence identifier resolution and the "current schema"

Different schemas containing same tables names

In postgres search_path variable set the priority for choosing the schema for the table:

search_path (string)
This variable specifies the order in which
schemas are searched when an object (table, data type, function, etc.)
is referenced by a simple name with no schema specified. When there
are objects of identical names in different schemas, the one found
first in the search path is used.

[...]

The current effective value of the search path can be examined via the SQL function current_schemas.

A quick way to do so:

SELECT current_schemas(True);

You can find details on how to manage search_path in section 5.7.3. of this article: http://www.postgresql.org/docs/current/static/ddl-schemas.html

Postgres database.table notation

A DATABASE within MySQL is comparable with a SCHEMA in PostgreSQL and many other brands.

MySQL: ALTER TABLE database.table...
Others: ALTER TABLE schema.table...

From the MySQL manual:

CREATE SCHEMA is a synonym for CREATE DATABASE.

This actually means that a single MySQL server has just a single database. This database can have many schema's and every schema can have many tables.

Within a single database you can jump from one schema to the other schema, no problem. This works for MySQL, PostgreSQL and many others. You can not jump from one database to another database without a new database connection because it's a different instance.

It is that MySQL uses a different name for a schema, it calls this a database. A little confusing.

If you want the same thing in other databases, like PostgreSQL, just use schema's within a single database.

Database schema confusion

Yes, the word "schema" unfortunately has become overloaded among database vendors.

"SQL-99 Complete, Really" says:

An SQL Catalog is a named group of Schemas. ...

An SQL Schema is a named group of SQL-data that is owned by a particular
. ... Every Schema Object has a name that must be unique
(among Objects of its name class) within the Schema it belongs to. The Schema Object name classes are:

• Base tables and Views
• Domains and UDTs
• Constraints and Assertions
• Character sets
• Collations
• Translations
• Triggers
• SQL-server Modules
• SQL-invoked routines

Oracle uses "schema" and "user" interchangeably, which always makes my eyebrows raise.

MySQL uses SCHEMA as a synonym for DATABASE.

PostgreSQL uses "schema" but uses "database" to refer to what standard SQL calls a "catalog."

Why PostgreSQL does not like UPPERCASE table names?

put table name into double quotes if you want postgres to preserve case for relation names.

Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case. For example, the identifiers
FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo"
and "FOO" are different from these three and each other. (The folding
of unquoted names to lower case in PostgreSQL is incompatible with the
SQL standard, which says that unquoted names should be folded to upper
case. Thus, foo should be equivalent to "FOO" not "foo" according to
the standard. If you want to write portable applications you are
advised to always quote a particular name or never quote it.)

from docs (emphasis mine)

example with quoting:

t=# create table "UC_TNAME" (i int);
CREATE TABLE
t=# \dt+ UC

t=# \dt+ "UC_TNAME"
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | UC_TNAME | table | postgres | 0 bytes |
(1 row)

example without quoting:

t=# create table UC_TNAME (i int);
CREATE TABLE
t=# \dt+ UC_TNAME
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | uc_tname | table | postgres | 0 bytes |
(1 row)

So if you created table with quotes, you should not skip quotes querying it. But if you skipped quotes creating object, the name was folded to lowercase and so will be with uppercase name in query - this way you "won't notice" it.

PosgtreSQL procedure

your function must be a set-returning

create or replace function name_list(schemaname text, tablename text) 
  -- function will return a set
  RETURNS SETOF text
language 'plpgsql'
as $body$
declare
  _query text;
begin
  -- correct way to format the dynamic sql
  _query := FORMAT('SELECT "names" FROM %I.%I;', schemaname, tablename);
  RAISE NOTICE '"%"' , _query;
  -- execute and return all
  RETURN QUERY EXECUTE _query;
end;
$body$;

PostgreSQL Schemas -- Usage Scenario/Case

I've seen people in my Google searches
about schemas make a separate
schema+tables for each online customer
to their website. They have like
100,000 schemas. Q3: What am I missing
here? This seems extreme to say the
least. They should be adding record(s)
to standard table(s) for each customer
not making schemas and tables for each
customer.

One database per tenant (customer) is easy to build, and it gives you the strongest isolation between tenants, and the simplest disaster recovery. But it's relatively expensive.

One schema per tenant is also easy to build. There's a smaller degree of isolation between tenants. A dbms can support more tenants per server with one schema per tenant than with one database per tenant. Disaster recovery for one tenant is more complicated than with one database per tenant.

A shared schema requires every row to have a tenant identifier. Hardware and backup are cheaper; disaster recovery for one tenant can be a real bitch. (To recover data for a single tenant, you have to recover some rows in every table. Performance suffers for all tenants when that happens.) Isolation is trickier. Since tenants share tables, making sure no tenant can access other tenants data is a lot harder than with one database or one schema per tenant.

The search term for this stuff is "multi-tenant database design". SO also has a multi-tenant tag.

Another common use is to group database objects that belong together. For example, if you were developing an accounting database, all the objects that implement "accounts payable" features might go in the "ap" schema. I use schemas for PostgreSQL extensions, too. In my db, I installed the hstore extension in the "hst" schema, the tablefunc extension in the "tbf" schema, etc.

---

Related Topics