Thread: Sub:column "" is of type bigint but expression is of type character varying
Sub:column "" is of type bigint but expression is of type character varying
From
Rama Krishnan
Date:
Hi All,
i have purged the old data from orginal table when i am restoring the data from archive table into orignal table i am getting the error
I am having table name called tickets
\d tickets
Column | Type | Collation | Nullable | Default
------------------------------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
ticket_purchase_no| bigint | | not null |
this below table contains more than 2 years old data
\d tickets_archive
Column | Type | Collation | Nullable | Default
------------------------------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
ticket_purchase_no| bigint | | not null |
insert into tickets select * from tickets_archive;
column "ticket_purchase_no" is of type bigint but expression is of type character varying
Regards
A.Rama Krishnan
Regards
A.Rama Krishnan
Re: Sub:column "" is of type bigint but expression is of type character varying
From
JITEN KUMAR SHAH
Date:
1. Please check you tickets_archive.ticket_purchase_no data type.
2. check if you are using correct schema.
2. check if you are using correct schema.
On 8/17/22 14:23, Rama Krishnan wrote:
Hi All,I am having table name called tickets\d ticketsColumn | Type | Collation | Nullable | Default------------------------------+-----------------------------+-----------+----------+---------id | bigint | | not null |ticket_purchase_no| bigint | | not null |this below table contains more than 2 years old data\d tickets_archiveColumn | Type | Collation | Nullable | Default------------------------------+-----------------------------+-----------+----------+---------id | bigint | | not null |ticket_purchase_no| bigint | | not null |i have purged the old data from orginal table when i am restoring the data from archive table into orignal table i am getting the errorinsert into tickets select * from tickets_archive;column "ticket_purchase_no" is of type bigint but expression is of type character varying
Regards
A.Rama Krishnan
Re: Sub:column "" is of type bigint but expression is of type character varying
From
Adrian Klaver
Date:
On 8/17/22 01:53, Rama Krishnan wrote: > Hi All, > > > i have purged the old data from orginal table when i am restoring the > data from archive table into orignal table i am getting the error > > > *insert into tickets select * from tickets_archive;* The above depends on: https://www.postgresql.org/docs/current/sql-insert.html "The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right. " If this is not the case then you can get mismatched columns where a varchar value is being inserted into an integer field. Verify that the table column order is the same for both tables. > > > column "*ticket_purchase_no*" is of type bigint but expression is of > type character varying > Regards > > A.Rama Krishnan -- Adrian Klaver adrian.klaver@aklaver.com
Re: Sub:column "" is of type bigint but expression is of type character varying
From
"David G. Johnston"
Date:
On Wed, Aug 17, 2022 at 5:13 AM Rama Krishnan <raghuldrag@gmail.com> wrote:
Hi All,I am having table name called tickets\d ticketsColumn | Type | Collation | Nullable | Default------------------------------+-----------------------------+-----------+----------+---------id | bigint | | not null |ticket_purchase_no| bigint | | not null |this below table contains more than 2 years old data\d tickets_archiveColumn | Type | Collation | Nullable | Default------------------------------+-----------------------------+-----------+----------+---------id | bigint | | not null |ticket_purchase_no| bigint | | not null |i have purged the old data from orginal table when i am restoring the data from archive table into orignal table i am getting the errorinsert into tickets select * from tickets_archive;column "ticket_purchase_no" is of type bigint but expression is of type character varying
This sequence seems impossible if executed all from the same psql session. So I presume you most likely aren't actually doing that, and so the psql output you show is meaningless since it doesn't show what the insert/select command is actually working with.
If you are, showing the results of "select * from {tickets|tickets_archive} limit 1" would be informative. Listing columns explicitly in the insert command and then putting an explicit cast on tickets_archive.ticket_purchase_no would also be interesting.
David J.