Importing a CSV Into PostgreSQL Like a Pro

Importing data into PostgreSQL can be time consuming and painful – unless you toss the GUI tools and use scripts.

Importing data into PostgreSQL can be time consuming and painful – unless you toss the GUI tools and use scripts. I get into this in SQL in Orbit (which I launched last month) but I thought I’d share the technique with everyone.

USING HEAD

It all starts with using the head command in the shell in order to pull out the column names:

head -1 master_plan.csv

This will pop out the very first line of the CSV, which is typically the header row. I’m working with Cassini’s mission plan data, so this is what I see:

Head Command

Now I just copy/paste that into VS Code and run a simple replacement using ā€œChange all Occurrencesā€ to build my create table statement.

The final step is to use the copy from command to pull data out of the CSV and into the database. There’s a whole lot more to this (like data types to use and creating an isolated schema) – just watch the video already!


There's More...

What's Your Exit Plan?
Big-O, In Anger
Want to learn more about Postgres?
I wrote a fun, free ebook where you get to assemble NFL stats into a fantasy football cheat sheet. have a look!