Re: [ADMIN] Fwd: Re: fun with postgresql

On Fri, Jan 28, 2000 at 11:38:49AM -0500, Ruben Fagundo wrote:
> Does anyone have any idea why this happened ?
>
> I'm having trouble with postgresql. My script tries to do this:
>
> my $sql = "insert into people (firstName, lastName, Email)
> values ($V{'firstname'}, $V{'lastname'}, $V{'MAILTO'})";
>
> but it gets the error: DBD::Pg::st execute failed: ERROR: Attribute don not
> found . 'don' is the firstName value that I'm trying to insert. Even in
> psql I get similar weirdness:
>
> postgres=> select firstName, lastName, Email from people \g
> ERROR: attribute 'firstname' not found
> postgres=> select firstName from people \g
> ERROR: attribute 'firstname' not found
> postgres=> select Email from people \g
> ERROR: attribute 'email' not found

To make this work, try:

select "firstName", "lastName", "Email" from people ;

>
> But 'select * from people' works.
>
> What am I missing? Is there a case-sensitivity issue? I'm using the case
> for the field names that I see with a '\d people'

Right, it's the SQL quoting rules. A bare name is taken to mean an
identifier (table name or field name), which is then downcased before
lookup. Quoting with double quotes (") indicates an identifier that is
used directly, without downcasing (or validation against reserved words:
you can make very odd tablenames that way). All string values need to be
quoted with single quotes (')

idas=> select test;
ERROR: attribute 'test' not found
idas=> select 'test';
?column?
--------
test
(1 row)

idas=>

I don't know perl, but you need to get the single quotes around all the
values.

Hope this helps,

Ross

P.S. This question probably belongs on the interfaces list, not admin.
--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005