postgres.md

Binary data to SQL

pg_restore db.bin > db.sql

Log in to postgres

sudo -u postgres -i

Import from .sql file into Postgresql

psql -U postgres -W -d "valuevest" -f vv.sql -h localhost

Export a column to CSV:

• In psql: \copy tablename to 'filename' csv;
• https://stackoverflow.com/questions/33826566/postgresql-what-is-the-best-way-to-export-specific-column-from-specific-table-fr

Copy from CSV to postgres table

• Move the file to /tmp. Run: copy <table_name> FROM '/tmp/<filename>.csv' WITH (FORMAT csv);

Single table dump:

$ pg_dump --column-inserts --data-only --table=<table> <database>
$ pg_dump -h localhost -p 5432 -U postgres -d mydb -t my_table > backup.sql

Show columns in a table:

postgres=# SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema' AND table_name   = 'your_table'
postgres=# \d+ <table_name>

Resync primary_key field:

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1)

Expanded display:

\x on

Delete older data from database:

TableName.objects.filter(date__gte=datetime.now()-timedelta(days=14)).delete()

Reference:

• https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546
• http://postgresguide.com/utilities/psql.html