1 -- create a tablespace we can use
2 CREATE TABLESPACE testspace LOCATION '@testtablespace@';
3 -- create a schema we can use
4 CREATE SCHEMA testschema;
6 CREATE TABLE testschema.foo (i int) TABLESPACE testspace;
7 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
8 where c.reltablespace = t.oid AND c.relname = 'foo';
14 INSERT INTO testschema.foo VALUES(1);
15 INSERT INTO testschema.foo VALUES(2);
16 -- tables from dynamic sources
17 CREATE TABLE testschema.asselect TABLESPACE testspace AS SELECT 1;
18 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
19 where c.reltablespace = t.oid AND c.relname = 'asselect';
21 ----------+-----------
25 PREPARE selectsource(int) AS SELECT $1;
26 CREATE TABLE testschema.asexecute TABLESPACE testspace
27 AS EXECUTE selectsource(2);
28 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
29 where c.reltablespace = t.oid AND c.relname = 'asexecute';
31 -----------+-----------
36 CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
37 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
38 where c.reltablespace = t.oid AND c.relname = 'foo_idx';
44 -- let's try moving a table from one place to another
45 CREATE TABLE testschema.atable AS VALUES (1), (2);
46 CREATE UNIQUE INDEX anindex ON testschema.atable(column1);
47 ALTER TABLE testschema.atable SET TABLESPACE testspace;
48 ALTER INDEX testschema.anindex SET TABLESPACE testspace;
49 INSERT INTO testschema.atable VALUES(3); -- ok
50 INSERT INTO testschema.atable VALUES(1); -- fail (checks index)
51 ERROR: duplicate key value violates unique constraint "anindex"
52 DETAIL: Key (column1)=(1) already exists.
53 SELECT COUNT(*) FROM testschema.atable; -- checks heap
59 -- Will fail with bad path
60 CREATE TABLESPACE badspace LOCATION '/no/such/location';
61 ERROR: could not set permissions on directory "/no/such/location": No such file or directory
63 CREATE TABLE bar (i int) TABLESPACE nosuchspace;
64 ERROR: tablespace "nosuchspace" does not exist
66 DROP TABLESPACE testspace;
67 ERROR: tablespace "testspace" is not empty
68 DROP SCHEMA testschema CASCADE;
69 NOTICE: drop cascades to 4 other objects
70 DETAIL: drop cascades to table testschema.foo
71 drop cascades to table testschema.asselect
72 drop cascades to table testschema.asexecute
73 drop cascades to table testschema.atable
75 DROP TABLESPACE testspace;