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

Question

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!

Александр · Accepted Answer · 2024-09-02 07:00:03Z

1

The issue was resolved with the command

SET default_tablespace = '';

answered Sep 2, 2024 at 7:00

Александр

391 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lajos Arpad · Accepted Answer · 2024-09-01 11:59:39Z

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.

I don't quite understand. I have a problem when creating the partitioned table itself. So, I want to do: CREATE TABLE service.test_table ( ..... ) PARTITION BY hash (id); (Here I get an error) And then I plan to create the partitions themselves: BEGIN FOR i IN 0..9 LOOP EXECUTE format('CREATE TABLE service.test_table_p%s PARTITION OF service.test_table FOR VALUES WITH (MODULUS 10, REMAINDER %s);', i, i); END LOOP; END
@Александр I do not see what TABLESPACE you specified in the command you have given. If you read carefully the example I have given, you will see that the TABLESPACE is explicitly specified. In your query it is not. Hence, since your query does not instruct the RDBMS what TABLESPACE to use, it will resort to the default where your access is restricted.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related