Drizzle Team•10mo ago

Is it possible to store an array of primary keys for table A on table B using Postgres?

IThis might be more of a Postgres question ,and my research has turned up that the answer is pretty much "No", but I was wondering if anybody has found a way to do this using Drizzle and custom types maybe? I want to store an array of primary keys in a table. I'm having trouble figuring out the correct way to do this. I would settle for using the a json type as well but that doesn't seem to work either given the type in the column of the reference table needs to be the same as the primary key, in this case uuid or text. Any ideas? Thanks!

2 Replies

Amruth Pillai•10mo ago

It sounds like what you are trying to create is a bridge/junction table? But I'm quite not sure. In any case, yes, you cannot have multiple primary keys in a single table, but you can have composite primary keys (PKs that constitute of two or more columns). In Drizzle for example, this is how you would do that:

export const taskUserTable = pgTable(
  "task_user",
  {
    taskId: text("task_id")
      .notNull()
      .references(() => taskTable.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => userTable.id, { onDelete: "cascade" }),
    createdAt: timestampType("created_at"),
    createdById: text("created_by_id").references(() => userTable.id, { onDelete: "set null" }),
  },
  (t) => ({
    primaryKey: primaryKey({ columns: [t.taskId, t.userId] }),
  }),
);

export const taskUserTable = pgTable(
  "task_user",
  {
    taskId: text("task_id")
      .notNull()
      .references(() => taskTable.id, { onDelete: "cascade" }),
    userId: text("user_id")
      .notNull()
      .references(() => userTable.id, { onDelete: "cascade" }),
    createdAt: timestampType("created_at"),
    createdById: text("created_by_id").references(() => userTable.id, { onDelete: "set null" }),
  },
  (t) => ({
    primaryKey: primaryKey({ columns: [t.taskId, t.userId] }),
  }),
);

Here, taskId and userId are both references to PKs on a different table, but used in union as a primary key to ensure there are never two rows which have the same task and user ID. You can add more columns to this primary key simply by appending the array.

_PearOP•10mo ago

Hey thanks for the answer.. Im not sure that's what im looking for. Could that array only reference one key instead of two? I only have one primary key on table A. in table B i want to store an array of references to those primary keys. So this is still the way to achieve that? in other words, in your example, is it possible to make the created_by_id column to be an array of user ids? Asking this another way: How can I cast uuid or text to uuid[] or text[]? The .array() method seems to work in theory but when I try to apply changes with it I get: Key columns "images" and "path" are of incompatible types: text[] and text.

In any case, yes, you cannot have multiple primary keys in a single table, but you can have composite primary keys (PKs that constitute of two or more columns).

Going to try this too. I was able to get this solved by redoing some of my tables and making a one-to-many relationship. I think I had the tables swapped before but this setup makes more sense. Thanks!

Gaming

Programming

Is it possible to store an array of primary keys for table A on table B using Postgres?