Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite

I have a table which contains three field ip_address,contary_name,region_name.

         ip_address         countary_name      region_name
         100.12.34.19           xyz               jk                 
         100.12.34.23           xyz               jk
         170.63.34.41           abc               mn
         170.63.34.42           abc               mn
         170.63.34.43           abc               mn

so i need some query like this :

select distinct substring('rex_exp') as common_ip,
       substring('rex_exp') as ip_part,countary_name,region_name
  from table;

and output is 

         common_ip      ip_part        countary_name      region_name
         100.12.34.     19,23           xyz               jk                 
         170.63.34.     41,42,43        abc               mn
share|improve this question
 
What datatype is ip_address? Is that a varchar/text column or an inet type? –  a_horse_with_no_name Jun 21 '12 at 6:11
 
@a_horse_with_no_name: these all are of character varying type. –  vikas malik Jun 21 '12 at 6:44
add comment

1 Answer

up vote 1 down vote

Assuming ip_address is of type text (or varchar) then this should do it:

select common_ip,
       string_agg(ip_part, ',')
       country_name,
       region_name
from (
  select substring(ip_address from '[0-9]{2,3}\.[0-9]{2,3}\.[0-9]{2,3}') as common_ip,
         substring(ip_address from '[0-9]{2,3}$') as ip_part,
         country_name, 
         region_name
  from some_table
) t
group by common_ip, country_name, region_name;

For old Postgres versions you can replace string_agg with a combination of array_agg and array_to_string

select common_ip,
       array_to_string(array_agg(ip_part), ',')
       country_name,
       region_name
from (
  select substring(ip_address from '[0-9]{2,3}\.[0-9]{2,3}\.[0-9]{2,3}') as common_ip,
         substring(ip_address from '[0-9]{2,3}$') as ip_part,
         country_name, 
         region_name
  from some_table
) t
group by common_ip, country_name, region_name;
share|improve this answer
 
this query throwing error : function string_agg(text, unknown) does not exist. No function matches the given name and argument types. You might need to add explicit type casts. –  vikas malik Jun 21 '12 at 7:05
 
I m using Postgres 8.4 so there is no string_agg function. –  vikas malik Jun 21 '12 at 7:35
 
@vikasmalik: see my edit –  a_horse_with_no_name Jun 21 '12 at 11:21
 
thanks for reply –  vikas malik Jun 22 '12 at 7:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.