Subject: Puzzling "INSERT has more expressions than target columns" error

From: Ron Johnson <[email-masked]>
Date: Wed, 13 May 2026 15:57:13 -0400

PG 17.9

I imported a foreign schema, and then wanted to create a local copy.  Thus:

TAPb=# CREATE TABLE l_tap35.batch (LIKE f_tap35.batch EXCLUDING ALL);
CREATE TABLE

But then:

TAPb=# INSERT INTO l_tap35.lockbox SELECT * FROM f_tap35.batch;
ERROR:  INSERT has more expressions than target columns
LINE 1: INSERT INTO l_tap35.lockbox SELECT * FROM f_tap35.batch;
                                           ^
Ok, maybe the sequence on the PK field is causing a problem?

But that doesn't seem to be the case.

TAPb=# select count(*)
from pg_attribute
where attrelid = (SELECT oid
                 FROM pg_class
                 where relname = 'batch'
                 AND relnamespace::regnamespace::text = 'f_tap35')
TAPb-#   AND attnum > 0;
 count
-------
   132
(1 row)

TAPb=# select count(*)
from pg_attribute
where attrelid = (SELECT oid
                 FROM pg_class
                 where relname = 'batch'
                 AND relnamespace::regnamespace::text = 'l_tap35')
  AND attnum > 0;
 count
-------
   132
(1 row)

TAPb=# SELECT f.attname, f.attnum, f.atttypid, (f.attname = l.attname),
(f.atttypid = l.atttypid)
FROM (select attname, attnum, atttypid, attidentity, attgenerated
      from pg_attribute
      where attrelid = (SELECT oid
                        FROM pg_class
                        where relname = 'batch'
                        AND relnamespace::regnamespace::text = 'f_tap35')) f
   , (select attname, attnum, atttypid, attidentity, attgenerated
      from pg_attribute
      where attrelid = (SELECT oid
                        FROM pg_class
                        where relname = 'batch'
                        AND relnamespace::regnamespace::text = 'l_tap35')) l
WHERE f.attnum = l.attnum
  AND f.attnum > 0
ORDER BY f.attnum;
                 attname                  | attnum | atttypid | ?column? |
