Home > Software engineering >  What are good ways to bring SQL query results into a LaTeX table?
What are good ways to bring SQL query results into a LaTeX table?

Time:03-01

Background

I'm doing some data analysis on a large dataset on a local PostgreSQL server that I use the DataGrip IDE to query. A substantial proportion of that analysis is tabular stuff: frequency tables and the like which summarize various aspects of key variables.

I do all my writeups in a LaTeX environment, which means I'm often bringing stuff over from Postgres (and from R, where I do the other part of my analysis) into Tex documents.

The Problem

In many cases, I'll try to structure my Postgres queries so that they result in exactly the table I'm looking for.

My question for the community is this: do you guys have favorite, neat, or preferred ways to turn the result of a SQL query into a LaTeX table?

What I've tried

Take a simple Postgres database that looks like this:

db

And say that I wanted a simple table that added up the number of errors for each id:

select id as "ID", sum(errors) as "Sum of errors"
from d
group by id

Yielding something like this in the DataGrip output viewer:

dg_result

All well and good, but how to get it into LaTeX?

The main way I've tried is functional, but clunky:

  1. I use Datagrip's "export data" function next to the output window to copy the table to the clipboard, like so:

clipboard

  1. I then paste it into this website, result

    So like I said, this is all functional, but since I'm a competent but ultimately quite novice SQL and LaTeX user, I wanted to ask the community whether they had anything better or quicker. Thanks.

    (Oh, and let me know if the Tex StackExchange is a better place for this. I'd be happy to move it. I actually may cross-post anyway.)

    CodePudding user response:

    I'd have a look at psql's latex format:

    test=> \pset format latex
    Output format is latex.
    test=> SELECT * FROM pg_am;
    \begin{tabular}{r | l | l | l}
    \textit{oid} & \textit{amname} & \textit{amhandler} & \textit{amtype} \\
    \hline
    2 & heap & heap\_tableam\_handler & t \\
    403 & btree & bthandler & i \\
    405 & hash & hashhandler & i \\
    783 & gist & gisthandler & i \\
    2742 & gin & ginhandler & i \\
    4000 & spgist & spghandler & i \\
    3580 & brin & brinhandler & i \\
    \end{tabular}
    
    \noindent (7 rows) \\
    

    CodePudding user response:

    I've created feature request to add LaTeX extractor to DataGrip, please follow and vote.

  • Related