Re: Creating dynamically-typed tables using psycopg2's built-in formatting

Yeah,
"schema.tbl" will search all schemas in search path for a table explicitly called "schema.tbl".
So that will never find a table called "tbl", and most probably never find a table at all.

And without quotes, it will search for a table called "tbl" in a schema called "schema".
So your issue makes perfect sense now.

FWIW, if you where to query "schema"."tbl", it would look for a table (or view) called "tbl" in a schema called "schema",
Which seems to be what you want.

Sebastiaan Alexander Mannem
Senior Consultant
Anthony Fokkerweg 1
1059 CM Amsterdam, The Netherlands
T: +31 6 82521560
www.edbpostgres.com

> On 14 Jun 2019, at 18:41, Daniel Cohen <daniel(dot)m(dot)cohen(at)berkeley(dot)edu> wrote:
>
> Hi again,
>
> I realize the error was that I specified the name as "schema.tbl", and I think because there was a period in the name, it converted to double-quote. I tried again as just "tbl" and it worked perfectly. Thanks so much for all your help.
>
> Best,
>
> Danny
>
> On Fri, Jun 14, 2019 at 9:12 AM Christophe Pettus <xof(at)thebuild(dot)com <mailto:xof(at)thebuild(dot)com>> wrote:
>
>
> > On Jun 13, 2019, at 13:54, Daniel Cohen <daniel(dot)m(dot)cohen(at)berkeley(dot)edu <mailto:daniel(dot)m(dot)cohen(at)berkeley(dot)edu>> wrote:
> >
> > Thanks so much for your response. The uppercase --> lowercase fix worked for the types, but I'm still only getting tables that can be searched by double-quotations (i.e. > SELECT * FROM tbl; returns nothing, but > SELECT * FROM "tbl"; returns the table I uploaded).
>
> By "returns nothing," do you mean you get an error, or that you get zero rows?
>
> I'd connect to the database using psql and use \d to see what tables actually exist.
> --
> -- Christophe Pettus
> xof(at)thebuild(dot)com <mailto:xof(at)thebuild(dot)com>
>