Re: Relation "xxxxx" does not exist - more info

Bill,

I found in docs that identifier names in Postgre behave inconsistenly from
another servers.

In postgre FOO,Foo, foo are equals to "foo" while in others database they
are equals to "FOO".

This behavior prevents the use of DTS to direct transfer tables from SQL
Server to Postgre. First it is necessary create tables with non-quoted
identifiers and then tranfer them (Luckly DTS uses quotes just to create
tables and not to data transfer).

The whole problem is not so bad except for pgAdmin behavior that should work
like psql.

thanks,

Valter

----- Original Message -----
From: "Bill Moran" <wmoran(at)potentialtech(dot)com>
To: "Valter" <pgsql(at)intersic(dot)com(dot)br>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, March 17, 2004 3:46 PM
Subject: Re: [GENERAL] Relation "xxxxx" does not exist - more info

> Valter wrote:
> > What I didn't perceived before is DTS and pgAdmin create tables using
> > quoted names like:
> >
> > CREATE TABLE "XYZ" ....
> >
> > while I create tables using non-quoted names like CREATE TABLE ABC.
> >
> > It happens that altough invisible to \dt am I unable to use these tables
> > either in psql or DTS.
> >
> > In sum, DTS creates the table but doesn't transfer data.
> >
> > Is quoted names a matter of postgre config?
>
> Not sure if I'm answering the question you're asking, but I figure I'll
> give a try.
>
> Per the SQL standards, names are normally case-insensitive, if they are
> quoted, then they are case-sensitive.
>
> For example, if you do:
>
> create table Test1ForMe ...
>
> All of the following will work:
>
> Select * from test1forme
> select * from TEST1FORME
> select * from Test1ForMe
>
> ... and any combination of caps/small in addition. However, the following
> _will_not_ work
>
> select * from "Test1ForMe"
>
> because Postgres folds the case to all lowers if the identifier is not
quoted.
>
> In addition, if you do the following:
>
> create table "Test1ForMe" ...
>
> The ONLY way to access that table is to use "Test1ForMe", because that is
the
> only way that Postgres will maintain and match the case differences.
>
> Hope this helps.
>
> >
> > Thanks,
> >
> > Valter
> >
> > ----- Original Message -----
> > *From:* Valter <mailto:pgsql(at)intersic(dot)com(dot)br>
> > *To:* pgsql-general(at)postgresql(dot)org
> > <mailto:pgsql-general(at)postgresql(dot)org>
> > *Sent:* Wednesday, March 17, 2004 2:52 PM
> > *Subject:* [GENERAL] Relation "xxxxx" does not exist
> >
> > When I create tables thru DTS (mssqlserver) or pgAdmin, I get the
> > following message when I try select/insert row from/into this
tables:
> >
> > Relation "table-name" does not exist
> >
> > Command \dt lists all the tables, but \dt PATTERN doesn't.
> >
> > Could it be charset/sort order or something like that?
> >
> > All client softwares but pgsql are running on windows2k
> >
> > Valter
>
>
> --
> Bill Moran
> Potential Technologies
> http://www.potentialtech.com