16

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?

0
21

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...

2
  • Thanks. I will follow these instructions and see if those are valid schema names. If so, then I'll accept this.
    – Ramon Tayag
    Jul 2 '13 at 2:46
  • Might want to add pg_ underscore to that link, like Nathan C mentioned. Jul 12 '13 at 1:31
5

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

3
  • 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.
    – Ramon Tayag
    Jul 2 '13 at 2:23
  • 3
    @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. Jul 2 '13 at 19:36
  • Yes you're probably right. Let me look into this. Jul 2 '13 at 23:55
3

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.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29 '17 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.