Thread: 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
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
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: ValterSent: Wednesday, March 17, 2004 2:52 PMSubject: [GENERAL] Relation "xxxxx" does not existWhen 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 existCommand \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 windows2kValter
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: > > > > 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