From 3ac35a4a654b2593a5aaeea873ab48b90f0409f5 Mon Sep 17 00:00:00 2001 From: cbreton Date: Mon, 18 Nov 2019 08:50:20 +0100 Subject: Add psql cheat --- dotfiles/cheat/psql | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) create mode 100644 dotfiles/cheat/psql (limited to 'dotfiles') 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; -- cgit v1.2.1