Hi, I'll first explain how it is now and then I'll explain how I think it should behave :-)
I have Postgresql 9.1.4 64bit Linux - The default tablespace at install is "pg_default" which is (here) somewhere in /var/lib/... - The parameter "default_tablespace" in *postgresql.conf* is commented out (it's original state) My Tablespace C1 definition: CREATE TABLESPACE "C1" OWNER postgres LOCATION '/some/path'; My Database C1 definition: CREATE DATABASE "C1" WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = "C1" LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' CONNECTION LIMIT = -1; After these two statements, tablespace C1 should be the default tablespace for storing all objects in database C1, meaning that when I do not specify a tablespace, objects are stored there. Right? I now proceed to do: CREATE TABLE "TEMP1" ( "ID" bigint NOT NULL, CONSTRAINT "PK_TEMP1" PRIMARY KEY ("ID" ) ); That makes a table "TEMP1" in tablespace C1. Ok so far. *But it creates the index of the PK in tablespace "pg_default" !!!* *If I want the index of that constraint in tablespace C1, I must explicitely specify it.* Same for any indexes I create: If I do not specify the tablespace when I create an index, it goes into "pg_default" :-( And now, this is how I would like Postgres to behave: a) ALL objects in a given database, not only tables, should by default go into the tablespace which is defined as default for the DB - in my case above, "C1". b) All CREATE commands creating objects into a given database should assume as the default tablespace the tablespace given as default for the DB, in my case above "C1". c) If would be nice if there was an additional (optional) parameter "INDEX_TABLESPACE" for the CREATE DATABASE command, which would define a default tablespace to contain indexes for the DB. d) By extension, it would be nice if there were additional "<OBJECTTYPE>_TABLESPACE" parameters for the CREATE DATABASE command for all object types which have to be stored somewhere in the DB. In any case, I think that how it behaves now is not 100% correct. Thanks and best wishes, RD