Insert bigint value into int column type Postgresql

Question

I need to insert some data from one table to another in Postgresql. Schema of the table I need to fill with data looks like this:

create table public.test_int_table(
    id                          int,
    description             text,
    hash_code               int
);

The issue is that id column in another table is of bigint type. So when I try to insert biting data into int column I get an exception:

insert into public.test_int_table
select * from public.test_table

SQL Error [22003]: ERROR: integer out of range

How can I trim bigint data for insert in Postgresql? Usual cast operator seems to work only for upcasting (from int to bigint and not otherwise). Id it not a constantly increasing value and it fits into int in most of the cases so I don’t really care about loosing precision here.

Why not create int columns as bigint type in public.test_int_table table ? — Oto Shavadze
– Oto Shavadze, Commented Feb 26, 2020 at 12:28
This table should be preserved for some time not to break compatibility that is why. — Cassie
– Cassie, Commented Feb 26, 2020 at 12:40

Gordon Linoff · Accepted Answer · 2020-02-26 12:25:34Z

1

This could be quite dangerous, because you might get duplicates. But you can use direct comparison and then handle the values that are too large. The following "trims" them to NULL:

insert into public.test_int_table (id, description, hash_code)
    select (case when id < 2^31 - 1 then id::int end) as id,
           description, hash_code
    from public.test_table;

Note that this might totally mess up the data. If you can change the value, then you could replace with a new value:

insert into public.test_int_table (id, description, hash_code)
    select dense_rank() over (order by id),
           description, hash_code
    from public.test_table;

answered Feb 26, 2020 at 12:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert bigint value into int column type Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related