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