So it took me about 10 minutes to figure out that Excel has a date_format, date, date.toString() like function. It’s called text(). To get a date format into something which will import nicely into a postgres or mysql database, simply do somthing like this:
text(A1, "yyyy-mm-dd")

And since all the other languages seem to have a more clear “date format” function, here are the corresponding methods:

• .Net/C# – String.Format() or DateTime.ToString()
• php – date()
• mysql – date_format()
• postgres – to_char()
• java – String.Format combined with a java.util.Calendar

It was surprisingly hard to find a functional, working example of copying data from a file not based on the server into a PostgreSQL database.

cat file.txt | psql -c “COPY table_name FROM STDIN” -U username database_name

And don’t forget the handy trick for disabling the password prompt in psotgres.

A few helpful search terms:

• Copy file into Postgres from local filesystem
• Copy local file into Postgres from command line
• Using psql to copy file into Postgres database from file system

Due to security reasons, postgres (in contrast to mysql) does not allow a user to specify the password on the command line. However, there is a quick and hacky workaround which allow a psql command to be run without the password prompt.

From the command line, simply type:
export PGPASSWORD=yourpassword

From there you can now run:
psql -U username database

I was surprised that I didn’t easily find a query which gives me all of the table sizes for all of my Postgres database table. I did find this nice bit:
SELECT pg_size_pretty(pg_total_relation_size(table_name))

But I wanted to take it a step further and get the list of the largest tables in my database. This query worked like a charm:
SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name))
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema = 'public'
ORDER BY pg_total_relation_size(table_name) DESC


Finally, I wanted to know if any of these tables had a column name containing the text “org id”.
SELECT t.table_name, pg_size_pretty(pg_total_relation_size(t.table_name))
, case when cn.table_name is not null then ‘Yes’ ELSE ” end as “has_col”
FROM information_schema.tables t
LEFT JOIN
(select distinct c.table_name
from information_schema.columns c
where c.column_name LIKE ‘%org%id%’) cn on cn.table_name = t.table_name
WHERE t.table_type=’BASE TABLE’ AND table_schema = ‘public’
ORDER BY pg_total_relation_size(t.table_name) DESC
;