Relation "xxxxx" does not exist

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?

Thanks,

Valter
----- Original Message -----
From: Valter
To: pgsql-general@postgresql.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

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@intersic.com.br>
*To:* pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.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

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@potentialtech.com>
To: "Valter" <pgsql@intersic.com.br>
Cc: <pgsql-general@postgresql.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@intersic.com.br>
*To:* pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.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: