SEO

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-h....html#a7177095
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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/s...AX-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

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/s...AX-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-h....html#a7177919
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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/s...AX-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-h....html#a7188642
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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-h....html#a7189127
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

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-h....html#a7191015
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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-h....html#a7243176
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


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

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