21 lines
1.1 KiB
SQL
21 lines
1.1 KiB
SQL
CREATE TABLE "posts" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"slug" varchar(200) NOT NULL,
|
|
"title" text NOT NULL,
|
|
"summary" text NOT NULL,
|
|
"content" text NOT NULL,
|
|
"tags" text[] DEFAULT ARRAY[]::text[] NOT NULL,
|
|
"status" varchar(20) DEFAULT 'draft' NOT NULL,
|
|
"reading_minutes" integer DEFAULT 1 NOT NULL,
|
|
"cover" text,
|
|
"description" text,
|
|
"published_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "posts_slug_unique_idx" ON "posts" USING btree ("slug");--> statement-breakpoint
|
|
CREATE INDEX "posts_status_published_at_idx" ON "posts" USING btree ("status","published_at" DESC NULLS LAST);--> statement-breakpoint
|
|
CREATE INDEX "posts_tags_gin_idx" ON "posts" USING gin ("tags");--> statement-breakpoint
|
|
ALTER TABLE "posts" ADD CONSTRAINT "posts_status_check" CHECK ("status" IN ('draft', 'published'));--> statement-breakpoint
|
|
ALTER TABLE "posts" ADD CONSTRAINT "posts_slug_format_check" CHECK ("slug" ~ '^[a-z0-9]+(-[a-z0-9]+)*$'); |