5

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).

  • 1
    There is a \copy command in psql that can import a CSV file. I don't know if there is something to import directly from the variable. Oh, and there is ob-sql that can deal with a SQL database. – NickD Feb 24 at 13:11
4

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…

  • Great solution! Way better than both of mine – Martín Feb 27 at 2:04
  • Still learned a lot from your answer :) Nice of org-mode, that variables are assigned according to context (in sh i.e. as plain text, in sql like the automated version of your solution.m1) – kai-dj Feb 27 at 8:31
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
  • do you know, why orgtbl-to-csv does not output the headings? if used interctively w/o params it does… thinking of expanding the hack to also create a table with column names [and as we're shellscripting anyway perhaps interpret type from a part of headings, too] – kai-dj Feb 26 at 20:27

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.