?column?
------------------------------------------+--------+----------+----------+----------
 batch_id                                 |      1 |       20 | t        | t
 number                                   |      2 |       20 | t        | t
 batch_file_type_code_id                  |      3 |       20 | t        | t
 import_status_code_id                    |      4 |       20 | t        | t
 batch_identifier                         |      5 |     1043 | t        | t
 paired_batch_id                          |      6 |       20 | t        | t
 file_sequence_number                     |      7 |     1700 | t        | t
 start_time                               |      8 |     1114 | t        | t
 export_time                              |      9 |     1114 | t        | t
 balanced                                 |     10 |     1043 | t        | t
 deposit_time                             |     11 |     1114 | t        | t
 check_count                              |     12 |     1700 | t        | t
 check_total                              |     13 |     1700 | t        | t
 doc_count                                |     14 |     1700 | t        | t
 credit_date                              |     15 |     1114 | t        | t
 invoice_total                            |     16 |     1700 | t        | t
 invoice_count                            |     17 |     1700 | t        | t
 batch_limit                              |     18 |     1700 | t        | t
 association_type                         |     19 |     1043 | t        | t
 truncation_flag                          |     20 |       16 | t        | t
 eod_status                               |     21 |     1043 | t        | t
 ocr_count                                |     22 |     1700 | t        | t
 unprocessable_items                      |     23 |     1700 | t        | t
 no_check_items                           |     24 |     1700 | t        | t
 non_systemic_character_count             |     25 |       20 | t        | t
 systemic_character_count                 |     26 |       20 | t        | t
 repair_count                             |     27 |       20 | t        | t
 doctus_page_count                        |     28 |     1700 | t        | t
 front_image_count                        |     29 |     1700 | t        | t
 rear_image_count                         |     30 |     1700 | t        | t
 front_ocr_count                          |     31 |     1700 | t        | t
 rear_ocr_count                           |     32 |     1700 | t        | t
 batch_status                             |     33 |     1700 | t        | t
 doctus_character_count                   |     34 |       20 | t        | t
 stop_check_count                         |     35 |     1700 | t        | t
 bank_name                                |     36 |     1043 | t        | t
 affiliate                                |     37 |     1043 | t        | t
 pass1_mach_no                            |     38 |     1700 | t        | t
 zip_filename                             |     39 |     1043 | t        | t
 image_path                               |     40 |     1043 | t        | t
 lockbox_id                               |     41 |       20 | t        | t
 site_code_id                             |     42 |       20 | t        | t
 batch_mode_code_id                       |     43 |       20 | t        | t
 processing_bank_id                       |     44 |     1700 | t        | t
 weekend_reporting_date                   |     45 |     1082 | t        | t
 cds_deposit_time                         |     46 |     1043 | t        | t
 cds_file_create_time                     |     47 |     1043 | t        | t
 cds_archive_time                         |     48 |     1114 | t        | t
 check_21_count                           |     49 |     1700 | t        | t
 check_21_total                           |     50 |     1700 | t        | t
 arc_count                                |     51 |     1700 | t        | t
 arc_total                                |     52 |     1700 | t        | t
 arc_suspect_count                        |     53 |     1700 | t        | t
 arc_suspect_total                        |     54 |     1700 | t        | t
 pending_count                            |     55 |     1700 | t        | t
 pending_total                            |     56 |     1700 | t        | t
 duplicate_count                          |     57 |     1700 | t        | t
 duplicate_total                          |     58 |     1700 | t        | t
 item_state                               |     59 |     1700 | t        | t
 arc_status                               |     60 |     1700 | t        | t
 cds_batch_number                         |     61 |       20 | t        | t
 cds_full_batch_number                    |     62 |       20 | t        | t
 rebatching_source                        |     63 |     1043 | t        | t
 rebatching_rule                          |     64 |     1043 | t        | t
 coupon_count                             |     65 |     1700 | t        | t
 correspondence_count                     |     66 |     1700 | t        | t
 envelope_count                           |     67 |     1700 | t        | t
 cds_system_time                          |     68 |     1114 | t        | t
 cds_cut_off_date                         |     69 |     1082 | t        | t
 work_flow                                |     70 |     1700 | t        | t
 inbound_batch_number                     |     71 |     1043 | t        | t
 inbound_file_name                        |     72 |     1043 | t        | t
 inbound_receipt_time                     |     73 |     1114 | t        | t
 third_party_provider                     |     74 |     1043 | t        | t
 mail_out_state                           |     75 |     1700 | t        | t
 exported_on                              |     76 |     1114 | t        | t
 aux_on_us                                |     77 |     1043 | t        | t
 tms_check_count                          |     78 |     1700 | t        | t
 tms_check_total                          |     79 |     1700 | t        | t
 tms_invoice_total                        |     80 |     1700 | t        | t
 tms_invoice_count                        |     81 |     1700 | t        | t
 retention_date                           |     82 |     1082 | t        | t
 archive_front_check_image_count          |     83 |     1700 | t        | t
 archive_rear_check_image_count           |     84 |     1700 | t        | t
 archive_front_invoice_image_count        |     85 |     1700 | t        | t
 archive_rear_invoice_image_count         |     86 |     1700 | t        | t
 archive_front_coupon_image_count         |     87 |     1700 | t        | t
 archive_rear_coupon_image_count          |     88 |     1700 | t        | t
 archive_front_correspondence_image_count |     89 |     1700 | t        | t
 archive_rear_correspondence_image_count  |     90 |     1700 | t        | t
 archive_front_envelope_image_count       |     91 |     1700 | t        | t
 archive_rear_envelope_image_count        |     92 |     1700 | t        | t
 backload_manifest_id                     |     93 |       20 | t        | t
 cds_weekend_reporting_date               |     94 |     1082 | t        | t
 batch_output_type                        |     95 |     1700 | t        | t
 bank_retention_on                        |     96 |     1114 | t        | t
 customer_retention_on                    |     97 |     1114 | t        | t
 lockbox_retention_on                     |     98 |     1114 | t        | t
 arc_weekend_reporting_date               |     99 |     1082 | t        | t
 virutal_endorsement_date                 |    100 |     1082 | t        | t
 capture_source                           |    101 |     1700 | t        | t
 consolidation_number                     |    102 |     1043 | t        | t
 doc_group                                |    103 |     1700 | t        | t
 deposit_tran_code                        |    104 |     1043 | t        | t
 deposit_rt_number                        |    105 |     1043 | t        | t
 retry_count                              |    106 |     1700 | t        | t
 doc_archive_img_count_exclude_no_image   |    107 |     1700 | t        | t
 processing_date_number                   |    108 |     1043 | t        | t
 ocr_item_state                           |    109 |     1700 | t        | t
 ocr_started_on                           |    110 |     1114 | t        | t
 ocr_finished_on                          |    111 |     1114 | t        | t
 ems_cutoff_time                          |    112 |     1114 | t        | t
 watchlist_item_state                     |    113 |     1700 | t        | t
 rdc_number                               |    114 |       20 | t        | t
 rdc_created_on                           |    115 |     1114 | t        | t
 rdc_created_by                           |    116 |       20 | t        | t
 holdover_period                          |    117 |     1700 | t        | t
 holdover_count                           |    118 |     1700 | t        | t
 custom_userfield_234                     |    119 |     1700 | t        | t
 custom_userfield_235                     |    120 |     1700 | t        | t
 custom_userfield_236                     |    121 |     1700 | t        | t
 custom_userfield_237                     |    122 |     1700 | t        | t
 custom_userfield_238                     |    123 |     1700 | t        | t
 custom_userfield_239                     |    124 |     1700 | t        | t
 custom_userfield_240                     |    125 |     1043 | t        | t
 custom_userfield_241                     |    126 |     1043 | t        | t
 created_on                               |    127 |     1114 | t        | t
 created_by                               |    128 |       20 | t        | t
 modified_on                              |    129 |     1114 | t        | t
 modified_by                              |    130 |       20 | t        | t
 process_date                             |    131 |     1082 | t        | t
 partition_type                           |    132 |     1700 | t        | t
