How can I save records in psql during iteration in a file

Question

I have a file that contains just id. the id matches one of the columns in table. I want to extract records in that column and get the result in csv file. I don't want to create another table.

I have docid.txt file with the following data:

12000072
112073
11279
1182
11188
11182094
82099
11102

Each of these correspond to a nameid column in student table. student table has:

name    nameid      dob         performance
-------------------------------------------
andre   12000072    12/4/1990   Excellent
Scott   112073      12/5/1999   Good
Reagan  2692882     15/7/2003   fair
Aldo    1402508     4/7/1998    Poor
Mary    2266257     5/11/1995   Very good
Walt    11182094    3/12/2000   Good
Tessy   82099       8/12/1999   Very good
Skinn   11102       7/2/2002    Excellent
Smart   678016      12/4/1990   fair
John    475689      12/5/1999   Poor
Rita    2796799    12/4/1990    Very good
Rachael 11188      12/5/1999    Poor
Gomez   3075168    3/12/2000    Very good
Becham  3075168    4/7/1998     Good
Walker  1050879    5/11/1995    Very good
Tommy   2017451    3/12/2000    Excellent
Trevor  11279      7/2/2002     Good
Litin   1182       12/5/1999    fair
Martha  883368     15/7/2003    fair
Rafael  3070833    4/7/1998     Poor
Kim     3070833    5/11/1995    Very good
Clichy  255918     12/4/1990    Good
Mario   2706915    12/5/1999    Excellent

I want to remove students with docid from student table. I tried the following:

for i in `cat docid.txt;` do
`psql -A -t $dbname $username << EOF`
`select * from student where nameid = $i;`
`EOF`
`done  >>docid.csv`

Thank you

Answer 1

Assuming you're using PostgreSQL, I suggest copying them into a temp table, then joining on it.

e.g.

$ psql <<'__END__'
BEGIN;
CREATE TEMPORARY TABLE docids (docid integer) ON COMMIT DROP;
\copy docids from 'docids.txt'
DELETE FROM student USING docids WHERE nameid = docid;
COMMIT;
__END__

In addition to being cleaner and simpler than a loop, it's going to be way faster, and it'll either succeed or fail as a unit without leaving half-completed work on an error.

If you want to output the deleted rows, append RETURNING * to the DELETE statement, and run with psql -q to suppress other output.