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