Обсуждение: Does pgAdmin have to double-quote table and field names?
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. -- View this message in context: http://www.nabble.com/Does-pgAdmin-have-to-double-quote-table-and-field-names--tf2574512.html#a7177095 Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.
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
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 > > -- View this message in context: http://www.nabble.com/Does-pgAdmin-have-to-double-quote-table-and-field-names--tf2574512.html#a7177919 Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.
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 > > -- View this message in context: http://www.nabble.com/Does-pgAdmin-have-to-double-quote-table-and-field-names--tf2574512.html#a7188642 Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.
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.
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 > > -- View this message in context: http://www.nabble.com/Does-pgAdmin-have-to-double-quote-table-and-field-names--tf2574512.html#a7189127 Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.
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.
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 > > -- View this message in context: http://www.nabble.com/Does-pgAdmin-have-to-double-quote-table-and-field-names--tf2574512.html#a7191015 Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.
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
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 > > -- View this message in context: http://www.nabble.com/Does-pgAdmin-have-to-double-quote-table-and-field-names--tf2574512.html#a7243176 Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.