query patterns/joins

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

Select first row per group with DISTINCT ON

To select the first row for each group in Postgres, use SELECT DISTINCT ON (column_name) combined with ORDER BY. The DISTINCT ON clause specifies which column to group by, and the ORDER BY clause determines which row is considered 'first' within each group. Place the grouping column first in the ORDER BY to ensure correct grouping, then order by the column that determines priority within each group.

DISTINCT ON example: maximum points per team

To find the row with maximum points for each team from a seasons table, use: select distinct on (team) id, team, points from seasons order by team, points desc; The query orders by team first (to group correctly), then by points descending to get the highest points first within each team group.

DISTINCT ON requires ORDER BY with grouping column first

When using DISTINCT ON (column), the ORDER BY clause must list the DISTINCT ON column first. This ensures Postgres correctly identifies which rows belong to which group before selecting the first one based on subsequent ORDER BY criteria.

DISTINCT ON with DESC to select maximum value per group

Use DESC in the ORDER BY clause after the grouping column to sort values from highest to lowest within each group. This makes DISTINCT ON return the row with the maximum value for each group.

Use EXPLAIN to analyze query performance

Use the EXPLAIN command to understand a query's execution plan. Look for slow parts such as Sequential Scans or high cost numbers. If creating an index does not reduce the cost of the query plan, remove it. Example: EXPLAIN SELECT * FROM customers WHERE sign_up_date > 25;

Index join columns to improve table connections

Indexes on columns used for joining tables help Postgres avoid scanning tables in their entirety when connecting tables. Note that if a column is already a primary key, it is already indexed. For example, indexing customer_id in an orders table improves join performance with the customers table.

Basic data insertion with supabase-js

Insert data using supabase-js with: `const { data, error } = await supabase.from('movies').insert([{ name: 'The Empire Strikes Back', description: 'After the Rebels...' }, { name: 'Return of the Jedi', description: 'After a daring mission...' }])`. Pass an array of objects where each object represents a row.

Foreign key syntax for table relationships

To create a foreign key relationship between tables, use: `alter table movies add column category_id bigint references categories;`. This creates a column in the movies table that references the id column in the categories table.

Many-to-many relationship with join table

To create a many-to-many relationship (e.g., movies with multiple actors, actors in multiple movies), create a join table: `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);`. The join table contains foreign keys to both related tables.

Give your agent this brain