Do you have to load data from funky files into postgres frequently? I do. I get a few requests per week of the “hey, can you give me a mass data extract for things that match the criteria in this spreadsheet” variety. Frequently, these are windows-formatted all-in-one-line “csv” files.
A year or so ago, I would have reformatted the file, reviewed the data, figured out appropriate data types for the various fields (and sometimes there are dozens of fields), written some SQL to create the table, tried to load the data, cursed a bit, lather, rinse, repeat.
I heard about Catherine’s ddl generator https://github.com/catherinedevlin/ddl-generator at last year’s PgOpen, and these days, all I do is this:
ddlgenerator --inserts postgres [datafile.csv] > datafile.sql
…then log into my database, and run:
As I said on twitter, it’s not just the time but the annoyance this tools saves me that I really value.
Try it out!
* at least, not about this.