postgresql/column-types

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

Point datatype overview in PostgreSQL

PostgreSQL has a special datatype called 'point' to store geometric data representing a point in two-dimensional space. The point datatype is represented as a pair of (x, y) coordinates. The point expects to receive longitude first, followed by latitude.

Create table with point datatype

To create a table with a point column in Drizzle, import point from 'drizzle-orm/pg-core' and use it with the mode option. Example: location: point('location', { mode: 'xy' }).notNull()

Insert point data with mode xy

When inserting point data with mode 'xy', pass an object with x and y properties: await db.insert(stores).values({ name: 'Test', location: { x: -90.9, y: 18.7 } })

Insert point data with mode tuple

When inserting point data with mode 'tuple', pass an array with [x, y] coordinates: await db.insert(stores).values({ name: 'Test', location: [-90.9, 18.7] })

Insert point data using raw SQL

Point data can be inserted using raw SQL with the sql function: await db.insert(stores).values({ name: 'Test', location: sql`point(-90.9, 18.7)` })

PostgreSQL <-> operator for distance calculation

The <-> operator in PostgreSQL computes the distance between point objects. It can be used in queries to find nearest locations by coordinates.

Query nearest location by point distance

To find the nearest location by coordinates, use the <-> operator with getColumns to select all columns and calculate distance: const sqlDistance = sql`location <-> point(${point.x}, ${point.y})`; await db.select({ ...getColumns(stores), distance: sql`round((${sqlDistance})::numeric, 2)` }).from(stores).orderBy(sqlDistance).limit(1);

PostgreSQL <@ operator for point containment

The <@ operator in PostgreSQL checks if the first object is contained in or on the second object. It can be used to filter rows where a point location falls within a specified rectangular boundary defined by two diagonal points.

Filter points within rectangular boundary

To filter rows where a point location falls within a rectangular boundary, use the <@ operator with box: await db.select().from(stores).where(sql`${stores.location} <@ box(point(${point.x1}, ${point.y1}), point(${point.x2}, ${point.y2}))` )

pgvector extension installation for vector similarity search

To implement vector similarity search in PostgreSQL with Drizzle ORM, use the pgvector extension. Drizzle does not automatically create extensions, so you must create it manually by generating a custom migration file with `npx drizzle-kit generate --custom` and adding the SQL query `CREATE EXTENSION vector;`

Vector column type in Drizzle schema

The vector column type is imported from 'drizzle-orm/pg-core' and accepts a dimensions parameter. For example: `vector('embedding', { dimensions: 1536 })` creates a vector column named 'embedding' with 1536 dimensions, suitable for storing OpenAI embeddings.

HNSW and IVFFlat indexes for vector columns

To perform efficient vector similarity search, create an HNSW or IVFFlat index on the vector column. Use the index function with `.using('hnsw', table.embedding.op('vector_cosine_ops'))` syntax to create an HNSW index with cosine distance operator for vector similarity operations.

PostgreSQL defaultNow() for current timestamp

Use the `defaultNow()` method on a timestamp column to set the current timestamp as the default value in PostgreSQL. This generates a SQL DEFAULT clause with `now()` function.

PostgreSQL unix timestamp default with extract(epoch from now())

To set unix timestamp (seconds since 1970-01-01) as default on an integer column in PostgreSQL, use `.default(sql`extract(epoch from now())`)`. This returns the number of seconds since the Unix epoch.

PostgreSQL timestamp mode option

The `mode` option on timestamp columns defines how values are handled in the application. The default mode (not specified) returns Date objects; 'string' mode returns strings. Both are stored as timestamps in the database.

PostgreSQL unix timestamp default example

Example of setting unix timestamp default in PostgreSQL: ```ts import { sql } from 'drizzle-orm'; import { integer, pgTable, serial } from 'drizzle-orm/pg-core' export const users = pgTable('users', { id: serial('id').primaryKey(), timestamp: integer('timestamp') .notNull() .default(sql`extract(epoch from now())`), }); ```

Give your agent this brain