tables

13 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Tables structure: columns and rows

Tables store data in a structure similar to spreadsheets, containing columns (fields) and rows (records). Every column has a predefined data type that must be defined when the column is created. Tables can be created via the Dashboard or directly using SQL.

Table naming convention

When naming tables, use lowercase and underscores instead of spaces. For example, use `table_name` instead of `Table Name`.

Data types supported in Postgres

Postgres supports the following data types: bigint/int8 (signed eight-byte integer), bigserial/serial8 (autoincrementing eight-byte integer), bit (fixed-length bit string), bit varying/varbit (variable-length bit string), boolean/bool (logical Boolean), box (rectangular box), bytea (binary data), character/char (fixed-length character string), character varying/varchar (variable-length character string), cidr (IPv4 or IPv6 network address), circle (circle on a plane), date (calendar date), double precision/float8 (double precision floating-point number, 8 bytes), inet (IPv4 or IPv6 host address), integer/int/int4 (signed four-byte integer), interval (time span), json (textual JSON data), jsonb (binary JSON data, decomposed), line (infinite line), lseg (line segment), macaddr (MAC address), macaddr8 (MAC address EUI-64 format), money (currency amount), numeric/decimal (exact numeric), path (geometric path), pg_lsn (Postgres Log Sequence Number), pg_snapshot (user-level transaction ID snapshot), point (geometric point), polygon (closed geometric path), real/float4 (single precision floating-point number, 4 bytes), smallint/int2 (signed two-byte integer), smallserial/serial2 (autoincrementing two-byte integer), serial/serial4 (autoincrementing four-byte integer), text (variable-length character string), time/time without time zone (time of day, no timezone), time with time zone/timetz (time of day with timezone), timestamp/timestamp without time zone (date and time, no timezone), timestamp with time zone/timestamptz (date and time with timezone), tsquery (text search query), tsvector (text search document), txid_snapshot (user-level transaction ID snapshot, deprecated), uuid (universally unique identifier), and xml (XML data).

Primary key best practices

It is recommended to create a primary key for every table. A primary key is a unique identifier for every row. Any column can be used as a primary key as long as it is unique for every row. It is common to use a uuid type or a numbered identity column as the primary key.

Identity column with generated always

Using `generated always as identity` creates an identity column where Postgres automatically assigns a unique number and does not allow inserting your own values. Example: `create table movies (id bigint generated always as identity primary key);`

Identity column with generated by default

Using `generated by default as identity` creates an identity column where Postgres automatically assigns a unique number, but allows you to insert your own unique values if needed. Example: `create table movies (id bigint generated by default as identity primary key);`

Bulk data loading with COPY command

For loading large datasets, use Postgres's COPY command which loads data directly from a file into a table. Supported file formats include text, CSV, binary, and JSON. Example: `psql -h DATABASE_URL -p 5432 -d postgres -U postgres -c "\COPY movies FROM './movies.csv' WITH DELIMITER ',' CSV HEADER"`

Foreign keys for table relationships

Tables can be joined together using foreign keys. A foreign key is a column in one table that references the primary key of another table. Example: `alter table movies add column category_id bigint references categories;`

Many-to-many relationships with join tables

To create many-to-many relationships (e.g., movies with multiple actors, actors in multiple movies), create a join table that references the primary keys of both related tables. Example: `create table performances (id bigint generated by default as identity primary key, movie_id bigint not null references movies, actor_id bigint not null references actors);`

Schemas for organizing tables

Schemas are used to organize tables, often for security reasons. Tables belong to schemas. If no schema is specified when creating a table, Postgres assumes the table should be created in the `public` schema. Create a schema with: `create schema private;` Then create tables inside it: `create table private.salaries (id bigint generated by default as identity primary key, salary bigint not null, actor_id bigint not null references public.actors);`

Custom schemas and API exposure

To access a custom schema through the Supabase Data API, you need to expose it and grant appropriate permissions. See Using Custom Schemas documentation for detailed steps, and Securing your API for security best practices around schema exposure.

Views as query shortcuts

A view is a convenient shortcut to a query that does not involve new tables or data. When run, an underlying query is executed and returns its results. Views can simplify repeated complex queries, ensure consistency, improve logical organization, and provide security by restricting data access.

Creating a basic view

Create a view with: `create view transcripts as select students.name, students.type, courses.title, courses.code, grades.result from grades left join students on grades.student_id = students.id left join courses on grades.course_id = courses.id; grant all on table transcripts to authenticated;` Then query it with: `select * from transcripts;`

Give your agent this brain