Upper and Lower-cased Database names?

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)---------------------------

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

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

--
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!

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