Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2010
    Posts
    24

    Unanswered: Must double quote table name?

    In pgAdmin (or in Java code) when I query my table:

    SELECT * FROM mrclib1.TEST2 (or SELECT * FROM mrclib1.test2)

    I get ERROR: relation "mrclib1.test2" does not exist.

    With double quotes around table name it works. Why?

    SELECT * FROM mrclib1."TEST2"

    I am very new to postgreSQL.

    Thanks.
    Bruce

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by brucejin View Post
    IWith double quotes around table name it works. Why?
    Because you created the table that way.

    SQL is not case sensitive as long as you omit the quotes. The following names are all identical:
    TEST2, test2, Test2, TeSt2

    The following tables are different:
    "TEST2", "test2", "Test2"

    Most probably you ran the following statement to create your table:

    CREATE TABLE "TEST2" (...);

    which made the name case-sensitive.

    The general rule is: never ever use double quotes and you'll never have to worry about names being case-sensitive or not.

    I recommend you also (re-) read the manual regarding this topic:
    http://www.postgresql.org/docs/curre...AX-IDENTIFIERS

  3. #3
    Join Date
    Feb 2010
    Posts
    24
    Thanks!
    Yes pgAdmin quoted the table name when I was creating.
    Now I have recreated it without quotes.
    Thanks
    Bruce

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    When using the pgAdmin GUI when creating database objects, if you use upper case or mixed case in object names, pgAdmin quotes the names.

    If you use only lower case, pgAdmin does NOT quote the name.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Feb 2010
    Posts
    24
    Thanks Lou.
    Field names seem more trouble some.
    If one creates table with this:
    CREATE TABLE tablex ("Field1" char)

    I have to query it like this:

    SELECT "Field1" FROM tablex;

    Not
    SELECT field1 FROM tablex;

    and not

    SELECT Field1 FROM tablex;

    and not

    SELECT "FIELD1" FROM tablex;

    Query become case sensitive!

    How to get around it?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by brucejin View Post
    SELECT "FIELD1" FROM tablex;

    How to get around it?
    Same again: never ever use quotes

    Code:
    CREATE TABLE tablex (Field1 char)
    All object names follow the same rule. It does not matter whether it is a column, table, view, function, sequence or trigger name.

  7. #7
    Join Date
    Feb 2010
    Posts
    24
    Thanks shammat.

    This seems to be unique with PostgreSQL.

    I have written applications for DB2, MS SQL, Oracle, MySQL and I don't remember this double quotes rule with those databases.

    Thanks

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by brucejin View Post
    This seems to be unique with PostgreSQL.
    No. This is what the SQL standard requires.

    I have written applications for DB2, MS SQL, Oracle, MySQL and I don't remember this double quotes rule with those databases.
    Those database behave exactly the same when you use double quotes. There is no difference.

    SQL Server can be configured to always ignore case regardless of any quotes present (thus violating the SQL standard) and in MySQL it depends on the storage engine, the operating system and the system configuration whether it is case sensitive without quotes.

  9. #9
    Join Date
    Feb 2010
    Posts
    24
    If I create table:

    CREATE TABLE tablex ("F1" char(10));

    With other databases I can do query

    SELECT F1 FROM tablex

    But with PostgreSQL I have to do this

    SELECT "F1" FROM tablex

    Otherwise PostgreSQL thinks I am using f1. It seems to translate un-quoted names to lowercase?

    Thanks.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by brucejin View Post
    It seems to translate un-quoted names to lowercase?
    Correct. This is well documented in the manual.

    Most of the other databases fold everything to uppercase - which is what the ANSI standard requires. Again with the exception of MySQL and SQL Server where it depends on the configuration of the system.

    The bottomline is: if you don't use quotes, you do not have to worry about such things.

    That's the reason I strongly recommend to never use double quotes for identifiers.

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    When using pgAdmin, ALWAYS use lower case object names if you use the gui to create the table, and pgAdmin won't add quotes.

    If you use SQL CREATE TABLE statements (and NOT the create table wizard,) don't use quotes, and you'll be fine.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Feb 2010
    Posts
    24
    Thanks a lot!
    I will forget PostgreSQL for 2 days. I just arrived at Portage Wisconsin and will ski through Christmas!
    Happy holidays!

  13. #13
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by loquin View Post
    When using pgAdmin, ALWAYS use lower case object names if you use the gui to create the table, and pgAdmin won't add quotes.
    I think this is a very annoying "feature" of pgAdmin (adding quotes automatically, when the user enters a name with mixed or all upper case).
    As far as I can tell this is one of the biggest sources of problems for people coming from a different DBMS background and it should be turned off in a default installation.

  14. #14
    Join Date
    Feb 2010
    Posts
    24
    Thanks for the warning.
    It is a little confusion for me after programed 5 other databases.

  15. #15
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by brucejin View Post
    Thanks for the warning.
    It is a little confusion for me after programed 5 other databases.
    Again: the others were do not behave differently.

    The difference is, that with a databases that folds everything to uppercase the following will not work:
    Code:
    CREATE TABLE "person" (...);
    SELECT * FROM person;
    The following will work on every database including PostgreSQL
    Code:
    CREATE TABLE "foo" (...);
    SELECT * FROM "foo"; 
    
    CREATE TABLE "BAR" (...);
    SELECT * FROM "BAR";

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •