Understanding Data Types in Supabase

#database #postgres #beginners #supabase

Determined to be the open-source alternative to Firebase, Supabase was one of the special gifts born during the pandemic. It provides super features such as a serverless database which can be interacted with locally and with its visual tool (https://supabase.io/) via the browser. An interesting difference with Firebase is its adoption of SQL rather than No-SQL.

Built on PostgreSQL, it retains features such as Postgres’ security and strength. Other interesting features are authentication handling, API interaction with the database(PostgRest), and the ability to listen to database events as they happen (Realtime).

While Supabase has proved to be that superhero that we open-source enthusiasts have been expecting to chase the commercial villains away😊, this post is not to dig deep into Supabase but to understand the various data types in Supabase. As with SQL, most of the data types are similar as seen in the pictures below:

Bool: This data type is appropriate for Boolean values. An example of the column to fit this is a column allowed_newsletters to check whether the user is allowed to receive newsletters or not.
Date: This keeps date values without the time. An example of a column to fit this is the birth day.
Float4: This is a single-precision floating-point number. With values in the range ±1.18×10−38 to ±3.4×1038
Float8: This is a double-precision floating-point number with values in the range ±2.23×10−308 to ±1.80×10308. You may want to use this for monetary values as money needs the best of precision.
Int2: This can contain numbers from -32,768 to 32,767. An example of a column to fit this is human_bones since we know the number of bones in a human body can’t exceed this number.
Int 4: This can contain numbers from -2,147,483,648 to +2,147,483,647.An example of a column to fit this is the number of auctions per day. Since we know we have 84,600 seconds in a day, it is quite impossible to have 2,147,483,648 auctions since the auction process is synchronous and takes at least a second to process a new price.
Int 8: This can contain numbers from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. An example of a column to fit this is the number of corona_cases per day. Since we know the total number of the world population is below this and even if everyone contracts corona the same day, this data type would still accommodate it.
Json: Holds json data. Could fit for saving json inputs that needs to be reused.
Jsonb: Better way to store json. A decomposed binary format of the json which supports json data indexing. Same columns for json would fit for jsonb. So better to use jsonb.
Text: This is used to store strings without defining the upper limit.
Time: Takes only the time of the day. Could help for columns such as start_time and stop_time for a short-exercises stopwatch.
Timestamp: This stores the date and time. Could come through for data such as created_time and modified_time to show audit changes across system.
Timestamptz: This stores the date and time with timezone and uses the UTC time. Could come through if you want to set a time for the future and you know since it has the timezone with it, this is better to keep such data as opportunity to vary is not allowed.
Timetz: Takes only the time of the day with timezone.
Uuid: Universal unique identifiers for a particular database entity. Could be cool for data such as university matric number, customer id etc.
Varchar: This is used to strings and allows you to define the upper limit.

Hope you enjoyed reading this😊. You could stop by and read some of my other articles. CIAO!!!

Top comments (3)

David • Jun 17 '22 • Edited

I found this article when struggling to figure out how to get a date into Supabase. For me the solution was to convert it to iso format first. Python snippet:

def convert_date_to_iso(datetime_string):
    return datetime.strptime(datetime_string, DATE_FORMAT).isoformat()

And then the insert:

data = supabase.table("mytable").insert({
    "timestamp": convert_date_to_iso(datetime_string)
}).execute()

John Niyontwali • Jul 24 '22

Is there a way to define the data to go within the json. An example:

create table books (
  id serial primary key,
  name text,
  email text,
  addresses jsonb
);

Is there a way I can define the data that should go in the address, lets say I have an interface of :

export interface Tenants {
  id: string;
  name: string;
  email: string;
  addresses: {
    street: string;
    postal: string;
    address: string;
    city: string;
    postalCode: string;
    country: string;
    website: string;
  };
}