Puzzling "INSERT has more expressions than target columns" error

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Puzzling "INSERT has more expressions than target columns" error
Date: 2026-05-13 19:57:13
Message-ID: CANzqJaAz2doC3fLQOZ8Lemrp8Le63FN=8SfSjhHM3oSMRQF7aw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Thiemo Kellner 2026-05-13 20:03:10 Puzzling "INSERT has more expressions than target columns" error
Previous Message Laurenz Albe 2026-05-12 07:41:21 Re: PostgreSQL Repos and archiving