75

Simple question, is there any way to omit the double quote in PostgreSQL?

Here is an example, giving select * from A;, I will retrieve ERROR: relation "a" does not exist, and I would have to give select * from "A"; to get the real result.

Is there any way not to do the second and instead do the first on PostgreSQL?

zfm
  • 1,906
  • 2
  • 17
  • 28

5 Answers5

106

Your problem with this query started when you created your table. When you create your table, don't use quotes.

Use this:

CREATE TABLE a ( ... );

Not this:

CREATE TABLE "A" ( ... );

The latter will make it so that you always have to quote it later. The former makes it a normal name and you can use SELECT * FROM a; or SELECT * FROM A;

If you can't just recreate your table, use the ALTER TABLE syntax:

ALTER TABLE "A" RENAME TO a;
Steve Prentice
  • 23,230
  • 11
  • 54
  • 55
  • 12
    I use the table creator wizard, and everytime I write with capital-letter, it will automatically add the doublequote – zfm Jun 13 '11 at 14:40
  • 3
    I've never used the table creator wizard, but I would imagine there is an option in the software to not do that. It's not a very common practice to quote table names in postgres. It just makes your life harder. – Steve Prentice Jun 13 '11 at 14:51
  • 2
    when I said "wizard", it is what is given in `pgAdmin III` – zfm Jun 13 '11 at 16:15
  • 7
    I see. I just checked, and it appears you can't turn this feature off in pgAdmin. I would suggest typing the table names in lower case when creating so that pgAdmin doesn't think the table names need to be case sensitive. – Steve Prentice Jun 13 '11 at 16:26
  • Well, `"A"` is not the same as `a`, sure. But is `"a"` the same as `a`? Instead of “don't use quotes”, I feel the better advice is “don’t use uppercase”. – caw Aug 29 '18 at 17:01
  • @caw behind the scenes postgres converts all object names to lowercase unless they are quoted. When selecting you can use either upper or lowercase and the same conversion will happen.. unless you use quotes. Don't use quotes and your case won't matter. Upper case or lower case. So the advice is to not use quotes for object names and this problem will never show up again. Your suggestion to never use uppercase is similar, but really.... Wouldn't it be nicer to allow any case then it would to allow quotes or not? I think so. – JNevill Nov 30 '18 at 00:44
  • pgadmin 4 is still doing this. for example: personId will become "personId". A SELECT personId FROM XYZ query without the double quote will result in the error "column "personId" does not exist". If you use pgadmin to create your tables don't use capital letters. – TheQuestioner Jun 02 '22 at 15:07
52

double quotes are required if you include capital letters in your table name in postgres

to avoid the requirements name your table "a"

David Chan
  • 7,347
  • 1
  • 28
  • 49
  • 7
    nice answer... Is there any reason why PostgreSQL do that (force us to put doublequotes for a capital-letter table)? – zfm Jun 13 '11 at 14:14
  • 20
    Yes, the reason is "the ANSI standard says so". – PhilHibbs Dec 12 '16 at 13:26
  • 1
    Hi, thank you for your comment. Is there a way to enable/disable the ANSI behavior like MySQL does with `SET SQL_MODE=ANSI_QUOTES;` ? (ref: https://stackoverflow.com/questions/13884854/mysql-double-quoted-table-names) – Yanal-Yves Fargialla Dec 13 '21 at 12:05
40

Postgresql has some particular behaviour in regard to quoting and case sentivity: it folds every non-quoted identifier to lower case (also at creation time) and then works case-sensitively.

Double quotes in identifiers are only needed when the identifier (table name, column name, etc) was defined (at schema creation time) with uppercase letters (some or all) and between double quotes.

In that case (which I advice against), when you use that identifier, you must type it in the same way: case sensitively (type upper/lower case letter exactly as defined) and between double quotes.

In other cases, you can use non-quoted identifiers and work always case-insensitively.

leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • 1
    "...and work in case-unsensitive mode". How to do this exactly? – zfm Jun 13 '11 at 14:41
  • @zfm: as Steve says. You create your identifiers with lower case (or without quotes - it's the same thing), and then you can use them in case-insensitive mode (not quoting them). – leonbloy Jun 13 '11 at 14:44
  • sorry I misinterpret your sentence. I thought that there is an option to change that mode :D – zfm Jun 13 '11 at 15:08
  • @zfm: the sentence was indeed confusing, I changed it. – leonbloy Jun 13 '11 at 15:12
11

Don't use upper case letter in your table name or it's column name, if you are using such thing then the postgres will required double quote for accessing it.

Sandy
  • 827
  • 9
  • 6
5

Please see the detailed description of what is happening here.

The PostgreSQL server table names are case-sensitive, but forced to be lower-case by default: when you type CREATE TABLE AAA, it will become CREATE TABLE aaa before the query execution.

Double-quoted names keep their case as it was, so after CREATE TABLE "AaA" you get the table AaA and have to write it double-quoted again and again.

Have no idea why did they do so :)

Karatheodory
  • 895
  • 10
  • 16
  • I do have an idea why the SQL designers made it case-insensitive: to make it easier for the developers. The real question is why tools and people today seem to insist on double quotes, perhaps to write more impressive code? – Roland Jan 05 '23 at 16:31