Re: Copying data from a CSV file into a table dynamically

On 08/14/2018 06:10 AM, pavan95 wrote:
> Hi all,
>
> I am well versed with the COPY command for copying the contents of a csv
> file into a table. I am used to the below mentioned command:
>
> * COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-2018-08-14_000000.csv'
> WITH csv;*
>
> But in the location "/tmp/abc/xyz/" daily a file(with header
> postgresql-2018-08-14_000000.csv) will be generated.
>
> So how can I automate this task instead of manually specifying the file
> name, it has to look for the filename with current_date at the time of
> copying into table. How can I achieve this?

COPY FROM can do so from a program:

https://www.postgresql.org/docs/10/static/sql-copy.html

"PROGRAM

A command to execute. In COPY FROM, the input is read from standard
output of the command, and in COPY TO, the output is written to the
standard input of the command.

Note that the command is invoked by the shell, so if you need to
pass any arguments to shell command that come from an untrusted source,
you must be careful to strip or escape any special characters that might
have a special meaning for the shell. For security reasons, it is best
to use a fixed command string, or at least avoid passing any user input
in it.
"

So maybe create a program that does the file look up and then sends the
data to stdout for consumption by COPY.

OR

Just create a program/script that does it all, find the file and invoke
COPY on the file. I do similar things using Python and psycopg2.

>
> Any suggestions or modifications are most welcome.
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com