I set up an empty PostgreSQL database for use by our telecoms admin here at Reed. She created a number of working tables for an integration project she’s been planning, and now it’s my turn to pull the info out of her database and into our LDAP directory.
Unfortunately, I seem to be having some sort of basic “failure to communicate” situation going on. Poking around the database as the ‘postgres’ super-user, I see the following:
telecom=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+--------- public | june | table | telecom public | test | table | telecom (3 rows)
Interesting…there really should be more there. Then I remember she’s a recovering MS SQLServer admin, and so may have split her work out into its own schema namespace:
telecom=# \dn List of schemas Name | Owner --------------------+---------- Phones | telecom information_schema | postgres pg_catalog | postgres pg_toast | postgres public | postgres (5 rows)
My first thought was, “Okay, cool; I haven’t had much of a chance to mess around with Postgres schema support. Time to learn!”
On attempting to actually query the data, though, I get the following:
telecom=# select * from Phones.Identification; ERROR: schema "phones" does not exist
(I had previously determined that the ‘Identification’ table existed using PGadmin to browse the entire namespace.)
In point of fact, querying from the public schema doesn’t appear to work, either:
telecom=# \d test
Table "public.test"
Column | Type | Modifiers
-----------+------------------------+------------
LastName | character varying(100) |
FirstName | character varying(100) |
Ext | character varying(100) | default 20
ListedExt | character varying(100) |
Room | character varying(100) |
Date | character varying(50) |
Cable | character varying(100) |
LenPort | character varying(100) |
PK | character varying(50) | not null
Indexes:
"UID" UNIQUE, btree ("PK")
telecom=# select count(Room) from test;
ERROR: column "room" does not exist
LINE 1: select count(Room) from test;
So at this point, I’m basically stuck. Any suggestions from out there amongst the tubes? Is it a charset issue, since she’s working on a Windows client? Am I missing some grants that would actually allow me to get at the Phones schema?
Postgres will case-fold identifiers to lowercase unless you enclose them in double-quotes. Try:
select * from “Phones”.”Identification”;
You can set a default schema search path for your session with:
set search_path = “Phones”;
Awesome. One read-only view later (I hate re-typing all those double-quotes), and I’m in business.