(132 rows)

There are 132 columns in the foreign table and the local copy, all column
names match, data types match, and the remote table does not have any
generated columns.

I'm certainly overlooking something but don't see it.

Thanks

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Thiemo Kellner <[email-masked]>
Date: Wed, 13 May 2026 22:03:10 +0200

Hi

Did you make sure, the order of the columns is identical? Well, maybe that should result in a different error.

My two dimes

Thiemo

---

From: "Hajek, Nick" <[email-masked]>
Date: Wed, 13 May 2026 20:04:37 +0000

You're inserting into a table named lockbox but I don't see the schema for lockbox anywhere in your post.

Nick
________________________________
From: Ron Johnson <[email-masked]>
Sent: Wednesday, May 13, 2026 2:57 PM
To: Pgsql-admin <[email-masked]>
Subject: Puzzling "INSERT has more expressions than target columns" error

[ External Email ]
PG 17.9

But then:

TAPb=# select count(*)
from pg_attribute

TAPb=# select count(*)
from pg_attribute

TAPb=# SELECT f.attname, f.attnum, f.atttypid, (f.attname = l.attname), (f.atttypid = l.atttypid)
                 attname                  | attnum | atttypid | ?column? | ?column?

There are 132 columns in the foreign table and the local copy, all column names match, data types match, and the remote table does not have any generated columns.

Thanks

---

From: Ron Johnson <[email-masked]>
Date: Wed, 13 May 2026 16:55:29 -0400

See, I knew it was something stupid.

On Wed, May 13, 2026 at 4:04 PM Hajek, Nick <[email-masked]> wrote:

> *Nick*
> ------------------------------
> *From:* Ron Johnson <[email-masked]>
> *Sent:* Wednesday, May 13, 2026 2:57 PM
> *To:* Pgsql-admin <[email-masked]>
> *Subject:* Puzzling "INSERT has more expressions than target columns"
> error
>
> [ External Email ]
> PG 17.9
>
>  batch_id                                 |      1 |       20 | t        |
> t
>  number                                   |      2 |       20 | t        |
> t