concat two int values in postgresql

Question

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

By "concatenation", do you mean string concatenation (after casting appropriately)? — user554546
– user554546, Commented Jun 25, 2012 at 20:44

vyegorov · Accepted Answer · 2012-06-25 20:53:45Z

15

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;

edited Jun 25, 2012 at 20:53

answered Jun 25, 2012 at 20:45

vyegorov

23k7 gold badges61 silver badges76 bronze badges

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

5 Comments

Kumar Rajput Over a year ago

problem is, in my database the id is stored as numeric(24,0) so I cannot update the column with string value. I need numeric value .Is this possible ?

vyegorov Over a year ago

@KumarRajput, just cast the result back into numeric(24,0). Updated.

user554546 Over a year ago

+1. Also, as an FYI (since you're using PostgreSQL), you can do casting using the slightly more convenient :: operator. In other words, CAST(num1 AS text) is the same as num1::text.

vyegorov Over a year ago

@JackManey, true. I prefer to use CAST() on SO as it is a standard operator while :: is a PostgreSQL specific one.

Erwin Brandstetter Over a year ago

The second query is bound to fail without an ORDER BY clause.

Pedro Ulises · Accepted Answer · 2014-07-28 16:03:05Z

0

As I can concatenate a string to integer

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

usuarioid is string + 1

answered Jul 28, 2014 at 16:03

Pedro Ulises

11 bronze badge

Collectives™ on Stack Overflow

concat two int values in postgresql

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related