Querying PostgreSQL using WHERE clause

Question

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.

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
– freakish, Commented Apr 11, 2016 at 20:56
Doesnt answer the question but you might be interested in postgresql.org/docs/9.1/static/datatype-net-types.html — Mihai
– Mihai, Commented Apr 11, 2016 at 20:56
@freakish I am using BASH script. Is it possible to do with BASH? Or should I go with php ? — Swatesh Pakhare
– Swatesh Pakhare, Commented Apr 11, 2016 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
– freakish, Commented Apr 11, 2016 at 21:01

ADEpt · Accepted Answer · 2016-04-12 15:47:16Z

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);

edited Apr 12, 2016 at 15:47

answered Apr 12, 2016 at 13:56

ADEpt

5,5521 gold badge28 silver badges32 bronze badges

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

2 Comments

Swatesh Pakhare Over a year ago

Can I do this without using a text file ?

Swatesh Pakhare Over a year ago

I figured out how to do it without using text file. I replaced the second line of your code with this copy ip_addresses from PROGRAM ' #your commands ';

Collectives™ on Stack Overflow

Querying PostgreSQL using WHERE clause

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related