Re: Two novice questions

"Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov> writes:
> On Mon, Jul 14, 2008 at 11:08 AM, Wm.A.Stafford
> <stafford(at)marine(dot)rutgers(dot)edu> wrote:
>> I have received a collection of scripts to be used to build a PostgreSQL db
>> and I can't figure out the following.
>>
>> 1. Usage of quotes in the scripts.
>> The following comes from a script to build and populate a table:
>> gid serial PRIMARY KEY,
>> "fid_" int4,
>> Why is the second column name in quotes, i.e. what is the difference between
>> fid_ int4 and "fid_" int 4.
>>
>> In a similar vein, this comes from a script that creates a couple of
>> tables:
>> create table schema1."cache" ( ...
>> create table schema1.cache2 ( ...
>> Here one schema name is quoted and the other is not.

> The quoting is used to specify a literal name. In particular, if you
> specify something like:

> Name varchar(32)

> Without the quotes, this will be case-folded to all lower-case name.

In the particular examples given here, the quoting is useless --- you'd
get exactly the same results without them. I don't know why "fid_"
got quoted, though possibly that was generated by someone who
incorrectly thought they'd need quotes for an underscore. It's possible
that "cache" was quoted by something that knew CACHE is a Postgres
keyword --- though since it's an unreserved keyword, it doesn't really
need quoting. (If it were a reserved word then it *would* need quotes
to be used as a table name.) Up till pretty recently pg_dump didn't
make that distinction and would quote unreserved keywords, so I can
believe that the use of quotes with "cache" is an artifact of some
old pg_dump output. "fid_" I dunno ...

Anyway, read the manual concerning identifier syntax:
http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

regards, tom lane