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
