Skip to main content

Tables

In Postgres, data is stored in "tables". Tables are similar to excel spreadsheets. They contain columns & rows of data. There are a few key differences from a spreadsheet however:

  • Every column is a strict type of data. When you set up a column, you must define the "data type".
  • Tables can be "joined" together. For example you can have a users table, which is joined to a teams table (because users belong to teams).

For example, this table has 3 "columns" (id, name, description) and 4 "rows" of data:

idnamedescription
1Toy StoryWhen a new toy called "Forky" joins Woody and the gang, a road trip alongside old and new friends reveals how big the world can be for a toy.
2Monsters, Inc.In order to power the city, monsters have to scare children so that they scream. However, the children are toxic to monsters.
3Finding NemoAfter his son is captured in the Great Barrier Reef and taken to Sydney, a timid clownfish sets out on a journey to bring him home.
4WALL-EIn the distant future, a small waste-collecting robot inadvertently embarks on a space journey that will ultimately decide the fate of mankind.

Creating Tables#

Supabase provides several options for creating tables. You can use the Table Editor or create them directly using SQL. We provide a SQL editor within the Dashboard, or you can connect to your database and run the SQL queries yourself.

1. Go to the "Table editor" section.
2. Click "New Table".
3. Enter the table name "todos".
4. Click "Save".
5. Click "New Column".
6. Enter the column name "task" and make the type "text".
7. Click "Save".

Tips#

  • It is best practice to use lowercase and underscores when naming tables. For example: table_name, not Table Name.
  • Tables belong to schemas. If you don't explicitly pass the schema, Postgres will assume that you want to create the table in the public schema.

Data types#

Every column is a predefined type. PostgreSQL provides many default types, and you can even design your own (or use extensions) if the default types don't fit your needs.

Show/Hide default data types
NameAliasesDescription
bigintint8signed eight-byte integer
bigserialserial8autoincrementing eight-byte integer
bitfixed-length bit string
bit varyingvarbitvariable-length bit string
booleanboollogical Boolean (true/false)
boxrectangular box on a plane
byteabinary data (“byte array”)
charactercharfixed-length character string
character varyingvarcharvariable-length character string
cidrIPv4 or IPv6 network address
circlecircle on a plane
datecalendar date (year, month, day)
double precisionfloat8double precision floating-point number (8 bytes)
inetIPv4 or IPv6 host address
integerint, int4signed four-byte integer
interval [ fields ]time span
jsontextual JSON data
jsonbbinary JSON data, decomposed
lineinfinite line on a plane
lsegline segment on a plane
macaddrMAC (Media Access Control) address
macaddr8MAC (Media Access Control) address (EUI-64 format)
moneycurrency amount
numericdecimalexact numeric of selectable precision
pathgeometric path on a plane
pg_lsnPostgreSQL Log Sequence Number
pg_snapshotuser-level transaction ID snapshot
pointgeometric point on a plane
polygonclosed geometric path on a plane
realfloat4single precision floating-point number (4 bytes)
smallintint2signed two-byte integer
smallserialserial2autoincrementing two-byte integer
serialserial4autoincrementing four-byte integer
textvariable-length character string
time [ without time zone ]time of day (no time zone)
time with time zonetimetztime of day, including time zone
timestamp [ without time zone ]date and time (no time zone)
timestamp with time zonetimestamptzdate and time, including time zone
tsquerytext search query
tsvectortext search document
txid_snapshotuser-level transaction ID snapshot (deprecated; see pg_snapshot)
uuiduniversally unique identifier
xmlXML data