Обсуждение: PGAdmin for PostgreSQL 9.3 creates tables with double quotes embedded in the name

<div class="WordSection1"><p class="MsoNormal">I just spent half a day trying to set up a log4net AdoNetAppender to
writeinto a PostgreSQL table named “Log”.  It was not working, and I was not getting any error messages anyplace.  Not
myapplication, not PostgreSQL log files.  I stripped the log4net configuration file to a bare minimum, where it merely
writesthe same message every time, using this query:<p class="MsoNormal"> <p class="MsoNormal">INSERT INTO Log
(message)VALUES (‘This is a log message’);<p class="MsoNormal"> <p class="MsoNormal">I could not get it to work.<p
class="MsoNormal"> <pclass="MsoNormal">Finally, in desperation, I opened an SQL window in PGAdmin, and pasted the query
intoit.  It didn’t work!  It claimed there is no relation named Log.  I tried this:<p class="MsoNormal">INSERT INTO log
(message)VALUES ('This is a log message');<p class="MsoNormal">It didn’t work either.<p class="MsoNormal">I tried
this:<pclass="MsoNormal">INSERT INTO “Log” (message) VALUES (‘This is a log message’);<p class="MsoNormal">That
worked!<pclass="MsoNormal"> <p class="MsoNormal">When I was staring at the table definition PGAdmin showed me, I
noticedthat the SQL to create the table began with:<p class="MsoNormal">CREATE TABLE “Log”<p class="MsoNormal">Note the
doublequotes around the word Log.  I came to the conclusion that the double quotes are actually being included in the
tablename.  <p class="MsoNormal"> <p class="MsoNormal">I dropped the table and recreated it manually.  Now, when I look
atthe create table script, I see that it begins with:<p class="MsoNormal">CREATE TABLE Log<p class="MsoNormal">And when
Irun the application that tries to write log messages into that table, it works!<p class="MsoNormal"> <p
class="MsoNormal">Wecannot have double quotes embedded in object names for our applications.  It would be a huge change
inthe way many pieces of our products are configured.  Is there a way to change this behavior, or do we have to revert
toan older version of PostgreSQL?<p class="MsoNormal"> <p class="MsoNormal">Thank you very much!<p
class="MsoNormal"> <pclass="MsoNormal">RobR</div> 

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@iol.ie

Thank you for your reply.  You said, in part:
What's happening in your case is that the table must have been created like this:
      create table "Log" [.....]

But the whole point of my message is that that is not how this table was created.  I did not open an SQL window and
typein the CREATE TABLE command manually.  I selected the database in PGAdmin, went down to the Tables list (which said
Ihad 0 table), right clicked on the word "Tables", and selected "New table".  In the new table dialog I entered the
tablename as Log (no quotes), I defined its columns and its primary key, and I clicked OK.  After I did all that, I
thenfound that "select * from Log" (no quotes around Log) failed, claiming there was no relation named "Log".  I then
foundthat "select * from "Log" " (quotes around Log) worked.  I then concluded that the table was being created with
thedouble quotes included in the table name. 

I did find one place where I actually saw the name referred to as '"Log"'.  That's the word Log, surrounded by double
quotes,and then surrounded again by single quotes.  Unfortunately, I do not now remember where that was. 

RobR

Rob, this is what you do.

Either:

1. Use plain lowercase for all of your tablevar and other entity names, meaning 
it will work both quoted and unquoted in all contexts.

2. If you use any uppercase, then if writing SQL you use double-quotes around 
said entity nmes everywhere they appear.

See, SQL entities are case-sensitive in the general case of being quoted, but in 
the special case of not quoting the names in SQL, they are folded to lowercase 
before being matched.

If this is all complicated to you, then just use straight lowercase everywhere 
and it will just work.

-- Darren Duncan

On 2014-05-30, 1:56 PM, Rob Richardson wrote:
> Thank you for your reply.  You said, in part:
>
>     What's happening in your case is that the table must have been created like this:
>
>           create table "Log" [.....]
>
> But the whole point of my message is that that is not how this table was created.  I did not open an SQL window and
typein the CREATE TABLE command manually.  I selected the database in PGAdmin, went down to the Tables list (which said
Ihad 0 table), right clicked on the word "Tables", and selected "New table".  In the new table dialog I entered the
tablename as Log (no quotes), I defined its columns and its primary key, and I clicked OK.  After I did all that, I
thenfound that "select * from Log" (no quotes around Log) failed, claiming there was no relation named "Log".  I then
foundthat "select * from "Log" " (quotes around Log) worked.  I then concluded that the table was being created with
thedouble quotes included in the table name.
 
