0

I have created a table called Markers. Below is the .sql file.

CREATE TABLE markers (
    id SERIAL PRIMARY KEY ,
    ip_address varchar(25),
    Hostname varchar(255),
    Continent varchar(255),
    Country varchar(255),
    Capital varchar(255),
    State varchar (255),
    City_Location varchar(255)
);

I have a text file which contains ip addresses. The file looks like this.

8.8.8.8
23.64.0.0
31.0.0.0
50.22.0.0
54.244.0.0
54.72.0.0
54.80.0.0

The information belonging to each IP address is stored in the table. If I want to gather data for ip 8.8.8.8, I can write a query like this

SELECT * FROM markers WHERE ip_address='8.8.8.8';

and similarly I can do for other IP. But this is time consuming since I have to write a query for each IP address.

Instead, is there any way that I can read the IP address from text file and execute the query for all IP addresses in one shot?

Any help with this would be appreciated. Thank you.

Gabriel's Messanger
  • 3,213
  • 17
  • 31
Swatesh Pakhare
  • 7
  • 4
  • You can't do that with postgres alone. Just write a script (in any language you like) that will read the file and execute the query. – freakish Apr 11 '16 at 20:56
  • 2
    Doesnt answer the question but you might be interested in http://www.postgresql.org/docs/9.1/static/datatype-net-types.html – Mihai Apr 11 '16 at 20:56
  • @freakish I am using BASH script. Is it possible to do with BASH? Or should I go with php ? – Swatesh Pakhare Apr 11 '16 at 21:00
  • @SwateshPakhare It certainly is possible to do it in bash. Something like pipeing `cat` line by line to `psql`. I don't recommend using php for that. Python would be a nice alternative. – freakish Apr 11 '16 at 21:01

1 Answers1

1

You can do it using psql and issuing the following commands (tailor to your particular case, reading PG manual on "COPY FROM"):

create temp table ip_addresses (ip_address text);
copy ip_addresses from stdin csv; -- pipe your text file here
select * from markers where ip_address in (select * from ip_addresses);
ADEpt
  • 5,504
  • 1
  • 25
  • 32