pg_query() and double quotes

[Google Mike]

I'm discovering that my pg_query isn't working because PostgreSQL is
wanting double quotes around my column names and table names. Is there
a way to turn quoted identifiers off?

[Michael Fuhr]

Re: pg_query() and double quotes

googlemike@hotp op.com (Google Mike) writes:
[color=blue]
> I'm discovering that my pg_query isn't working because PostgreSQL is
> wanting double quotes around my column names and table names. Is there
> a way to turn quoted identifiers off?[/color]

PostgreSQL should require quotes around identifiers only when they're
identical to reserved key words[1] or if they contain characters
that aren't usually allowed in identifier names[2]. If your
identifiers use such names then you'll have to quote them. If
that's not the problem, then could you give an example of what
you're doing and what error(s) you're getting?

[1] http://www.postgresql.org/docs/curre...-appendix.html
[2] http://www.postgresql.org/docs/curre...AX-IDENTIFIERS

--
Michael Fuhr

http://www.fuhr.org/~mfuhr/

[ljb]

Re: pg_query() and double quotes

mfuhr@fuhr.org wrote:[color=blue]
> googlemike@hotp op.com (Google Mike) writes:
>[color=green]
>> I'm discovering that my pg_query isn't working because PostgreSQL is
>> wanting double quotes around my column names and table names. Is there
>> a way to turn quoted identifiers off?[/color]
>
> PostgreSQL should require quotes around identifiers only when they're
> identical to reserved key words[1] or if they contain characters
> that aren't usually allowed in identifier names[2]. If your
> identifiers use such names then you'll have to quote them. If
> that's not the problem, then could you give an example of what
> you're doing and what error(s) you're getting?[/color]

Add one more reason: case sensitivity. If you created your tables with
double-quoted names (table or column), then they become case sensitive, and
can generally only be accessed with double-quoted names which match the
original names in case.

[Michael Fuhr]

Re: pg_query() and double quotes

ljb <ljb220@mindspr ing.nospam.com> writes:
[color=blue]
> mfuhr@fuhr.org wrote:[color=green]
> > googlemike@hotp op.com (Google Mike) writes:
> >[color=darkred]
> >> I'm discovering that my pg_query isn't working because PostgreSQL is
> >> wanting double quotes around my column names and table names. Is there
> >> a way to turn quoted identifiers off?[/color]
> >
> > PostgreSQL should require quotes around identifiers only when they're
> > identical to reserved key words[1] or if they contain characters
> > that aren't usually allowed in identifier names[2]. If your
> > identifiers use such names then you'll have to quote them. If
> > that's not the problem, then could you give an example of what
> > you're doing and what error(s) you're getting?[/color]
>
> Add one more reason: case sensitivity. If you created your tables with
> double-quoted names (table or column), then they become case sensitive, and
> can generally only be accessed with double-quoted names which match the
> original names in case.[/color]

Ah yes, I forgot about that situation, and it might be more likely
than the cases I cited. Thanks for pointing it out.

--
Michael Fuhr

http://www.fuhr.org/~mfuhr/

[Google Mike]

Re: pg_query() and double quotes

Thanks. That's what I did, Michael. I was using the pgAdminIII tool to
define the tables, and it defaults to quoting table and column names,
forcing case-sensitivity. Since I like that tool, I decided that the
solution for me was to just lower-case everything when I created it.
Then, from PHP, I call everything in lowercase. It's still
case-sensitive, but it's more user-friendly to me. For other people,
however, I'll recommend that they create their tables and columns
without quoting them so that they are no longer case-sensitive.