Thread: SQL: Are the "" around table & coloumnames necessary?

SQL: Are the "" around table & coloumnames necessary?

From

mikey.unsafe@gmx.net (Mikey)

Date:

21 February 2002, 17:59:37

Hello,

when I  write a select for example, 
it doesn't work this way:
  select col1, col2      from my_table;

instead of that I have to write
  select "col1", "col2"      from "my_table";

I dont understand this...is there a way to use
the more comfortable form without "" ?

thx

...Mike

Re: SQL: Are the "" around table & coloumnames necessary?

From

Oliver Elphick

Date:

21 February 2002, 19:36:32

On Mon, 2002-02-18 at 12:43, Mikey wrote:
> Hello,
> 
> when I  write a select for example, 
> it doesn't work this way:
> 
>    select col1, col2 
>       from my_table;
> 
> instead of that I have to write
> 
>    select "col1", "col2" 
>       from "my_table";
> 
> I dont understand this...is there a way to use
> the more comfortable form without "" ?

The two queries you show are identical.  However, if any of the column
names contain upper-case characters and were enclosed in double-quotes
when the table was created, you will get the behaviour you are
complaining about, because all unquoted identifiers are folded to
lower-case before the query is executed.

The solution is not to use upper-case characters in identifiers.
If you already have tables with upper-case names, drop them and recreate
them (or do pg_dumpall, edit the dump file and reload the database).

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "My sheep hear my voice, and I know them, and they      follow me; And I give unto them eternal life; and they
shallnever perish, neither shall any man pluck them      out of my hand."          John 10:27,28

Re: SQL: Are the "" around table & coloumnames necessary?

From

"pauLSiew"

Date:

22 February 2002, 11:39:13

Try not use the SQL keywords as ur table or field name.

"Oliver Elphick" <olly@lfix.co.uk> wrote in message
news:1014327565.3217.862.camel@linda...
> On Mon, 2002-02-18 at 12:43, Mikey wrote:
> > Hello,
> >
> > when I  write a select for example,
> > it doesn't work this way:
> >
> >    select col1, col2
> >       from my_table;
> >
> > instead of that I have to write
> >
> >    select "col1", "col2"
> >       from "my_table";
> >
> > I dont understand this...is there a way to use
> > the more comfortable form without "" ?
>
> The two queries you show are identical.  However, if any of the column
> names contain upper-case characters and were enclosed in double-quotes
> when the table was created, you will get the behaviour you are
> complaining about, because all unquoted identifiers are folded to
> lower-case before the query is executed.
>
> The solution is not to use upper-case characters in identifiers.
> If you already have tables with upper-case names, drop them and recreate
> them (or do pg_dumpall, edit the dump file and reload the database).
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>
>      "My sheep hear my voice, and I know them, and they
>       follow me; And I give unto them eternal life; and they
>       shall never perish, neither shall any man pluck them
>       out of my hand."          John 10:27,28
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)