aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--dotfiles/cheat/psql33
1 files changed, 33 insertions, 0 deletions
diff --git a/dotfiles/cheat/psql b/dotfiles/cheat/psql
new file mode 100644
index 0000000..2efdced
--- /dev/null
+++ b/dotfiles/cheat/psql
@@ -0,0 +1,33 @@
+# psql is the PostgreSQL terminal interface. The following commands were tested on version 9.5.
+# Connection options:
+# -U username (if not specified current OS user is used).
+# -p port.
+# -h server hostname/address.
+
+# Connect to a specific database:
+psql -U postgres -h serverAddress -d dbName
+
+# Get databases on a server:
+psql -U postgres -h serverAddress --list
+
+# Execute sql query and save output to file:
+psql -U postgres -d dbName -c 'select * from tableName;' -o fileName
+
+# Execute query and get tabular html output:
+psql -U postgres -d dbName -H -c 'select * from tableName;'
+
+# Execute query and save resulting rows to csv file
+# (if column names in the first row are not needed, remove the word 'header'):
+psql -U postgres -d dbName -c 'copy (select * from tableName) to stdout with csv header;' -o fileName.csv
+
+# Read commands from file:
+psql -f fileName
+
+# Restore databases from file:
+psql -f fileName.backup postgres
+
+# clear tables in db
+DROP SCHEMA public CASCADE;
+CREATE SCHEMA public;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO public;