Обсуждение: 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!
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!
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