Category Archives: PostgreSQL

Incorporating SQL in Stata code

Here is a snippet of code showing how one can include SQL in Stata code (I assume that the ODBC connection has been set up). #delimit ; local sql ” SELECT * FROM director_bio.directorship_results WHERE non_match AND start_date < date_filed … Continue reading

Posted in PostgreSQL, Stata, Uncategorized | Tagged , , | Leave a comment

Executing SQL in a file against an ODBC source from Stata

I recently discovered that Stata has good support for OBDC. While the built-in functionality includes odbc sqlfile, a “batch job alternative to odbc exec,” there seems to be no equivalent alternative for odbc load (even though odbc load is surely … Continue reading

Posted in Mac, PostgreSQL, Stata | Leave a comment

Prototyping PL/Python functions

I’ve seen a variable called numerical intensity crop up in a couple of papers recently (one example is Lundholm, Rogo, and Zhang (2014)). I spent a little time coming up with a Python program to extract numbers from text. Once … Continue reading

Posted in PostgreSQL, Python | Tagged , , , , | Leave a comment

Upgrading PostgreSQL from 9.4 beta1 to 9.4 beta2

MacPorts installs PostgreSQL 9.4 beta2 in the same directory as PostgreSQL 9.4 beta 1 was in. And going from beta 1 to beta 2 requires an upgrade for the cluster to work. So upgrading is more like going from 9.3 … Continue reading

Posted in Mac, PostgreSQL | Tagged , , | Leave a comment

Using JSON to pass dictionaries from Python to PostgreSQL and back

To my (very untrained) eyes, JSON looks like Python dictionaries. I’d wondered if one could use JSON to pass around Python dictionaries. Turns out that it works quite well. Task Get the top N words from passages of text (conference … Continue reading

Posted in PostgreSQL, Python | Tagged , , , , | 1 Comment

Pulling data into Stata from PostgreSQL in Mac OS X

The following is perhaps of interest to Stata users. This was pretty easy to install on my (OS X) laptop (a few tweaks to the Makefile, then the usual “make; sudo make install”). I suspect one could get it to … Continue reading

Posted in Mac, PostgreSQL, Stata | 3 Comments

Moving all tables to a different tablespace in PostgreSQL

I first created a directory on my computer as If I wanted to move my database into this new tablespace: But if I just want to move the stuff I use and manage: and saved it as move_to_ssd.sql (“ssd” is … Continue reading

Posted in PostgreSQL | Tagged | 3 Comments