Does pgAdmin have to double-quote table and field names?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Does pgAdmin have to double-quote table and field names?

novnovice
As far as I can tell pgsql itself does not require table and field names to be wrapped in double quotes, but pgAdmin does that by default (at least on windows). What's the rationale? I've not found a way to turn this off in pgAdmin, is there a way, is it safe to do this?

It's a very nice package, btw, thanks to all of the contributors.
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

Dave Page-3
novnov wrote:
> As far as I can tell pgsql itself does not require table and field names to
> be wrapped in double quotes, but pgAdmin does that by default (at least on
> windows).

PostgreSQL does require you to use double quotes in some circumstances
(for example, if you use upper case letters). pgAdmin automatically
quotes identifiers if you use a name that requires them.

 > What's the rationale? I've not found a way to turn this off in
> pgAdmin, is there a way, is it safe to do this?

No, you cannot turn it off, and yes, it is safe as long as you remember
to quote things appropriately outside of pgAdmin. See
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS 
for more info.

> It's a very nice package, btw, thanks to all of the contributors.

You're welcome.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

novnovice
I see, so to make it explicit, if I'd named my table item not Item, and the column itemname not ItemName, pgAdmin (and pgsql) wouldn't need to double quote. Not my preference but I'll live.

Thanks

Dave Page-3 wrote
novnov wrote:
> As far as I can tell pgsql itself does not require table and field names to
> be wrapped in double quotes, but pgAdmin does that by default (at least on
> windows).

PostgreSQL does require you to use double quotes in some circumstances
(for example, if you use upper case letters). pgAdmin automatically
quotes identifiers if you use a name that requires them.

 > What's the rationale? I've not found a way to turn this off in
> pgAdmin, is there a way, is it safe to do this?

No, you cannot turn it off, and yes, it is safe as long as you remember
to quote things appropriately outside of pgAdmin. See
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS 
for more info.

> It's a very nice package, btw, thanks to all of the contributors.

You're welcome.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

novnovice
In reply to this post by Dave Page-3
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.

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.


Dave Page-3 wrote
novnov wrote:
> As far as I can tell pgsql itself does not require table and field names to
> be wrapped in double quotes, but pgAdmin does that by default (at least on
> windows).

PostgreSQL does require you to use double quotes in some circumstances
(for example, if you use upper case letters). pgAdmin automatically
quotes identifiers if you use a name that requires them.

 > What's the rationale? I've not found a way to turn this off in
> pgAdmin, is there a way, is it safe to do this?

No, you cannot turn it off, and yes, it is safe as long as you remember
to quote things appropriately outside of pgAdmin. See
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS 
for more info.

> It's a very nice package, btw, thanks to all of the contributors.

You're welcome.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

Dave Page-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

novnovice
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'?



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
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

Dave Page-3
novnov wrote:

> 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?

Anywhere you type raw SQL in pgAdmin, you need to quote manually. In
other words, if you manually type SQL into the Query Tool, or in a
function body. pgAdmin doesn't try to second guess what you've typed in
these cases - to to so would likely cause more problems.

In the GUI elements of pgAdmin, quoting is always added if you use an
identifier that requires it. In these cases it knows exasctly what you
are typing because you've typed it into the 'name' textbox for example.

psql is essentially the same as the pgAdmin query tool in that you must
do everything yourself.

> 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'?

The point of a graphical interface is to simplify complex tasks for you.
In this case that means giving you a set of textboxes, comboboxes and
other widgets with which you specify the object you want to create. It
shields you from the details of how that object is created, because in
the vast majority of cases people don't care (in pgAdmin's case you
actually can see the raw SQL if desired by looking at the SQL tab on the
properties dialogues).

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

novnovice
Thanks again.

Is there anything like SOP for pgsql devs then? Do most stick to lowercase everywhere, and skip the issues, or do they keep the namestyles like "ItemName" and just accomodate the need to double quote when it arises? I am sure good devs could go either way, but curious if you or other have any impressions re common practice. I'd guess most decide to use the "ItemName" style, since double quoting would only be an issue under some circumstances (like writing sprocs), and double quotes aren't a heavy burden anyways.


Dave Page-3 wrote
novnov wrote:
> 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?

Anywhere you type raw SQL in pgAdmin, you need to quote manually. In
other words, if you manually type SQL into the Query Tool, or in a
function body. pgAdmin doesn't try to second guess what you've typed in
these cases - to to so would likely cause more problems.

In the GUI elements of pgAdmin, quoting is always added if you use an
identifier that requires it. In these cases it knows exasctly what you
are typing because you've typed it into the 'name' textbox for example.

psql is essentially the same as the pgAdmin query tool in that you must
do everything yourself.

> 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'?

The point of a graphical interface is to simplify complex tasks for you.
In this case that means giving you a set of textboxes, comboboxes and
other widgets with which you specify the object you want to create. It
shields you from the details of how that object is created, because in
the vast majority of cases people don't care (in pgAdmin's case you
actually can see the raw SQL if desired by looking at the SQL tab on the
properties dialogues).

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote table and

Erwin Brandstetter-4
novnov wrote:
> Is there anything like SOP for pgsql devs then? Do most stick to lowercase
> everywhere, and skip the issues, or do they keep the namestyles like
> "ItemName" and just accomodate the need to double quote when it arises? I am
> sure good devs could go either way, but curious if you or other have any
> impressions re common practice. I'd guess most decide to use the "ItemName"
> style, since double quoting would only be an issue under some circumstances
> (like writing sprocs), and double quotes aren't a heavy burden anyways.
>


I would generally stick to lower case names. Just like I would generally
avoid non-ASCII characters in names. I certainly do, I think most do.
Makes your life easier.


Regards
Erwin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Does pgAdmin have to double-quote (now namestyles)

novnovice
I will be asking this on the pgsql general list too but if lowercase is best, I'll have to change some habits I've developed over the years, want to glean whatever collective wisdom I can here from experienced pgsql devs.

I've been using namestyles with mixed case like OrgID. That is much more readable than orgid. I know many would use org_id. That change I can live with.

But another issue is the way that I've been naming foreign key references. I tend to prefix field names with a table based acryonym. So names like OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, if I have a table tblEmployee, fk to the org table would be like EmpOrgID. I know many would simply use OrgID as the fk in tblEmployee, but I have liked compounding the names because is results in completly unambiguous field names throughout the db. If I'm giving up the mixed case naming, I could use emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best.

I am very curious to hear the various conventions folks here have arrived at. I don't expect there to be consensus, but the various rationales might help me arrive at an approach that works well for me.


brsaweda wrote
novnov wrote:
> Is there anything like SOP for pgsql devs then? Do most stick to lowercase
> everywhere, and skip the issues, or do they keep the namestyles like
> "ItemName" and just accomodate the need to double quote when it arises? I am
> sure good devs could go either way, but curious if you or other have any
> impressions re common practice. I'd guess most decide to use the "ItemName"
> style, since double quoting would only be an issue under some circumstances
> (like writing sprocs), and double quotes aren't a heavy burden anyways.
>


I would generally stick to lower case names. Just like I would generally
avoid non-ASCII characters in names. I certainly do, I think most do.
Makes your life easier.


Regards
Erwin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq