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

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
>