0

I have a file containing a value which should go into a field of a PostgreSQL table.

By searching a little, I found many answers, e.g. How can I update column values with the content of a file without interpreting it? or https://stackoverflow.com/a/14123513/6630397, with this kind of snippet, but it has to be run in a psql terminal:

\set content `cat /home/username/file.txt`

UPDATE table SET field = :'content' WHERE id=1;

It works, but is it possible to programmatically execute it in one shot, directly from a bash prompt, without manually entering the psql command line, e.g. something like:

$ psql -d postgres://postgres@localhost/mydatabase -c \
  "UPDATE table SET field = :'the_file_content' WHERE id=1;"

?

There is also the -v argument that seems promising but I'm not successful when using it:

$ psql -d postgres://postgres@localhost/mydatabase \
  -v content=`cat ${HOME}/file.txt` \
  -c "UPDATE table SET field = :'content' WHERE id=1;"

I've got thousands of psql: warning: extra command-line argument where psql actually seems to "execute" each comma separated strings of the file as pg commands, where it shouldn't of course; the file content, which consists of a single line, must be treated as a whole.

Doc PostgreSQL 14:
https://www.postgresql.org/docs/current/app-psql.html

swiss_knight
  • 5,787
  • 8
  • 50
  • 92

1 Answers1

0

How about reading the file content into a variable first and then use it?

content=$(<integer_infile); psql -p 5434 -c "update table set field = $content where id = 1;"
content=$(<text_infile); psql -p 5434 -c "update table set field = '$content' where id = 1;"

This at least works for me if the file contains an integer or text including spaces on a single line.

rienix
  • 711
  • 1
  • 6
  • 12