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(at)darrenduncan(dot)net]
> Sent: Friday, May 30, 2014 5:23 PM
> To: Rob Richardson; pgadmin-support(at)postgresql(dot)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
>