Let's break down what's going on here and fix those errors step by step.

First, your issues stem from two key PostgreSQL behaviors: strict case sensitivity for quoted identifiers, and schema scoping for database objects like sequences.

Why the "column does not exist" error happens #

PostgreSQL automatically converts unquoted identifiers (like column or table names) to lowercase. Since your column is defined as "ExampleID" (mixed-case), writing ExampleID without quotes tells PostgreSQL to look for a lowercase exampleid column—which doesn't exist. You must wrap this column name in double quotes every time you reference it.

Why the "cannot use column reference" error happens #

This error is misleading, but it’s almost certainly because your sequence and table live in different schemas. Your table is in schema.example, but you created the sequence with just CREATE SEQUENCE example_id_seq MINVALUE 3—which defaults to putting the sequence in the public schema. When trying to reference this cross-schema sequence in your table’s default value, PostgreSQL misinterprets the expression as a column reference instead of a sequence call.

Correct Step-by-Step Solution #

1. Move the sequence to the same schema as your table

First, clean up the existing sequence (if it’s in public), then recreate it in your target schema:

-- Delete the public schema sequence if it exists
DROP SEQUENCE IF EXISTS public.example_id_seq;
-- Create the sequence in the same schema as your table
CREATE SEQUENCE schema.example_id_seq MINVALUE 3;

2. Set the default value correctly

Use double quotes for the column name, specify the full schema path for both the table and sequence:

ALTER TABLE schema.example ALTER COLUMN "ExampleID" SET DEFAULT nextval('schema.example_id_seq');

(The COLUMN keyword is optional, but including it makes the statement more readable.)

3. Link the sequence to your primary key (optional but recommended)

To make PostgreSQL recognize the sequence as the official source for your auto-incrementing primary key (so tools like \d will show the relationship), run:

ALTER SEQUENCE schema.example_id_seq OWNED BY schema.example."ExampleID";

Verify the Fix #

Check that the default value is applied correctly with this query:

SELECT column_name, column_default 
FROM information_schema.columns 
WHERE table_schema = 'schema' 
  AND table_name = 'example' 
  AND column_name = 'ExampleID';

内容的提问来源于stack exchange，提问作者eliezra236