What are the valid formats of a PostgreSQL schema name?

Question

I can't seem to find documentation that describes the valid formats of a PostgreSQL schema name. I know that a schema name cannot:

• start with a number
• have spaces
• start with pg_

What else? Where should I look?

Answer 1

Per the fine documentation, I think this might be what you're looking for.

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable...

• Ramon Tayag: Thanks. I will follow these instructions and see if those are valid schema names. If so, then I'll accept this.
• Ramon Tayag: Might want to add pg_ underscore to that link, like Nathan C mentioned.

Answer 2

The correct answer is the one provided by gsiems. However, I want to point out that PostgreSQL has rules on quoted identifiers that you might keep in mind. "Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.)" ... There are also some restrictions on case you might want to look at.

So if you're going to quote your identifiers, then you can use any character you want (with the exception of 0円). But if you aren't quoting your identifiers, you have to follow the rules outlined on that page.

I wanted to point this out mainly because it's bitten me before, especially the rules concerning case in non-quoted identifiers (and schema names count as identifiers).

UPDATE:

As an example (not specifically applicable to schema identifiers, but equally applicable to them):

 DROP TABLE "tbluser"; -- assuming it exists
 DROP TABLE "TBLUSER"; -- assuming it exists; incidentally, they are two different tables
 CREATE TABLE "TBLUSER" ( username text ); 
 INSERT INTO "TBLUSER" VALUES ( 'joe' ); 
 SELECT * FROM TBLUSER; -- this returns an error that the tbluser relation does not exist
 SELECT * FROM "TBLUSER"; -- works fine

This might be expected behavior for those who are experienced with PostgreSQL (and perhaps the SQL standards), but someone who is new to PG and coming from the standpoint of other database servers (SQL Server or Oracle for example) might bump into this behavior and wonder why the table they just created is missing.

Perhaps some manuals recommend against using quoted identifiers, but the fact of the matter is that quoted identifiers are available for use and can be used and furthermore, many packages make it a policy to always use quoted identifiers when creating and accessing relations that aren't entirely lowercase, e.g., PGAdmin III.

For example, this is the script generated by PGAdmin III when creating a table through the UI:

 CREATE TABLE public."TBLUSER"
 (
 username text
 ) 
 WITH (
 OIDS = FALSE
 )
 ;

Therefore, the only way a user can access this table in a query is by referring to its quoted identifier, i.e., "TBLUSER". Trying to access this table in a query with a non-quoted identifier will result in failure to locate the relation, i.e., TBLUSER.

• Paul White: Comments are not for extended discussion; this conversation has been moved to chat.

Answer 3

According to the documentation, it also can't start with pg_ as it's reserved. Other than that it looks fairly freeform.

• Ramon Tayag: Thanks I'll add that to the list of what schemas can't be named. Unfortunately, it's not the only rule, apparently. I could name it this-is schema and it would still be an invalid schema name.
• Daniel Vérité: @Ramon: this-is or this-is schema are valid schema names, strictly speaking. You seem to be confusing what is valid with when it must be quoted.
• Ramon Tayag: Yes you're probably right. Let me look into this.