Re: 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