> I'd like to know if there's any reasoning for not allowing creating an index
> inside the same schema where the table is. For example, if I have a
> multi-company database where each company has its own schema and its employees
> table, shouldn't I have a different index for each of those? What if I have
> some slightly different columns on some of these tables?
>
> ================================================================================
> teste=# create schema testing;
> CREATE SCHEMA
> teste=# create table testing.testing123 (something serial primary key,
> otherthing float);
> NOTICE: CREATE TABLE will create implicit sequence
> "testing123_something_seq" for serial column "testing123.something"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "testing123_pkey" for table "testing123"
> CREATE TABLE
> teste=# create index testing.testing123_index on testing.testing123
> (otherthing);
> ERROR: syntax error at or near "." no caracter 21
> LINHA 1: create index testing.testing123_index on testing.testing123 ...
> ^
> teste=#
> ================================================================================
>
>
> (I wouldn't mind if the autogenerated index for the PK was created on the
> public schema if no specific name was supplied.)
>
>
> This would also help identifying all objects to make a certain feature
> available and where they belong to on the database...
Just say
create index testing123_index on testing.testing123 (otherthing);
and you'll otain exactly what you want (see below).
Bye, Chris.
chris=> create schema testing;
CREATE SCHEMA
chris=> create table testing.testing123 (something serial primary key,
otherthing float);
NOTICE: CREATE TABLE will create implicit sequence "testing123_something_seq"
for serial column "testing123.something"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"testing123_pkey" for table "testing123"
CREATE TABLE
chris=> create index testing123_index on testing.testing123 (otherthing);
CREATE INDEX
chris=> \di *.*
List of relations
Schema | Name | Type | Owner | Table
---------+------------------+-------+-------+------------
testing | testing123_index | index | chris | testing123
testing | testing123_pkey | index | chris | testing123
(2 rows)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