>
> I did find one place where I actually saw the name referred to as '"Log"'.  That's the word Log, surrounded by double
quotes,and then surrounded again by single quotes.  Unfortunately, I do not now remember where that was.
 
>
> RobR

RobR wrote
> I selected the database in PGAdmin, went down to the Tables list (which
> said I had 0 table), right clicked on the word "Tables", and selected "New
> table".  In the new table dialog I entered the table name as Log (no
> quotes)

Enter the table name in lower-case.  If you cannot surround your references
to identifiers with double-quotes in your applications you must define all
identifiers using lower-case only - so that after all identifiers in your
application are converted to lower-case they will match up with those stored
in PostgreSQL .  PgAdmin issues its SQL in such a manner that what you enter
is considered case-sensitive so you must do this manually if you rely on
pgAdmin to perform your DDL.

Note that all pgAdmin does is issue SQL against the database, and the SQL it
issues indeed preserves case by putting identifiers in double-quotes.  Those
quotes, though, are strictly instructive to the parser of whether case is to
be preserved.  Any identifiers lacking quotes are automatically converted to
lower-case first, and then that lower-case identifier is matched against the
database in a literal fashion.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PGAdmin-for-PostgreSQL-9-3-creates-tables-with-double-quotes-embedded-in-the-name-tp5805606p5805645.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

RobR wrote
>  I then concluded that the table was being created with the double quotes
> included in the table name.

In short:

log = "log"
Log = "log"
Log = log
LoG = lOg
Log <> "Log"

Again, this is all just parser stuff.  Whatever is present post-parse on a
create table is stored, as-is, in the database and then subsequent queries
get parsed and matched literally against what is stored.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PGAdmin-for-PostgreSQL-9-3-creates-tables-with-double-quotes-embedded-in-the-name-tp5805606p5805646.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

<div class="WordSection1"><p class="MsoPlainText">Darren,<p class="MsoPlainText"> <p class="MsoPlainText">You are not
listeningto me.<p class="MsoPlainText"> <p class="MsoPlainText">If I was using plain SQL, entered manually, I would use
alllower-case characters.<p class="MsoPlainText"> <p class="MsoPlainText">I am not using plain SQL entered manually.  I
amusing PGAdmin's table creation tool.  That is why this thread was started in the PGAdminSupport group instead of
pg_general. My table creation process starts here:<p class="MsoPlainText"> <p class="MsoPlainText"><img height="510"
id="Picture_x0020_1"src="cid:image001.png@01CF7E3C.BF881DA0" width="741" /><p class="MsoPlainText"> <p
class="MsoPlainText">Ithen specify columns like this:<p class="MsoPlainText"> <p class="MsoPlainText"><img height="510"
id="Picture_x0020_2"src="cid:image002.png@01CF7E3C.E9D32460" width="741" /><p class="MsoPlainText"> <p
class="MsoPlainText">Ithen specify a primary key like this:<p class="MsoPlainText"> <p class="MsoPlainText"><img
height="510"id="Picture_x0020_3" src="cid:image003.png@01CF7E3D.C2421810" width="741" /><p class="MsoPlainText"> <p
class="MsoPlainText">Ithen use PGAdmin’s SQL window to try to access the table:<p class="MsoPlainText"> <p
class="MsoPlainText"><imgheight="547" id="Picture_x0020_4" src="cid:image004.png@01CF7E3D.C2421810" width="1155" /><p
class="MsoPlainText"> <pclass="MsoPlainText">In this case, as I was afraid of, the query worked, telling me there was
nodata in the table.  That is not the result I was seeing last week.<p class="MsoPlainText"> <p
class="MsoPlainText">Pleasedo not insult my intelligence by telling me that I created the table I was asking about in
someway other than the one shown above.  The method shown above is how I created the table.  I repeat:  I did not use a
manuallytyped SQL statement to create the table.<p class="MsoPlainText"> <p class="MsoPlainText">When I first created
thetable, I named it “Log”.  My first guess was that that was a reserved word, and PostgreSQL automatically wrapped it
indouble quotes to make sure that the word would be handled as a name rather than as a keyword.  I believe that that
muchis correct.  But then I changed name to “AnotherLog”, and I still had the same problem.  Then I dropped the table
andrecreated it using a name like “ThisIsALogTableButItsNameIsNotAKeyword”.  I still had the same problem!  Finally, I
hadto drop the table created using PGAdmin and use the SQL query window to manually enter a CREATE TABLE query.<p
class="MsoPlainText"> <pclass="MsoPlainText">I am relieved to see that I could not recreate the problem now, since that
meansthat my company will not have to change its normal practices when creating databases, but I would still like to
understandexactly what happened so that I can avoid it in the future.  And if this is a bug in PGAdmin, perhaps it can
befixed.<p class="MsoPlainText"> <p class="MsoPlainText">Rob Richardson<p class="MsoPlainText">Rad-Con, Incorproated<p
class="MsoPlainText"> <pclass="MsoPlainText">-----Original Message-----<br /> From: Darren Duncan
[mailto:darren@darrenduncan.net]<br /> Sent: Friday, May 30, 2014 5:23 PM<br /> To: Rob Richardson;
pgadmin-support@postgresql.org<br/> Subject: Re: [pgadmin-support] PGAdmin for PostgreSQL 9.3 creates tables with
doublequotes embedded in the name<p class="MsoPlainText"> <p class="MsoPlainText">Rob, this is what you do.<p
class="MsoPlainText"> <pclass="MsoPlainText">Either:<p class="MsoPlainText"> <p class="MsoPlainText">1. Use plain
lowercasefor all of your tablevar and other entity names, meaning it will work both quoted and unquoted in all
contexts.<pclass="MsoPlainText"> <p class="MsoPlainText">2. If you use any uppercase, then if writing SQL you use
double-quotesaround said entity nmes everywhere they appear.<p class="MsoPlainText"> <p class="MsoPlainText">See, SQL
entitiesare case-sensitive in the general case of being quoted, but in the special case of not quoting the names in
SQL,they are folded to lowercase before being matched.<p class="MsoPlainText"> <p class="MsoPlainText">If this is all
complicatedto you, then just use straight lowercase everywhere and it will just work.<p class="MsoPlainText"> <p
class="MsoPlainText">--Darren Duncan<p class="MsoPlainText"> <p class="MsoPlainText">On 2014-05-30, 1:56 PM, Rob
Richardsonwrote:<p class="MsoPlainText">> Thank you for your reply.  You said, in part:<p
class="MsoPlainText">> <pclass="MsoPlainText">>             What's happening in your case is that the table must
havebeen created like this:<p class="MsoPlainText">> <p class="MsoPlainText">>                            
  createtable "Log" [.....]<p class="MsoPlainText">> <p class="MsoPlainText">> But the whole point of my message
