Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jul 2 '13 at 13:55

This question came from our site for professional system and network administrators.

3 Answers 3

up vote 8 down vote accepted

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

share|improve this answer
    
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. –  Ramon Tayag Jul 12 '13 at 1:31

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

share|improve this answer
    
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
2  
@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. –  Daniel Vérité Jul 2 '13 at 19:36
    
Yes you're probably right. Let me look into this. –  Ramon Tayag Jul 2 '13 at 23:55

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.

share|improve this answer
3  
Those are "some strange rules" invented by Postgres. It's the SQL standard which defines these requirements. –  a_horse_with_no_name Jul 2 '13 at 16:48
    
Let's say I name my table TBLUSER. I can't write a query SELECT * from TBLUSER in PG. I have to write SELECT * from "TBLUSER" because PG will not recognize TBLUSER unquoted. If that is part of the SQL naming standard, then I'll accept that. But I know of no other database that follows this particular rule. People coming from Microsoft naming conventions will have a hard time when they name their tables in camel case, e.g., tblUser or from Oracle when they name their tables in all caps, e.g., TBLUSER. –  efesar Jul 2 '13 at 16:55
4  
As long as you do not use quotes, SELECT * from TBLUSER will work just fine. The SQL standard requires quoted identifiers to be case-sensitive. And Oracle works the same way: create table "FooBar" (...) will require a select * from "FooBar" as well. The only way Postgres deviates from the standard is that names are folded to lowercase instead of uppercase. Oracle example: sqlfiddle.com/#!4/e4f71/1 Postgres example: sqlfiddle.com/#!12/e4f71/1 –  a_horse_with_no_name Jul 2 '13 at 17:00
1  
Yes, but then the table is named "TBLUSER" and not TBLUSER. Those are two different names. Using quoted identifier is not really recommended, btw. From the Postgres manual "If you want to write portable applications you are advised to always quote a particular name or never quote it" and from the Oracle manual "Oracle does not recommend using quoted identifiers for database object names" –  a_horse_with_no_name Jul 2 '13 at 17:44
5  
If you always quote identifiers both system behave absolutely identical. If you never quote identifiers both systems behave identical as well. Mixing unquoted and quoted identifiers is the worst choice you can make and both manuals rightfully discourage that. In my company using quoted identifiers is considered a bug in the SQL code. –  a_horse_with_no_name Jul 2 '13 at 18:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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