Greetings,

I have a table with a column with type tsvector. It contains the
result of to_tsvector() of varchar field in the table. What I'd like
to do is be able to search through the table and find all of the
distinct IP addresses. Any idea how to turn:

SELECT message_index_col FROM systemevents LIMIT 10;
message_index_col
---------------------------------------------------------------------------------------------------------------------------------
'leas':4 'return':2
'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
'00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
'ip':4 'leas':2,5 'ident':7 'hardwar':1
'leas':2 'choos':1 'address':5 'request':4
'leas':2 'return':1 '65.110.236.113':3
'00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
'10.10.94.126':10
'00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
'10.10.94.126':12 '65.110.236.113':3
'451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
'tempfail':12 'n29c3q08020087':1 'kgander@iastate.edu':4

into

IP_ADDRESSES
-------------------------
65.110.236.113
10.10.94.126

Thanks for the help...

Justin Funk

Search Discussions

  • Lubomir Petrov at Mar 12, 2009 at 6:44 pm
    Hi,

    Maybe you can use something like the following:

    test=# select * from t1;
    t
    -----------------------------------------------------------------------
    'leas':4 'return':2
    'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
    '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
    'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
    'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
    'ip':4 'leas':2,5 'ident':7 'hardwar':1
    'leas':2 'choos':1 'address':5 'request':4
    'leas':2 'return':1 '65.110.236.113':3
    '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
    '10.10.94.126':10
    '00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
    '10.10.94.126':12 '65.110.236.113':3
    '451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
    'tempfail':12 'n29c3q08020087':1 'kgander@iastate.edu':4
    (14 rows)

    test=#
    test=#
    test=# select
    test-# distinct ip_address
    test-# from
    test-# (select substring(t from E'\\d+\\.\\d+\\.\\d+\\.\\d+') as
    ip_address from t1) as t
    test-# where ip_address is not null;
    ip_address
    ----------------
    10.10.94.126
    65.110.236.113
    (2 rows)


    test=#
    test=#


    Of course you should make the regular expression stricter, but this is
    the idea.

    Hope that helps.


    Regards,
    Lubomir Petrov


    Justin Funk wrote:
    Greetings,

    I have a table with a column with type tsvector. It contains the
    result of to_tsvector() of varchar field in the table. What I'd like
    to do is be able to search through the table and find all of the
    distinct IP addresses. Any idea how to turn:

    SELECT message_index_col FROM systemevents LIMIT 10;
    message_index_col
    ---------------------------------------------------------------------------------------------------------------------------------
    'leas':4 'return':2
    'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
    '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
    'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
    'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
    'ip':4 'leas':2,5 'ident':7 'hardwar':1
    'leas':2 'choos':1 'address':5 'request':4
    'leas':2 'return':1 '65.110.236.113':3
    '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
    '10.10.94.126':10
    '00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
    '10.10.94.126':12 '65.110.236.113':3
    '451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
    'tempfail':12 'n29c3q08020087':1 'kgander@iastate.edu':4

    into

    IP_ADDRESSES
    -------------------------
    65.110.236.113
    10.10.94.126

    Thanks for the help...

    Justin Funk

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 9, '09 at 2:48p
activeMar 12, '09 at 6:44p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Justin Funk: 1 post Lubomir Petrov: 1 post

People

Translate

site design / logo © 2022 Grokbase