How to copy org-table to postgresql database?

Question

With sqlite one can easily populate a table in a database via a org-table like so:

[EDIT](using only one sqlite-scr-block with org-table variable parameter)[/EDIT]

#+NAME: A
| val1 | val2 |
|------+------|
|    2 |   16 |
|    4 |   25 |
|   10 |    2 |

#+begin_src sqlite :db */path/to/db* :var orgtable=A :colnames yes
drop table if exists A;
create table A(val1 int, val2 int);
.mode csv A
.import $orgtable A
select * from a;
#+end_src

#+RESULTS:
| val1 | val2 |
|------+------|
|    2 |   16 |
|    4 |   25 |
|   10 |    2 |

I want to do this with postgresql. I tried with some COPY commands, but did not find other concrete info, on how to do this with org-variables.

How can I populate a table in postgresql with the contents of an org-mode-table?

EDIT (after @Martín's answer and @NickD's comment): I "merged" the answer and the comment.

1) Export table to tmp-file

#+begin_src emacs-lisp :var orgtable=A
(write-region (orgtbl-to-csv orgtable nil) nil "/tmp/test.csv")
#+end_src

2) Import to db via localcopy \copy in sql-src-block

#+name: my-query
#+header: :engine postgresql
#+header: :dbhost localhost
#+header: :dbuser USER
#+header: :dbpassword PW
#+header: :database org-db
#+begin_src sql
drop table if exists A;
create table A(val1 int, val2 int);
\copy A(val1,val2) FROM '/tmp/test.csv' DELIMITER ',';
#+end_src

This comes quite close… But before I start to improve this, I'd love hints and answers how to do this with variable as input (w/o the detour with 2 src-blocks and a transfer tmp file).

Answer 1

Solution: in sql-scr-block context the variable $orgtable translates to path-string of an auto-generated temporary csv file – wich can then be imported with \copy.

#+name: my-query-push-table
#+header: :var orgtable=A
#+begin_src sql :cmdline -q :database org :dbpassword PW :dbuser USER :dbhost localhost :engine postgresql
DROP TABLE if exists A;
CREATE TABLE A(val1 int, val2 int);
\copy A(val1,val2) FROM '$orgtable' DELIMITER ',';
SELECT * FROM A;
#+end_src
#+name: my-query-push-table-result
#+RESULTS: my-query-push-table
| val1 | val2 |
|------+------|
|    2 |   16 |
|    4 |   25 |
|   10 |   22 |

…almost the same solution as in answer from @Martín via a tmp-file, but… org-magic ^^ and using local copy \copy as @NickD suggested. So to have an answer, I posted my solution… feel free to update yours, I'll switch marked solution…

Answer 2

It is possible, but it's a little hacky (okay, very hacky)

Method 1:

First, you have to save the org-table to a CSV file

#+begin_src emacs-lisp :var orgtable=A
(write-region (orgtbl-to-csv orgtable nil) nil "test.csv")
#+end_src

Then, you can use the COPY command to import the local CSV file from STDIN with the psql command

#+begin_src sh
 psql -h dbhost -d mydb -U myuser -c "COPY mytable_a FROM STDIN with delimiter as ','" < test.csv
#+end_src

The only caveat is that the table cannot have the headings, because if it does the import will fail trying to insert them.

Method 2:

We can use a python block to format the table rows in order to be inserted with a multirow INSERT INTO mytable_a VALUES ((2,16),...)

#+name: values
#+begin_src python :var orgtable=A :results output
def format_row(row):
    return "(" + ",".join(map(str, row)) + ")"

def format_rows(rows):
    return ",".join(map(format_row, rows))

print (format_rows(orgtable))
#+end_src

#+RESULTS: values
: (2,16),(4,25),(10,2)

Then, we reference the result value of the python block in a SQL block to insert the rows

#+begin_src sql :engine postgresql :var val=values
INSERT INTO mytable_a VALUES $val
#+end_src