Sub:column "" is of type bigint but expression is of type character varying

1. Please check you tickets_archive.ticket_purchase_no data type.

2. check if you are using correct schema.

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

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 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 |

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;*

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.