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

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:

[cid:image001(dot)png(at)01CF7E3C(dot)BF881DA0]

I then specify columns like this:

[cid:image002(dot)png(at)01CF7E3C(dot)E9D32460]

I then specify a primary key like this:

[cid:image003(dot)png(at)01CF7E3D(dot)C2421810]

I then use PGAdmin’s SQL window to try to access the table:

[cid:image004(dot)png(at)01CF7E3D(dot)C2421810]

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