CODEWITHSUNDEEP
postgresqlintegerconcatenation
Kumar Rajput
Kumar Rajput

Reputation: 125

concat two int values in postgresql

I have 7 integer values (with 3,1,3,4,4,5,4 digits respectively) and I have to concatenate them to a single integer (i.e a 24 digit no.) . I tried to do it like this 

create or replace function gen_id(int,int,int,int,int,int,int) returns bigint as $$
declare
    id bigint;
begin
    id = $1 * 1000000000000000000000 + $2 * 100000000000000000000 + $3 * 100000000000000000 + $4 * 10000000000000 + $5 * 1000000000 + $6 * 10000 + $7;
    return id;
end;
$$ language plpgsql;

select * from gen_id(100,1,101,1000,1001,10001,1000);

But when I execute it I get error: bigint out of range . Is there any other better way to do it ?
thanks

Upvotes: 10

Views: 36410

Answers (2)

Pedro Ulises
Pedro Ulises

Reputation: 1

As I can concatenate a string to integer

SELECT REPLACE(STR(ISNULL(MAX(usuarioid) + 1, 1), 6), ' ', '0') FROM usuarios

usuarioid is string + 1

Upvotes: 0

vyegorov
vyegorov

Reputation: 22905

What about:

SELECT CAST(CAST(num1 AS text)||CAST(num2 AS text)||... AS numeric(24,0))

If you happen to have your IDs in some table, then you can do:

SELECT CAST(string_agg(CAST(num AS text), '') AS numeric(24,0)) FROM srctab;

Upvotes: 15

Related Questions