Table partitioning with unique columns

44 Views Asked by At

I have a task to create two tables = test_1 and test_2. test_1 should have (email, ip, age) columns and test_2 (name, surname, count and email_id). email_id should be foreign key referencing email. The problem is when I'm trying to create partitioning I'm getting an error '''FeatureNotSupported: unique constraint on partitioned table must include all partitioning columns DETAIL: UNIQUE constraint on table "test_table" lacks column "age" which is part of the partition key.'''. Can you help me? email should be UNIQUE because I need to use it as a reference to email_id, and I need to create partitioning on age.

Here is where I'm getting error when creating table

sql_query = '''CREATE TABLE IF NOT EXISTS test_1 
            (
            email TEXT UNIQUE,
            ip INET, 
            timestamp TIMESTAMP,
            age SMALLINT)
            PARTITION BY RANGE (age);'''

cursor.execute(sql_query)
connection.commit()

Also here is my second table

sql_query = '''CREATE TABLE test_2 (
                name TEXT,
                surname TEXT,
                count SMALLINT,
                email_id TEXT,
                FOREIGN KEY (email_id) REFERENCES test_1(email));'''
cursor.execute(sql_query)
connection.commit()

I understand that partitioning failed because email is UNIQUE, but I cannot change that. Is there a way to implement stuff that I want? I'm using PostgreSQL 16

0

There are 0 best solutions below