0

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?

-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:

  • Thanks a lot for the answer Erwin. One more thing, suppose after every few rows of data in my file, there are comments(starting with #), is it then possible to store those comments in another table and the rows of data in another? – suvrat Oct 19 at 4:35
  • @suvrat: I added more above to address comments. – Erwin Brandstetter Oct 21 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.