CODEWITHSUNDEEP
sqlpostgresqlwhere-clause
Swatesh Pakhare
Swatesh Pakhare

Reputation: 7

Querying PostgreSQL using WHERE clause

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.

Upvotes: 0

Views: 894

Answers (1)

ADEpt
ADEpt

Reputation: 5552

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

Upvotes: 1

Related Questions