« Return to Thread: Does pgAdmin have to double-quote table and field names?
Re: Does pgAdmin have to double-quote table and
by novnov
::
Rate this Message:
Hi Dave and thanks for responding.
I have to worry about the SQL syntax because practically the first thing I did after using pgAdmin to create the tables was to try to write a stored proceedure with pgAdmin against the table. The stored proceedure wouldn't work until I manually added the double quotes. Naturally as a novice I didn't know about needing the double quotes. pgAdmin automatically adds the double quotes at table creation time but does not for the stored proceedures, and the error returned (ERROR relation "item" does not exist) doesn't exactly give one a clue that the issue is missing double quotes. The pgAdmin GUI I'm sure it not supposed to provide complete isolation from SQL syntax...it was an issue for me right away.
The second part of what you wrote adds to my confusion. You're saying that ultimately pgsql is strict about lowercase on everything unless double quoted, tho internally it is looser than that. Essentially then pgAdmin is automatically adding the double quotes to names like Item as a safety measure, knowing that eventually the double quotes will likely be important?
It's confusing because in the other mailing list I seem to have been told simply that the issue I ran into was due to a habit of pgAdmin. In a way you confirm, but clarify that pdAdmin is doing it for 'my own good'?
I have to worry about the SQL syntax because practically the first thing I did after using pgAdmin to create the tables was to try to write a stored proceedure with pgAdmin against the table. The stored proceedure wouldn't work until I manually added the double quotes. Naturally as a novice I didn't know about needing the double quotes. pgAdmin automatically adds the double quotes at table creation time but does not for the stored proceedures, and the error returned (ERROR relation "item" does not exist) doesn't exactly give one a clue that the issue is missing double quotes. The pgAdmin GUI I'm sure it not supposed to provide complete isolation from SQL syntax...it was an issue for me right away.
The second part of what you wrote adds to my confusion. You're saying that ultimately pgsql is strict about lowercase on everything unless double quoted, tho internally it is looser than that. Essentially then pgAdmin is automatically adding the double quotes to names like Item as a safety measure, knowing that eventually the double quotes will likely be important?
It's confusing because in the other mailing list I seem to have been told simply that the issue I ran into was due to a habit of pgAdmin. In a way you confirm, but clarify that pdAdmin is doing it for 'my own good'?
Dave Page-3 wrote:novnov wrote:
> I am having a discussion re pgsql double quoting or not in the General
> mailing list, it's buried in the thread titled "Simple stored procedure
> examples?" As far as I understand things, pgsql ignores case, unless I
> explicitly use surround table and field names etc with double quotes. If I
> use the double quotes then pgsql requires me to use referenece those objects
> using double quotes and the exact case that I did originally. I'm not even
> sure that I'm correct about all of that, but if I am, it seems that pgAdmin
> is adding an uneccessary layer by automaticallly double quoting table and
> field names that are not all lowercase.
pgAdmin is a GUI app that is designed to shield users from
implementation details wherever possible. When you create a table, you
don't need to know the SQL syntax, so why should you know about quoting
issues?
> This is important to me because I want to use namestyles like Item and
> ItemName, and I don't want to have to double quote every table and field
> reference. It seems that pgsql itself is fine with my preferred style but
> that pgAdmin is effectively preventing it? I like pgAdmin otherwise so I'd
> prefer not to have to hunt down a different editor.
in psql you're not using your preferred naming style as PostgreSQL will
be downcasing the names for you unless you quote them. The queries may
work, but as soon as you attach an ODBC app, .NET app, Java app or
whatever, it will see all the names in lowercase.
Regards, Dave.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
« Return to Thread: Does pgAdmin have to double-quote table and field names?
| Free embeddable forum powered by Nabble | Forum Help |
