PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Partitioning Error: ERROR: cannot specify default tablespace for partitioned relations

I am trying to add partitioning to a table in my database. Here is an example:

CREATE TABLE IF NOT EXISTS myBd.test_table 
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
) PARTITION BY hash (id);

However, I get the following error:

ERROR: cannot specify default tablespace for partitioned relations

I understand that the default tablespace has been changed by the database administrators. After running the command:

SELECT datname, dattablespace 
FROM pg_database 
WHERE datname = 'myDb';

I get:

dattablespace = 16000

What can I do to create a partitioned table? I do not have permissions to change the default tablespace.

Thank you!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue was resolved with the command

    SET default_tablespace = '';

  2. 0

    The documentation says

    Partitions thus created are in every way normal PostgreSQL tables (or, possibly, foreign tables). It is possible to specify a tablespace and storage parameters for each partition separately.

    It provides an example for the tablespace being explicitly specified as we can see:

    CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

    So, if you are not allowed to specify the default tablespace and you have no access right to it, then make sure you have access to a tablespace and specify it as your tablespace.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search