Postgres upsert

Question

I am trying to do update my database tables via upserts from a postgres-to-postgres foreign data wrapper.

I have two code snippets below. The first is using the constraint on the primary key and the latter is using the actual primary key column. The first code is missing a few post for all the updated tables (~10-20 out of 100,000s) and the latter does the complete upsert as expected.

The latter code is working, but i would like to understand what the difference is between the two?

First code snippet (primary key constaint)

INSERT INTO myschema.mytable AS t1 (
    SELECT 
        column1,
        column2
    FROM fdwschema.fdwtable 
)
ON CONFLICT ON CONSTRAINT mytable_pkey
    DO UPDATE SET...

Second code snippit (primary key column)

INSERT INTO myschema.mytable AS t1 (
    SELECT 
        column1,
        column2
    FROM fdwschema.fdwtable 
)
ON CONFLICT ON (pk_column)
    DO UPDATE SET...

EDIT: By request from a_horse_with_no_name.

All index and foreign keys are removed prior to inserts.

Empty tables are created if not exists and primary key is remove if exists and added again.

An example can be seen below. The combined sql does thing in 3 steps:

1. Create all empty table if not exists. Remove all pk and add it again. Remove all index/fk.

2. Update all tables.

3. Create all index/fk.

    CREATE TABLE IF NOT EXISTS jupiter.code AS
        (
            SELECT 
                code,
                codetype,
                shorttext,
                longtext,
                sortno,
                insertdate,
                updatedate
            FROM geus_fdw.code
            LIMIT 0
        )
    ;
   
    ALTER TABLE jupiter.code 
        DROP CONSTRAINT IF EXISTS code_pkey CASCADE
    ;
   
    ALTER TABLE jupiter.code 
        ADD PRIMARY KEY (code, codetype)
    ;
   
    ALTER TABLE jupiter.code 
        DROP CONSTRAINT IF EXISTS fk_codetype_code CASCADE
    ;
   
    DROP INDEX IF EXISTS code_code_idx;
   
    DROP INDEX IF EXISTS code_codetype_idx;
   
    INSERT INTO jupiter.code AS t1
        (
            SELECT 
                code,
                codetype,
                shorttext,
                longtext,
                sortno,
                insertdate,
                updatedate
            FROM geus_fdw.code t2
        ) 
        ON CONFLICT (code, codetype) --ON CONSTRAINT code_pkey 
            DO UPDATE SET 
                code = excluded.code,
                codetype = excluded.codetype,
                shorttext = excluded.shorttext,
                longtext = excluded.longtext,
                sortno = excluded.sortno,
                insertdate = excluded.insertdate,
                updatedate = excluded.updatedate
            WHERE COALESCE(t1.updatedate, t1.insertdate) != 
                COALESCE(excluded.updatedate, excluded.insertdate)
    ;
   
    CREATE INDEX IF NOT EXISTS code_code_idx
        ON jupiter.code (code)
    ;
   
    CREATE INDEX IF NOT EXISTS code_codetype_idx
        ON jupiter.code(codetype)
    ;
   
    ALTER TABLE jupiter.code 
        ADD CONSTRAINT fk_codetype_code FOREIGN KEY (codetype) 
        REFERENCES jupiter.codetype (codetype)
    ;