Drizzle Team•17mo ago

postges, auto-generated uuid as primary key

I'm looking to change all my tables from having a serial int primary key as ID what is the best way for generating the column definition in the drizzle schema file? Thank you

9 Replies

DiamondDragon•17mo ago

i have it like this. my postgres has a uuid v7 extension installed so it generates

export const entities = pgTable("entities", {
    entityId: uuid("object_id")
        .default(sql`uuid_generate_v7()`)
        .primaryKey(),

export const entities = pgTable("entities", {
    entityId: uuid("object_id")
        .default(sql`uuid_generate_v7()`)
        .primaryKey(),

rubberduckiesOP•17mo ago

thank you, i'll try it out. I didn't know it would need an extension for it do you know how i can install it in my AWS RDS posgres instance?

DiamondDragon•17mo ago

im using neon so i dont know. try asking chatgpt

rubberduckiesOP•17mo ago

i did

DiamondDragon•17mo ago

you like just need to execute a SQL command CREATE EXTENSION ...

rubberduckiesOP•17mo ago

ChatGPT tells me that, but it only mentions UUID v4, i don't know the differences

As of my knowledge cutoff in September 2021, the latest version of the `uuid-ossp` extension available in PostgreSQL is version 1.1, and it provides the UUID v4 functionality. However, there might have been updates or changes since then.

To enable the `uuid-ossp` extension in your AWS RDS PostgreSQL instance, you can follow these general steps:

1. Connect to your AWS RDS PostgreSQL instance using a PostgreSQL client tool or the AWS RDS console.

2. Open a database session with administrative privileges (e.g., with a user having the `rds_superuser` role).

3. Run the following SQL command to enable the `uuid-ossp` extension:

As of my knowledge cutoff in September 2021, the latest version of the `uuid-ossp` extension available in PostgreSQL is version 1.1, and it provides the UUID v4 functionality. However, there might have been updates or changes since then.

To enable the `uuid-ossp` extension in your AWS RDS PostgreSQL instance, you can follow these general steps:

1. Connect to your AWS RDS PostgreSQL instance using a PostgreSQL client tool or the AWS RDS console.

2. Open a database session with administrative privileges (e.g., with a user having the `rds_superuser` role).

3. Run the following SQL command to enable the `uuid-ossp` extension:

sql CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

   This command creates the extension if it doesn't already exist in the database.

4. Verify that the extension has been installed by running the following command:

   This command creates the extension if it doesn't already exist in the database.

4. Verify that the extension has been installed by running the following command:

sql SELECT * FROM pg_extension WHERE extname = 'uuid-ossp';

   If the installation was successful, you should see a row returned with the extension details.

Please note that the availability of specific extension versions can depend on the PostgreSQL version that is running on your AWS RDS instance. Additionally, if there have been updates or changes to the `uuid-ossp` extension since my knowledge cutoff, you might need to adapt the instructions accordingly. It is recommended to consult the official AWS RDS documentation and the PostgreSQL documentation for the most up-to-date and specific instructions for your setup.

   If the installation was successful, you should see a row returned with the extension details.

Please note that the availability of specific extension versions can depend on the PostgreSQL version that is running on your AWS RDS instance. Additionally, if there have been updates or changes to the `uuid-ossp` extension since my knowledge cutoff, you might need to adapt the instructions accordingly. It is recommended to consult the official AWS RDS documentation and the PostgreSQL documentation for the most up-to-date and specific instructions for your setup.

DiamondDragon•17mo ago

v4 is fine to use

rubberduckiesOP•17mo ago

awesome, it's working thank you so much

MrMendez•10mo ago

am using neon too, but how do i stop the type system from asking me to insert an id?

Gaming

Programming

postges, auto-generated uuid as primary key