How to fix insufficient columns in unique constraint for partition key in PostgreSQL

-- Example error:
-- ERROR:  unique constraint on partitioned table must include all partitioning columns
-- DETAIL:  Constraint on table "orders" lacks column "created_date" which is part of the partition key.

-- Query to find partition key columns:
SELECT
  schemaname,
  tablename,
  pg_get_partkeydef(relid) AS partition_key
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename AND c.relkind = 'p'
WHERE tablename = 'orders';

-- For more detail, use this query:
SELECT
  attname,
  attnum
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
  AND (attnum = ANY((SELECT conkey FROM pg_constraint WHERE conrelid = 'orders'::regclass AND contype = 'p')))
ORDER BY attnum;

-- BEFORE (fails with 42P17):
CREATE TABLE orders (
  id BIGSERIAL,
  order_number VARCHAR(50),
  created_date DATE,
  amount DECIMAL(10,2)
) PARTITION BY RANGE (created_date);

ALTER TABLE orders ADD PRIMARY KEY (id);
-- ERROR: unique constraint on partitioned table must include all partitioning columns
-- DETAIL: Constraint on table "orders" lacks column "created_date" which is part of the partition key.

-- AFTER (includes partition key):
ALTER TABLE orders ADD PRIMARY KEY (id, created_date);
-- SUCCESS

-- BEFORE (fails):
ALTER TABLE orders ADD CONSTRAINT orders_order_number_unique UNIQUE (order_number);

-- AFTER (includes partition key):
ALTER TABLE orders ADD CONSTRAINT orders_order_number_unique UNIQUE (order_number, created_date);

-- If partitioned by multiple columns:
CREATE TABLE transactions (
  id BIGSERIAL,
  customer_id INT,
  region VARCHAR(50),
  transaction_date DATE,
  amount DECIMAL(10,2)
) PARTITION BY RANGE (transaction_date)
           SUBPARTITION BY LIST (region);

-- Constraint must include BOTH partition key columns:
ALTER TABLE transactions
ADD PRIMARY KEY (id, transaction_date, region);

-- View all partitions for a table:
SELECT
  schemaname,
  tablename,
  pg_get_partkeydef(relid) AS partition_key
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename AND c.relkind = 'p'
WHERE tablename = 'your_table';

-- For LIST or HASH partitioning, get partition details:
SELECT
  relname AS partition_name,
  pg_get_partkeydef(oid) AS partition_definition
FROM pg_class
WHERE relkind = 'p'
  AND relname = 'your_table';

-- List existing constraints on the table:
SELECT
  constraint_name,
  constraint_type,
  column_name
FROM information_schema.key_column_usage
WHERE table_name = 'your_table'
ORDER BY constraint_name, ordinal_position;

-- Orders: unique per customer, per day
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id INT,
  order_date DATE,
  order_seq INT
) PARTITION BY RANGE (order_date);

-- This makes sense: allow multiple sequences per customer if they span days
ALTER TABLE orders
ADD PRIMARY KEY (customer_id, order_date, order_seq);

-- User IDs must be globally unique, but the table is partitioned by signup_date
CREATE TABLE users (
  user_id BIGSERIAL,
  email VARCHAR(255),
  signup_date DATE,
  profile_data JSONB
) PARTITION BY RANGE (signup_date);

-- For global uniqueness on user_id across all partitions:
-- Option A: Include partition key (allows same ID in multiple date ranges—not desired)
ALTER TABLE users ADD PRIMARY KEY (user_id, signup_date);
-- This is misleading: suggests user_id is not globally unique

-- Option B: Use a non-partitioned unique index on a separate table (better for global uniqueness)
CREATE TABLE user_id_registry (
  user_id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT NOW()
);
-- In application, check this table before inserting into users.

-- Option C: Use application-level uniqueness checks + triggers
CREATE TRIGGER check_global_user_id BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION validate_unique_user_id();

CREATE TABLE orders (
  id BIGSERIAL,
  created_date DATE,
  customer_id INT,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, created_date)
) PARTITION BY RANGE (created_date);

CREATE TABLE orders_y2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

// Workaround: define a model but tell Prisma to ignore it for migration
model Orders {
  id          BigInt   @default(autoincrement())
  createdDate DateTime @db.Date
  customerId  Int
  amount      Decimal  @db.Decimal(10, 2)

  @@id([id, createdDate])
  @@map("orders")
}

-- Insert valid data across partitions:
INSERT INTO orders (id, created_date, customer_id, amount)
VALUES
  (1, '2024-01-15', 100, 50.00),
  (2, '2024-02-15', 100, 75.00),
  (3, '2024-03-15', 101, 100.00);

-- Verify partition key is enforced:
SELECT * FROM orders ORDER BY created_date;

-- Try to insert a duplicate key (should fail):
INSERT INTO orders (id, created_date, customer_id, amount)
VALUES (1, '2024-01-15', 100, 99.99);
-- ERROR: duplicate key value violates unique constraint "orders_pkey"

-- But the same ID in a different partition is allowed (if partition key is NOT in constraint):
INSERT INTO orders (id, created_date, customer_id, amount)
VALUES (1, '2024-04-15', 100, 60.00);
-- This succeeds because id=1 with a different created_date is allowed