public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: 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 16:55:29 -0400
Message-ID: <CANzqJaA6WG=E-wzzRJNWwgEhQZwKoX0+27hwZGXXr22CdNNTYw@mail.gmail.com> (raw)
In-Reply-To: <LV9PR02MB11231E84BBAF4D4357A94D86CE5062@LV9PR02MB11231.namprd02.prod.outlook.com>
References: <CANzqJaAz2doC3fLQOZ8Lemrp8Le63FN=8SfSjhHM3oSMRQF7aw@mail.gmail.com>
<LV9PR02MB11231E84BBAF4D4357A94D86CE5062@LV9PR02MB11231.namprd02.prod.outlook.com>
See, I knew it was something stupid.
On Wed, May 13, 2026 at 4:04 PM Hajek, Nick <Nick.Hajek@vishay.com> wrote:
> 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!
>
--
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: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Puzzling "INSERT has more expressions than target columns" error
In-Reply-To: <CANzqJaA6WG=E-wzzRJNWwgEhQZwKoX0+27hwZGXXr22CdNNTYw@mail.gmail.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