Re: bug in on_error_rollback !?

I understand that psql commands are not SQL, but since psql is used to
interact with SQL database, then the assumption of case-insensitivity in
psql also comes naturally to the user.

\ds and \dS are commands (first token on the line) so it is acceptable that
they be case-sensitive. But a command's parameters/arguments should not be
case sensitive, unless quoted.

If it is documented that psql commands are case sensitive, then I would like
to point to an ambiguity:

I have two tables:
postgres=# create table "t"( a int);
CREATE TABLE
postgres=# create table "T"( b int);
CREATE TABLE

Now, according to your statement, the \d command should report about two
different tables in the following first two commands:

postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |

postgres=# \d T
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |

postgres=# \d "T"
Table "public.T"
Column | Type | Modifiers
--------+---------+-----------
b | integer |

postgres=#

But, as you can see, I had to d-quote T to get the intended result. IMHO, \d
is behaving correctly, and other '\' commands should treat their
parameters/arguments likewise.

For the more inquisitive (I know you'll go and try the -E switch to psql),
here's what's sent to the backend for the three different \d commands:

postgres=# \d t
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^t$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

postgres=# \d T
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^t$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

postgres=# \d "T"
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^T$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

postgres=#

Regards,

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

On 10/27/06, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> Gurjeet Singh wrote:
> > Thanks ...
> >
> > but case-sensitivity (even without quotes or d-quotes) is the last thing
> > I'd
> > expect in a SQL compliant software. This was highly unexpected. May I
> dare
> > to raise a bug to eliminate case-sensitivity in psql variables? Will I
> get
> > support from the community?
> >
>
>
> psql variables and commands are not SQL, and are case sensitive. For
> example, \ds and \dS are not at all the same.
>
> This is documented clearly on the psql man page, so it is simply not a
> bug, and changing this would probably break lots of legacy scripts.
>
> cheers
>
> andrew
>
>