Thread: Autonumbering Problem?

Hi,

I'm used to MS Access handling the autonumbering all by itself.  I'm not
sure if this issue is common knowledge for both PostGres and MySQL, but
perhaps the reason for my error message has to do with a badly formed
SQL statement.  Here is the output seen in my terminal window:

[arthur@arthur ~]$ psql tco
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

tco=# insert into customers (FullName) values ('Arthur E. Baldwin');
ERROR:  relation "customers" does not exist
tco=# \d
                 List of relations
 Schema |         Name         |   Type   | Owner
--------+----------------------+----------+--------
 public | Customers            | table    | arthur
 public | Customers_CustID_seq | sequence | arthur
(2 rows)

tco=#

What am I doing wrong?

On 8/21/06 12:51 PM, "Arthur Baldwin" <mobilepc@mobilepcclinic.net> wrote:

> Hi,
>
> I'm used to MS Access handling the autonumbering all by itself.  I'm not
> sure if this issue is common knowledge for both PostGres and MySQL, but
> perhaps the reason for my error message has to do with a badly formed
> SQL statement.  Here is the output seen in my terminal window:
>
> [arthur@arthur ~]$ psql tco
> Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> tco=# insert into customers (FullName) values ('Arthur E. Baldwin');
> ERROR:  relation "customers" does not exist
> tco=# \d
>                  List of relations
>  Schema |         Name         |   Type   | Owner
> --------+----------------------+----------+--------
>  public | Customers            | table    | arthur
>  public | Customers_CustID_seq | sequence | arthur
> (2 rows)

Postgresql is case-sensitive.  Try:

insert into "Customers" (FullName) values ('Arthur E. Baldwin');

Note that you have to use double quotes to ensure that the names are not
case-folded to lower case.

Sean

> tco=# insert into customers (FullName) values ('Arthur E. Baldwin');
> ERROR:  relation "customers" does not exist
> tco=# \d
>                  List of relations
>  Schema |         Name         |   Type   | Owner
> --------+----------------------+----------+--------
>  public | Customers            | table    | arthur
>  public | Customers_CustID_seq | sequence | arthur
> (2 rows)
>
> tco=#
>
> What am I doing wrong?

the table in your database has a capital 'C'. I'm guessing your table
fields have capitals as well, so try this

tco=# insert into "Customers" ("FullName") values ('Arthur E. Baldwin');




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Arthur Baldwin schrieb:
> tco=# insert into customers (FullName) values ('Arthur E. Baldwin');
> ERROR:  relation "customers" does not exist
> tco=# \d
>                  List of relations
>  Schema |         Name         |   Type   | Owner
> --------+----------------------+----------+--------
>  public | Customers            | table    | arthur
>  public | Customers_CustID_seq | sequence | arthur
> (2 rows)
>
> What am I doing wrong?
>
Postgresql's handles identifiers case relevant, as the other posters
allready mentioned.
So you created Customers with "C" and wanted to insert into customers
with "c".
The same goes for column names, too.
You can either use double quotes " for all identifiers or stop using
upper case characters in names. Actually I'm not even sure if PG
converts unquoted identifiers to lower or rather to upper case internally.
This behaviour seems to be stupid but AFAIK it conforms to SQL rules and
it is cleaner because you get what you asked for and not what the DBMS
thinks you probaply wanted to ask for but didn't.

I guess you used some GUI tool to create the tables. PGAdmin for example
uses the quotes automatically if you try to create tables/columns with
mixed case names.
That could lead to ambiguities since you are even alowed to have a table
"Customers" besides another one called "customers".

And while we are at it ... PG distinguishes upper/lower case in string
comparisons, too.
You should be aware that MySQL and Access will find 'Arthur', 'arthur',
'ArtHur' if you use SELECT ... WHERE name= 'arthur'.
Postgresql only shows 1 record that matches the criterium exacly.