Re: PGAdmin for PostgreSQL 9.3 creates tables with double quotes embedded in the name

On 30/05/2014 20:49, Rob Richardson wrote:
> I just spent half a day trying to set up a log4net AdoNetAppender to
> write into a PostgreSQL table named “Log”. It was not working, and I
> was not getting any error messages anyplace. Not my application, not
> PostgreSQL log files. I stripped the log4net configuration file to a
> bare minimum, where it merely writes the same message every time, using
> this query:
>
> INSERT INTO Log (message) VALUES (‘This is a log message’);
>
> I could not get it to work.
>
> Finally, in desperation, I opened an SQL window in PGAdmin, and pasted
> the query into it. It didn’t work! It claimed there is no relation
> named Log. I tried this:
>
> INSERT INTO log (message) VALUES ('This is a log message');
>
> It didn’t work either.
>
> I tried this:
>
> INSERT INTO “Log” (message) VALUES (‘This is a log message’);
>
> That worked!
>
> When I was staring at the table definition PGAdmin showed me, I noticed
> that the SQL to create the table began with:
>
> CREATE TABLE “Log”
>
> Note the double quotes around the word Log. I came to the conclusion
> that the double quotes are actually being included in the table name.

Actually, this isn't what's happening. Rather, table names in Postgres
are automatically folded to lower case, unless you surround them with
double-quotes - in the latter case they're treated as case-sensitive.

What's happening in your case is that the table must have been created
like this:

create table "Log" [.....]

- so the table name is Log, not log. Then, when you this -

insert into log [....]

- there's no table named log, hence the error.

> I dropped the table and recreated it manually. Now, when I look at the
> create table script, I see that it begins with:
>
> CREATE TABLE Log
>
> And when I run the application that tries to write log messages into
> that table, it works!

Yes, what happened here is that the table name was folded to lower-case,
so this command was the same as this:

CREATE TABLE log [....]

> We cannot have double quotes embedded in object names for our
> applications. It would be a huge change in the way many pieces of our
> products are configured. Is there a way to change this behavior, or do
> we have to revert to an older version of PostgreSQL?

This case-folding behaviour has been standard in PostgreSQL for a very
long time; certainly since I started using PostgreSQL (about version 7.3
I think).

Anyway, short version - there aren't any double-quotes in the table
names; instead, using double-quoted forces case-sensitivity in the table
names.

I hope this helps clarify things!

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie