Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?

Jay Stanley <beansboy(at)cruzio(dot)com> writes:
> I've come across some interesting behavior with regards to creating a
> partition of a table that includes the schema name and a period in the
> beginning, so that the resulting name is like
> "my_schema"."my_schema.my_table_should_not_work".
> After created it, most SQL won't access it at all, even when
> double-quoting the table name exactly, though drop seems to work.

I think this has little to do with the funny table names, and much
to do with your being careless about which schema the partitions
end up in. We intentionally don't constrain partitions to live
in the same schema as their parent. So when you do

> create schema my_schema;

> create table my_schema.my_table(
> i bigint not null primary key,
> dat text)
> partition by range(i);

> create table my_table_default partition of my_schema.my_table DEFAULT;
> create table my_table_1 partition of my_schema.my_table for values from
> (1) to (100);

the parent "my_table" is in "my_schema", but the partitions are
(probably) in schema "public". Your catalog-investigation query
doesn't show that, adding to your confusion. The commands
that don't work for you are failing because you assume the
partitions are in "my_schema", except in some places where
you leave that off, and then it does work because public
is in your search_path.

regards, tom lane