Skip to main content

Postgres - Piccolo (Python) hints

Piccolo column types

Postgres column types

Postgres type Python type Piccolo type Python example
timestamptz datetime.datetime Timestamptz(required=True) with timezone arrow.utcnow().datetime
timestamp(3) or timestamp(6) datetime.datetime Timestamp(required=True) without timezone arrow.utcnow().naive
uuid asyncpg.pgproto.pgproto.UUID UUID default: UUIDArg = UUID4()
json str on fetch JSON {"hello":"world"} or string on insert
jsonb str on fetch JSONB {"hello":"world"} or string on insert
bytea bytes Bytea b"test"
text or varchar str Text "test"
float4 or float8 float DoublePrecision 3.14
int4 or int8 int Integer 42
bool bool Boolean True or False

Postgres datetime default

last_parsed timestamp(3) DEFAULT '2000-01-01 00:00:00'::timestamp without time zone NOT NULL,

upload_date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,

Piccolo example

import arrow
class TestTable(Table, db=DB):
    temp_bool = Boolean(default=False)
    temp_int = Integer(default=42)
    temp_float = DoublePrecision(default=3.14)
    temp_text = Text(default="test")
    temp_bytes = Bytea(default=b"test")
    temp_json = JSON(default={"hello":"world"})
    temp_jsonb = JSONB(default={"hello":"world"})
    temp_uuid = UUID()
    temp_timestamp = Timestamp(default=arrow.utcnow().naive)
    temp_timestamptz = Timestamptz(default=arrow.utcnow().datetime)

results in

CREATE TABLE public.test_table (
	id serial4 NOT NULL,
	temp_bool bool DEFAULT false NOT NULL,
	temp_int int4 DEFAULT 42 NOT NULL,
	temp_float float8 DEFAULT 3.14 NOT NULL,
	temp_text text DEFAULT 'test'::text NOT NULL,
	temp_bytes bytea DEFAULT '\x3734363537333734'::bytea NOT NULL,
	temp_json json DEFAULT '{"hello": "world"}'::json NOT NULL,
	temp_jsonb jsonb DEFAULT '{"hello": "world"}'::jsonb NOT NULL,
	temp_uuid uuid DEFAULT uuid_generate_v4() NOT NULL,
	temp_timestamp timestamp DEFAULT '2025-08-05 13:00:24.314406'::timestamp without time zone NOT NULL,
	temp_timestamptz timestamptz DEFAULT '2025-08-05 15:00:24.314569+02'::timestamp with time zone NOT NULL,
	CONSTRAINT test_table_pkey PRIMARY KEY (id)
);

No comments to display

Back to top