Thread: Upper and Lower-cased Database names?

I am finding out for the first time that by having a database created
with the name: MyTest, I cannot do a simple query as follows:

postgres=# select * from MyTest.public.cars;
ERROR:  cross-database references are not implemented: "mytest.public.cars"

Notice, however since I created a cars table in the postgres database, I was
able to do a query:

postgres=# select * from postgres.public.cars ;

 carid |     name     | vendor | type
-------+--------------+--------+------
 H1    | Civic        | Honda  | FF
 N1    | Skyline GT-R | Nissan | 4WD
 T1    | Supra        | Toyota | FR
 T2    | MR-2         | Toyota | FF
(4 rows)

So the problem, it seems that mixed case database names might not be supported
with pssql?  I have a feeling that the default character set is SQL-ASCII and should be
changed to something else?  What might that be and how can I change/update the
character-set (encoding)?

Thanks!
Dan


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.14.6/1060 - Release Date: 10/9/2007 4:43 PM

Daniel,

please try:

select * from "MyTest".public.cars;

mixed cases need those ", per SQL-Standard. In my experienced mixed cases in qualifiers which cross OS-barriers cause more trouble then use (... filenames with WebServers ...)

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

This error probably does not have anything to do with the case of your
database name. Instead you probably logged into a database which is not
MyTest, because it is not possible to log into a database x and make a
query on database y.


Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




>>> On Wed, Oct 10, 2007 at 10:05 AM, in message
<021126B987E43D44A860139823C079110E2BA6@orion.cdkkt.com>, "Daniel B.
Thurman"
<dant@cdkkt.com> wrote:

> I am finding out for the first time that by having a database
created
> with the name: MyTest, I cannot do a simple query as follows:
>
> postgres=# select * from MyTest.public.cars;
> ERROR:  cross- database references are not implemented:
"mytest.public.cars"
>
> Notice, however since I created a cars table in the postgres
database, I was
> able to do a query:
>
> postgres=# select * from postgres.public.cars ;
>
>  carid |     name     | vendor | type
> ------- +-------------- +-------- +------
>  H1    | Civic        | Honda  | FF
>  N1    | Skyline GT- R | Nissan | 4WD
>  T1    | Supra        | Toyota | FR
>  T2    | MR- 2         | Toyota | FF
> (4 rows)
>
> So the problem, it seems that mixed case database names might not be

> supported
> with pssql?  I have a feeling that the default character set is SQL-
ASCII and
> should be
> changed to something else?  What might that be and how can I
change/update
> the
> character- set (encoding)?
>
> Thanks!
> Dan
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.488 / Virus Database: 269.14.6/1060 -  Release Date:
10/9/2007
> 4:43 PM
>
>
> --------------------------- (end of
broadcast)---------------------------
> TIP 2: Don't 'kill - 9' the postmaster

2007/10/10, Daniel B. Thurman <dant@cdkkt.com>:
>
> I am finding out for the first time that by having a database created
> with the name: MyTest, I cannot do a simple query as follows:
>
> postgres=# select * from MyTest.public.cars;
> ERROR:  cross-database references are not implemented: "mytest.public.cars"
>
> Notice, however since I created a cars table in the postgres database, I was
> able to do a query:
>
> postgres=# select * from postgres.public.cars ;
>
>  carid |     name     | vendor | type
> -------+--------------+--------+------
>  H1    | Civic        | Honda  | FF
>  N1    | Skyline GT-R | Nissan | 4WD
>  T1    | Supra        | Toyota | FR
>  T2    | MR-2         | Toyota | FF
> (4 rows)
>
> So the problem, it seems that mixed case database names might not be supported
> with pssql?  I have a feeling that the default character set is SQL-ASCII and should be
> changed to something else?  What might that be and how can I change/update the
> character-set (encoding)?

PostgreSQL doesn't support cross-database references, as per the error
message, i.e. you can only perform queries on the current database.
*However*, the syntax works when the named database is the same as one
you're connected to. If you do

\c MyTest
mytest=# select * from MyTest.public.cars;

the query will work (case is not the problem here).


HTH


Ian Barwick

--
http://sql-info.de/index.html

Daniel B. Thurman wrote:
> I am finding out for the first time that by having a database created
> with the name: MyTest, I cannot do a simple query as follows:
>
> postgres=# select * from MyTest.public.cars;
> ERROR:  cross-database references are not implemented: "mytest.public.cars"

Correct - a query takes place within a specific database. You want to
connect to "mytest" and then issue your query.

> Notice, however since I created a cars table in the postgres database, I was
> able to do a query:
>
> postgres=# select * from postgres.public.cars ;
>
>  carid |     name     | vendor | type
> -------+--------------+--------+------
>  H1    | Civic        | Honda  | FF
>  N1    | Skyline GT-R | Nissan | 4WD
>  T1    | Supra        | Toyota | FR
>  T2    | MR-2         | Toyota | FF
> (4 rows)

You're logged in to the postgres database, and you're querying the
postgres database. The query is equivalent to:
   SELECT * FROM public.cars;
or, assuming the "public" schema is in your search_path:
   SELECT * FROM cars;
If you were logged in to a different database your query would fail with
the same error as previously.

> So the problem, it seems that mixed case database names might not be supported
> with pssql?

No, it works fine, lthough PG folds to lower-case rather than upper-case
  (the standard). However, the rule-of-thumb is if you create the
database/table with "" to preserve case then always access it with ""

So:
   CREATE TABLE Foo   -- Gets folded to lower-case
   SELECT * FROM Foo  -- So does this, so it works
   SELECT * FROM FOO
   SELECT * FROM foo
   SELECT * FROM "Foo"  -- Fails, because you've stopped case-folding
   CREATE TABLE "Bar"
   SELECT * FROM "Bar"
   SELECT * FROM Bar -- fails, because this gets folded to lower-case

 >I have a feeling that the default character set is SQL-ASCII and should be
> changed to something else?  What might that be and how can I change/update the
> character-set (encoding)?

Well, you probably want a different character-set, but that will depend
upon your locale and the character-set of the data you are storing.
Nothing to do with this.

HTH
--
   Richard Huxton
   Archonet Ltd

Daniel B. Thurman wrote:
> I am finding out for the first time that by having a database created
> with the name: MyTest, I cannot do a simple query as follows:
>
> postgres=# select * from MyTest.public.cars;
> ERROR:  cross-database references are not implemented:
> "mytest.public.cars"

Try:

select * from "MyTest".public.cars;

Yours,
Laurenz Albe