Обсуждение: Quotes in SQL
Hi--I'm completely new to PostGres... From what I read in the documentation, sql here is same as there.
SELECT * FROM FOO.BAR WHERE FOO.BAR.BAZ='1234';
However, I just finished building my own PostGres and when I try to use it, I have to do stuff like this:
SELECT * FROM "FOO"."BAR" WHERE "FOO"."BAR"."BAZ" = '1234';
Is this a Postgres thing? Or did I compile it wrong? Or is it an option to create_db?
Thanks for your help
RDB
On Mon, May 13, 2002 at 02:49:47PM -0600, Randall Barber wrote: > Hi--I'm completely new to PostGres... From what I read in the documentation, sql here is same as there. > > SELECT * FROM FOO.BAR WHERE FOO.BAR.BAZ='1234'; > > However, I just finished building my own PostGres and when I try to use it, I have to do stuff like this: > > SELECT * FROM "FOO"."BAR" WHERE "FOO"."BAR"."BAZ" = '1234'; > > Is this a Postgres thing? Or did I compile it wrong? Or is it an option to create_db? That's normal. If you use quotes when you create the table, you (usually) need quotes when accessing the fields. If you don't use quotes when creating the table, you don't need them when accessing. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Canada, Mexico, and Australia form the Axis of Nations That > Are Actually Quite Nice But Secretly Have Nasty Thoughts About America
On Tue, May 14, 2002 at 11:31:03AM +1000, Martijn van Oosterhout wrote: > On Mon, May 13, 2002 at 02:49:47PM -0600, Randall Barber wrote: > > Hi--I'm completely new to PostGres... From what I read in the > documentation, sql here is same as there. > > > > SELECT * FROM FOO.BAR WHERE FOO.BAR.BAZ='1234'; > > > > However, I just finished building my own PostGres and when I try to use > it, I have to do stuff like this: > > > > SELECT * FROM "FOO"."BAR" WHERE "FOO"."BAR"."BAZ" = '1234'; > > > > Is this a Postgres thing? Or did I compile it wrong? Or is it an option > to create_db? > > That's normal. If you use quotes when you create the table, you (usually) > need quotes when accessing the fields. If you don't use quotes when creating > the table, you don't need them when accessing. > Huh? Doesn't the parser strip off the quotes? Are you saying it stores the quotes and expects you to provide them when accessing the fields? (Pardon if this seems like an incredibly dumb question.) Paul
Paul M Foster <paulf@quillandmouse.com> writes: >> That's normal. If you use quotes when you create the table, you (usually) >> need quotes when accessing the fields. If you don't use quotes when creating >> the table, you don't need them when accessing. > Huh? Doesn't the parser strip off the quotes? Are you saying it stores > the quotes and expects you to provide them when accessing the fields? No; the above is just a rule of thumb for staying out of case-folding trouble, which is what seems to be biting you. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS and pay particular attention to the footnote ... regards, tom lane
If I am correct, double quotation marks are used to denote an idetifier, such
as a table name or an attribute name. Double quotation marks preserve the
case of the name. In other words:
"foo" == "foo"
foo == "foo"
FOO == "foo"
but "FOO" != "foo"
so, if you create a table like:
create table "FOO" ( bar int );
then you must: select * from "FOO";
not: select * from foo;
I believe that is what he was refering to.
Regards,
Jeff
On Monday 13 May 2002 08:51 pm, Paul M Foster wrote:
> On Tue, May 14, 2002 at 11:31:03AM +1000, Martijn van Oosterhout wrote:
> > On Mon, May 13, 2002 at 02:49:47PM -0600, Randall Barber wrote:
> > > Hi--I'm completely new to PostGres... From what I read in the
> >
> > documentation, sql here is same as there.
> >
> > > SELECT * FROM FOO.BAR WHERE FOO.BAR.BAZ='1234';
> > >
> > > However, I just finished building my own PostGres and when I try to use
> >
> > it, I have to do stuff like this:
> > > SELECT * FROM "FOO"."BAR" WHERE "FOO"."BAR"."BAZ" = '1234';
> > >
> > > Is this a Postgres thing? Or did I compile it wrong? Or is it an
> > > option
> >
> > to create_db?
> >
> > That's normal. If you use quotes when you create the table, you (usually)
> > need quotes when accessing the fields. If you don't use quotes when
> > creating the table, you don't need them when accessing.
>
> Huh? Doesn't the parser strip off the quotes? Are you saying it stores
> the quotes and expects you to provide them when accessing the fields?
> (Pardon if this seems like an incredibly dumb question.)
>
> Paul
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
On Tue, 14 May 2002, Tom Lane wrote: > Paul M Foster <paulf@quillandmouse.com> writes: > >> That's normal. If you use quotes when you create the table, you (usually) > >> need quotes when accessing the fields. If you don't use quotes when creating > >> the table, you don't need them when accessing. > > > Huh? Doesn't the parser strip off the quotes? Are you saying it stores > > the quotes and expects you to provide them when accessing the fields? > > No; the above is just a rule of thumb for staying out of case-folding > trouble, which is what seems to be biting you. See > > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS > > and pay particular attention to the footnote ... The simple rule of thumb to prevent all this is just use lower case for everything. It sure makes my life easier.