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

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 <ronljohnsonjr(at)gmail(dot)com>
Sent: Wednesday, May 13, 2026 2:57 PM
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Puzzling "INSERT has more expressions than target columns" error

[ External Email ]
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!