isthat that is not how this table was created.  I did not open an SQL window and type in the CREATE TABLE command
manually. I selected the database in PGAdmin, went down to the Tables list (which said I had 0 table), right clicked on
theword "Tables", and selected "New table".  In the new table dialog I entered the table name as Log (no quotes), I
definedits columns and its primary key, and I clicked OK.  After I did all that, I then found that "select * from Log"
(noquotes around Log) failed, claiming there was no relation named "Log".  I then found that "select * from "Log" "
(quotesaround Log) worked.  I then concluded that the table was being created with the double quotes included in the
tablename.<p class="MsoPlainText">> <p class="MsoPlainText">> I did find one place where I actually saw the name
referredto as '"Log"'.  That's the word Log, surrounded by double quotes, and then surrounded again by single quotes. 
Unfortunately,I do not now remember where that was.<p class="MsoPlainText">> <p class="MsoPlainText">> RobR<p
class="MsoPlainText"> <pclass="MsoPlainText"> </div> 

Hi Rob,

Monday, June 2, 2014, 4:43:01 PM, you wrote:

RR> I am not using plain SQL entered manually.═ I am using
RR> PGAdmin's table creation tool.

I'm sure that last week you provided table name in mixed case (more
precisely, with at least one character not in [_a-z0-9]). PgAdmin saw
that and wrapped it in double quotes. If all the letters are
lowercase, no double quotes are added.
By the way, this relates to any identifiers, not only table names.
Isn't that mentioned in PgAdmin help?.. If not, it probably should.

-- 
Best regards,J.F.

Rob, I was listening to you just fine; you weren't listening to me.

Your problem was due to you using uppercase letters in your names when you 
created them.

I never at any time said you used a manually written SQL statement to create the 
table; my comment about writing SQL referred to later on, when you were *using* 
the table you had already created, per your examples.

-- Darren Duncan

