A Summary of Data Types for Supabase Table Columns

DBPostgreSQLSupabasePrisma

2 years ago

When you create a table in Supabase (PostgreSQL), choosing the data type of each column is important for using your data efficiently and optimizing search and processing performance.
It also affects more than just how data is stored, such as how you extend and maintain the database later, so it calls for careful design.

Supabase Table Data Type

If you're new to PostgreSQL data types or aren't familiar with them, it can be hard to know which type to choose.
So, to help you choose a data type, I've organized them into a table with descriptions as follows.
Use the table to design a database structure that fits your project!

Supabase	Prisma	TypeScript	Description
`int2`	`Int`	`number`	Used for integer values in a small range (`-32,768` to `+32,767`), such as grades, ages, and ratings. It can save a bit more storage space when you handle large amounts of data.
`int4`	`Int`	`number`	Integer values in the most common range (`-2,147,483,648` to `+2,147,483,647`).
`int8`	`BigInt`	`bigint`	Integer values in a very large range (`-9,223,372,036,854,775,808` to `+9,223,372,036,854,775,807`). Used when values need a very large range, such as IDs for large datasets, view counts, and large amounts of money.
`float4`	`Float`	`number`	A floating-point value with precision of up to 6 digits after the decimal point. Used when memory efficiency matters or when you don't need high precision, such as for temperatures and coordinates.
`float8`	`Float`	`number`	A floating-point value with precision of up to 15 digits after the decimal point. Used when you need more precise calculations, such as scientific computing and aviation data.
`numeric`	`Decimal`	`Decimal`	Used when you need decimal calculations that demand perfect accuracy with no room for error, such as financial transactions and accounting. Processing is relatively slow.
`json`	`Json`	`Prisma.JsonValue`	Stores structured JSON data as a string. It has to be parsed every time the data is processed, so unless you're only storing it, we recommend using `jsonb`.
`jsonb`	`Json`	`Prisma.JsonValue`	Stores JSON data in binary form. It's fast to process and supports indexing, so it's useful when you often need to search or modify the data.
`text`	`String`	`string`	A string with no length limit, such as long text or post content.
`varchar`	`String`	`string`	A string with a limited length, such as a username or email. Checking the length limit can add a little overhead, so in most cases we recommend using `text`.
`uuid`	`String`	`string`	Stores a universally unique identifier. Used when you need a unique ID, and it consists of 32 hexadecimal digits.
`date`	`DateTime`	`Date`	Stores a date without time information, and is used for things like birthdays and anniversaries. We recommend using `timestamptz`. Example) `2025-12-16` => `2025-12-16T00:00:00.000Z`
`time`	`DateTime`	`Date`	Stores only the time without date information, and is used for things like business hours and daily schedules. We recommend using `timestamptz`. Example) `08:10:28` => `1970-01-01T08:10:28.000Z`
`timetz`	`DateTime`	`Date`	Stores the time together with time zone information. We recommend using `timestamptz`. Example) `08:10:28+00` => `1970-01-01T08:10:28.000Z`
`timestamp`	`DateTime`	`Date`	Stores the date and time together, and is used for things like when an event occurred or when data was created/updated. We recommend using `timestamptz`. Example) `2025-01-15 08:10:28.000` => `2025-01-15T08:10:28.000Z`
`timestamptz`	`DateTime`	`Date`	Stores the date, time, and time zone information all together. Used when a global service needs to take time zones into account, and it's the most recommended option. Example) `2025-01-15 08:10:28.000+00` => `2025-01-15T08:10:28.000Z`
`bool`	`Boolean`	`boolean`	Stores a true/false Boolean value.