aboutsummaryrefslogtreecommitdiff
path: root/dotfiles/cheat/psql
diff options
context:
space:
mode:
authorcbreton <corentin.breton@fullsave.com>2019-11-18 08:50:20 +0100
committercbreton <corentin.breton@fullsave.com>2019-11-18 08:50:20 +0100
commit3ac35a4a654b2593a5aaeea873ab48b90f0409f5 (patch)
tree45c7c27b689b81b93124d65bd855922b5e8a76d0 /dotfiles/cheat/psql
parent2ec0089527cf5e0a6fab7592afa8e4b4fcdad47f (diff)
downloaddotfiles_dotdrop-3ac35a4a654b2593a5aaeea873ab48b90f0409f5.zip
dotfiles_dotdrop-3ac35a4a654b2593a5aaeea873ab48b90f0409f5.tar.xz
Add psql cheat
Diffstat (limited to 'dotfiles/cheat/psql')
-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;