Import data extracted from website directly into PostgreSQL database without using any text or csv file

Question

I have a shell script which extracts data from the website. I know, I can store this data into text or csv file and then import it into database using PostgreSQL "copy" command.

My interest is not to use this text or csv file in the process. But, directly copy the data into database.

Is there anyway to do this? Any suggestion are appreciated. Thank you

Answer 1

You can open a pipe and write your data to the pipe directly. Related answer with demo code:

• Insert SQL statements via command line without reopening connection to remote database

Or you can use COPY with a program or standard input directly:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

...

When PROGRAM is specified, the server executes the given command and reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.