On 2014-06-02, 5:43 AM, Rob Richardson wrote:
> Darren,
>
> You are not listening to me.
>
> If I was using plain SQL, entered manually, I would use all lower-case characters.
>
> I am not using plain SQL entered manually.  I am using PGAdmin's table creation
> tool.  That is why this thread was started in the PGAdminSupport group instead
> of pg_general.  My table creation process starts here:
>
> I then specify columns like this:
>
> I then specify a primary key like this:
>
> I then use PGAdmin’s SQL window to try to access the table:
>
> In this case, as I was afraid of, the query worked, telling me there was no data
> in the table.  That is not the result I was seeing last week.
>
> Please do not insult my intelligence by telling me that I created the table I
> was asking about in some way other than the one shown above.  The method shown
> above is how I created the table.  I repeat:  I did not use a manually typed SQL
> statement to create the table.
>
> When I first created the table, I named it “Log”.  My first guess was that that
> was a reserved word, and PostgreSQL automatically wrapped it in double quotes to
> make sure that the word would be handled as a name rather than as a keyword. I
> believe that that much is correct.  But then I changed name to “AnotherLog”, and
> I still had the same problem.  Then I dropped the table and recreated it using a
> name like “ThisIsALogTableButItsNameIsNotAKeyword”.  I still had the same
> problem!  Finally, I had to drop the table created using PGAdmin and use the SQL
> query window to manually enter a CREATE TABLE query.
>
> I am relieved to see that I could not recreate the problem now, since that means
> that my company will not have to change its normal practices when creating
> databases, but I would still like to understand exactly what happened so that I
> can avoid it in the future.  And if this is a bug in PGAdmin, perhaps it can be
> fixed.
>
> Rob Richardson
>
> Rad-Con, Incorproated
>
> -----Original Message-----
> From: Darren Duncan [mailto:darren@darrenduncan.net]
> Sent: Friday, May 30, 2014 5:23 PM
> To: Rob Richardson; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] PGAdmin for PostgreSQL 9.3 creates tables with
> double quotes embedded in the name
>
> Rob, this is what you do.
>
> Either:
>
> 1. Use plain lowercase for all of your tablevar and other entity names, meaning
> it will work both quoted and unquoted in all contexts.
>
> 2. If you use any uppercase, then if writing SQL you use double-quotes around
> said entity nmes everywhere they appear.
>
> See, SQL entities are case-sensitive in the general case of being quoted, but in
> the special case of not quoting the names in SQL, they are folded to lowercase
> before being matched.
>
> If this is all complicated to you, then just use straight lowercase everywhere
> and it will just work.
>
> -- Darren Duncan
>
> On 2014-05-30, 1:56 PM, Rob Richardson wrote:
>
>  > Thank you for your reply.  You said, in part:
>
>  >
>
>  >             What's happening in your case is that the table must have been
> created like this:
>
>  >
>
>  >                               create table "Log" [.....]
>
>  >
>
>  > But the whole point of my message is that that is not how this table was
> created.  I did not open an SQL window and type in the CREATE TABLE command
> manually.  I selected the database in PGAdmin, went down to the Tables list
> (which said I had 0 table), right clicked on the word "Tables", and selected
> "New table".  In the new table dialog I entered the table name as Log (no
> quotes), I defined its columns and its primary key, and I clicked OK.  After I
> did all that, I then found that "select * from Log" (no quotes around Log)
> failed, claiming there was no relation named "Log".  I then found that "select *
> from "Log" " (quotes around Log) worked.  I then concluded that the table was
> being created with the double quotes included in the table name.
>
>  >
>
>  > I did find one place where I actually saw the name referred to as '"Log"'.
> That's the word Log, surrounded by double quotes, and then surrounded again by
> single quotes.  Unfortunately, I do not now remember where that was.
>
>  >
>
>  > RobR
>

On Mon, Jun 2, 2014 at 7:09 PM, David G Johnston
<david.g.johnston@gmail.com> wrote:
>>
>> I
>> f all the letters are lowercase, no double quotes are added.
>
>
> I don't know if this statement is actually true but it makes no difference
> in reality whether the double-quotes are added in the case where only
> lowercase letters and numbers/underscore are used.  As far the executor is
> concerned the same exact identifier text will be used.  Likely, and for
> reasoning purposes, it is sufficient to consider that ALL identifiers
> generated by pgAdmin are surrounded with double-quotes in the resultant SQL
> statements that are sent to the server.

Correct. With the exception of the query tool (for obvious reasons),
pgAdmin is designed to handle all the implementation details of SQL
generation etc. for you. Therefore, if you use mixed-case in any of
the object properties dialogues for values that are not expected to be
expressions, it will automatically quote them for you (in the correct
way, as sometimes the quoting rules are different depending on the
situation - e.g. comment strings vs. identifiers). So, entering a
table name of Log will result in:

CREATE TABLE "Log" ...

whilst entering a table name of log will give:

CREATE TABLE log ...

In the latter case, pgAdmin will omit the quotes as they are not
necessary. You can see what it's doing by looking at the SQL tab on
any properties dialogue.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company