public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Hajek, Nick <Nick.Hajek@vishay.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Puzzling "INSERT has more expressions than target columns" error
Date: Wed, 13 May 2026 20:04:37 +0000
Message-ID: <LV9PR02MB11231E84BBAF4D4357A94D86CE5062@LV9PR02MB11231.namprd02.prod.outlook.com> (raw)
In-Reply-To: <CANzqJaAz2doC3fLQOZ8Lemrp8Le63FN=8SfSjhHM3oSMRQF7aw@mail.gmail.com>
References: <CANzqJaAz2doC3fLQOZ8Lemrp8Le63FN=8SfSjhHM3oSMRQF7aw@mail.gmail.com>
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@gmail.com>
Sent: Wednesday, May 13, 2026 2:57 PM
To: Pgsql-admin <pgsql-admin@lists.postgresql.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!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Nick.Hajek@vishay.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Puzzling "INSERT has more expressions than target columns" error
In-Reply-To: <LV9PR02MB11231E84BBAF4D4357A94D86CE5062@LV9PR02MB11231.namprd02.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox