4

What is the easiest way to query from .csv file ?

Ex: I have a list of 1000 email addresses in emails.csv file.

I want to query all users with emails that are in emails.csv file ex: 

SELECT * 
FROM users 
WHERE email IN (emails.csv)

Is there a way to do like this something or I need to create a script. If some script is needed, can you please provide some example.

Fi3n1k
  • 863
  • 3
  • 12
  • 20
  • you need to create a table and copy it from csv – Vao Tsun Jan 31 '18 at 14:25
  • If the file is located on the databases server, you could use a it through the [file_fdw](https://www.postgresql.org/docs/current/static/file-fdw.html) extension –  Jan 31 '18 at 14:40

1 Answers1

5

you need to create a table and copy it from csv, smth like:

t=# create table csv(i int,email text);
CREATE TABLE
t=# copy csv(email) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> q
>> w
>> \.
COPY 2
t=# select * from csv;
 i | email
---+-------
   | q
   | w
(2 rows)

but In your case you copy from file, not STDIN, eg

copy csv(email) from '/path/to/file';
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • hmm I don't think the company wants to add a table for this purpose. Thanks anyway for the suggestion – Fi3n1k Jan 31 '18 at 14:38
  • 1
    @Fi3n1k: well, queries can only work with tables. There is no way you can use those CSV values unless you create some kind of table. You could put it into a temporary table if you want –  Jan 31 '18 at 14:41
  • 1
    Depending on your case, you could use the `CREATE TEMPORARY TABLE` command to create a table that will only be accessible to the current transaction and will cease to exist when the transaction is over. There are plenty of tricks that can be used on top of that one. – Haroldo_OK Aug 01 '22 at 18:16