Can we copy data from 1 text file to 2 postgresql tables?

Question

I have a text file with some data and I have two similar tables in my postgresql database. Is it possible to copy a part of the data present in the file, say first 10 lines, to the first table and the remaining in the second table?

Answer 1

The SQL COPY command itself has no provision for that. But you can specify PROGRAM as input. The manual:

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.

Then, assuming you are on a Linux system, you can COPY the first 10 lines with:

COPY tbl1 FROM PROGRAM 'head -n10 /path/to/file/my.csv';

To COPY the rest, starting with line 11:

COPY tbl2 FROM PROGRAM 'sed -n 11,\$p /path/to/file/my.csv';

Or, to COPY without comments with leading # / only comments (asked in comment):

COPY tbl3 FROM PROGRAM 'awk ''!/^#/'' /path/to/file/my.csv';
COPY tbl4 FROM PROGRAM 'awk ''/#/ { sub(/^#/, ""); print }'' /path/to/file/my.csv';

The last command removes the leading # from comments (but not additional whitespace).

Consult the man page or your preferred online source about the shell commands head, sed and awk ...

As always, you need the necessary privileges. Consider the psql meta command \copy if you are not superuser nor member of the pg_execute_server_program role.

Related:

• Using Postgres COPY TO for data export without overwriting file