4

I would like to use some features of postgresql, specifically crosstab/pivot on my dataset. Databases, such as pgsql, handle large crosstabs much better than memory only tools such as Python's pandas. However, I don't want to install and manage the pgsql server.

Is there a way to use the query engine of pgsql as a python (or other language) library?

Here is an example of monetdb distribution (called monetdb-lite) which can be used as a python library: https://www.monetdb.org/blog/monetdblite-for-python

Shahbaz
  • 10,395
  • 21
  • 54
  • 83
  • 1
    Why don't you use docker or a remote service? It would be a lot simpler than trying to embed a library for something as comprehensive as postgresql. – Burhan Khalid May 14 '18 at 20:39

2 Answers2

7

PostgreSQL cannot be used as an embedded database in the way you would like.

There has been a lengthy thread on the pgsql-hackers mailing list about starting PostgreSQL in standalone mode for use as an embedded database, but the problems were just too bad, and too many necessary things cannot be made to work properly (e.g. autovacuum or checkpoints).

Use a different RDBMS.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • IMHO this does not qualify as an answer. Part of why is because you don't provide a reason why posgres does not work for this usecase and don't provide an alternative. Could you edit your answer accordingly? – blkpingu May 14 '18 at 20:54
  • 1
    Fair enough request. I backed my claim with a thread grom the developer list. – Laurenz Albe May 14 '18 at 21:08
0

As mentioned in the comments PostgreSQL is too comprehensive to implement as an embedded database. It has been tried, but it was just not feasible.

You could however use SQLite as an embedded database. It too can pivot.

blkpingu
  • 1,556
  • 1
  • 18
  • 41