NeonN
Neon•2w ago•
2 replies
managerial-maroon

Problem of "copy database schema"

In "tables" tab, "settings" (gear at left-lower corner of second column), there is a "copy database schema" function. The generated schema seems cannot be reproduced in a new database.

This is the generated schema of my database, as an example.

CREATE SCHEMA "public";
CREATE TABLE "courses" (
    "course_id" varchar(9) PRIMARY KEY,
    "syllabus" jsonb
);
CREATE TABLE "job_titles" (
    "token" varchar(128) CONSTRAINT "job_titles_token_uniq" UNIQUE,
    "openai_text_embedding_3_small" vector(1536)
);
CREATE TABLE "knowledge_points" (
    "token" varchar(128),
    "openai_text_embedding_3_small" vector(1536),
    "course_id" varchar(9),
    CONSTRAINT "knowledge_points_pk" PRIMARY KEY("course_id","token")
);
CREATE TABLE "program_plan" (
    "course_type" varchar(16),
    "course_id" varchar(9),
    "course_name" varchar(48),
    "semester" smallint,
    "institution" varchar(16),
    "credit" smallint,
    "module" varchar(16)
);
CREATE UNIQUE INDEX "courses_pkey" ON "courses" ("course_id");
CREATE INDEX "job_titles_openai_text_embedding_3_small_idx" ON "job_titles" USING hnsw ("openai_text_embedding_3_small");
CREATE UNIQUE INDEX "job_titles_token_uniq" ON "job_titles" ("token");
CREATE INDEX "knowledge_keypoints_openai_text_embedding_3_small_idx" ON "knowledge_points" USING hnsw ("openai_text_embedding_3_small");
CREATE UNIQUE INDEX "knowledge_points_pk" ON "knowledge_points" ("course_id","token");


However, there are 3 problems if trying to reproduce in the new database:

1. The plugin CREATE EXTENSION IF NOT EXISTS vector; is not included.
2. hnsw doesn't include the method name, which is hnsw ("openai_text_embedding_3_small" vector_cosine_ops)
3. Objects courses_pkey, job_titles_token_uniq, knowledge_points_pk are already created in table definition. Therefore the 3 separate query of CREATE UNIQUE INDEX will fail and block the subsequent queries.

I'm a beginner of PostgreSQL, so I'm not sure whether it's a bug or standard behavior.
Was this page helpful?