Thread: table name restiction

PostgreSQL users,
    What are the restrictions on naming tables or columns in tables other
than uniqueness (assuming ascii characters)?  For instance, are names
case sensitive.  What special characters can be used     (`_`,`-`,` `).
  I looked at the docs in the tutorial part in the beginning and in the
description of CREATE TABLE but could not find naming restriction info.
  Could someone point me in the right direction?

Thanks,

Dale

>   I looked at the docs in the tutorial part in the beginning and in the
> description of CREATE TABLE but could not find naming restriction info.
>   Could someone point me in the right direction?

Try section 4.1.1:  Identifiers and Key Words.

In general PostgreSQL's SQL syntax is case-insensitive,
ie, col_name and COL_NAME reference the same column.

However, the default for data comparisons is case-sensitive,
so a value of 'Abc' does not match 'ABC'.  There are some case-insensitive
operators, such as ilike, an extension to the SQL standard.

The issue of case-sensitivity either at the syntax level or the data
level seems to be one that brings out nearly religous ferver when 'discussed'.
--
Mike Nolan

On Mon, Jan 31, 2005 at 03:05:52PM -0600, dale wrote:
>
> What are the restrictions on naming tables or columns in tables
> other than uniqueness (assuming ascii characters)?

See "Identifiers and Key Words" in the "SQL Syntax" chapter of the
documentation:

http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

dale wrote:
> PostgreSQL users,
>     What are the restrictions on naming tables or columns in tables
> other than uniqueness (assuming ascii characters)?  For instance, are
> names case sensitive.  What special characters can be used
> (`_`,`-`,` `).  I looked at the docs in the tutorial part in the
> beginning and in the description of CREATE TABLE but could not find
> naming restriction info.  Could someone point me in the right direction?

As a rule of thumb, stick to all-one-case, a-z,0-9 and _
This should be portable to other database systems. SQL is case
insensitive (although the standard folds to UPPERCASE whereas PG folds
to lower). You can create a case-sensitive table by quoting but you will
need to use quotes in future too.

So this will work (because PG treats them all as "mytable"):
   CREATE TABLE MyTable
   SELECT * FROM MYTABLE
   SELECT * FROM mytable
   SELECT * FROM MyTaBlE
This will not (because the select ends up as "mytable" not "MyTable"):
   CREATE TABLE "MyTable"
   SELECT * FROM MyTable

--
   Richard Huxton
   Archonet Ltd