[GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 <[EMAIL PROTECTED]>, "Daniel B. Thurman" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Upper and Lower-cased Database names?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Upper and Lower-cased Database names?
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!
Re: [GENERAL] Upper and Lower-cased Database names?
2007/10/10, Daniel B. Thurman <[EMAIL PROTECTED]>: > > 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Upper and Lower-cased Database names?
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
