A quick reference to PostgreSQL.
Created on: 2019-01-22
PostgreSQL CHEAT SHEET DROP VIEW viewname; Delete a view DROP INDEX idxname; Drop an index CREATE VIEW v(c1,c2) AS SELECT c1, c2 FROM t; WITH CASCADED LOCAL CHECK OPTION; Create a new view with check option CREATE RECURSIVE VIEW v AS select-statement -anchor part UNION ALL select-statement; -recursive part. Show my ip and location.
- Importing Data from CSV in PostgreSQL Insert multiple rows List the tables in SQLite opened with ATTACH Meta commands in PSQL Outputting Query Results to Files with o Random Sequences Show Tables in Postgres SQL Cheat Sheet.
- PostgreSQL Cheat Sheet The PostgreSQL cheat sheet provides you with the common PostgreSQL commands and statements that enable you to work with PostgreSQL quickly and effectively. Download PostgreSQL cheat sheet We provide you with a 3-page PostgreSQL cheat sheet in PDF format.
- Quit from psql q -version select version; -Create PostgreSQL database create database MYDB owner myowner; -drop PostgreSQL database.BE CAREFUL USING THIS COMMAND - IT CANNOT BE REVERSED!! TAKE A BACKUP FIRST drop database MYDB; -list PostgreSQL databases list l -list schemas in a database with psql dn -list schemas in database with postgresql select nspname from pgcatalog.pg.
- One-page guide to Knex: usage, examples, and more. Knex is an SQL query builder for Node.js.This guide targets v0.13.0.
To check postgresql version:
To create a super suer with passowrd:
To create a database on Postgresql:
To remove an extension:
To connect to remote psql host 1:
To backup a psql database without typing password 2:
To create database from sql file:
to backup a single table:
to export a sql statement output to a csv file with header:
to export a sql statement output to a geojson file:
COPY current_relation_members TO '/var/lib/postgresql/csv/current_relation_members.CSV' DELIMITER ',' CSV HEADER;
The following are the Meta-Commands for psql command. This can be used with either with -c flag of the psql command like:
Or within the interactive prompt that comes after sudo -u postgres psql command. There are many Meta-Commands and there are available in the psql document. Bellow are a few useful most useful for me:
connect to a database: c $DATEBASE_NAME
list all users: du
list all database: l or list
list all table: dt
To allow remote connections to PostgreSQL database server, first check listen_addresses in postgresql.conf:
The output would show something like this:
Now let's edit the postgresql.conf file in our editor of choice:
Search for listen_addresses, and set it to '*' for all addresses or comma separated IP address, save the file and exit. Now we need to change the pg_hba.conf file so let's open it with our editor:
More details here PostgreSQL: Documentation: Connections and Authentication.
Now add the following to the end of file:
Now save the file and exit. Now restart PostgreSQL:
Now connect to the remote server:
Starting the server
You must do this every time you restart your machine.
After you have run this command the first time, postgres will start automatically whenever you boot up your computer.
Starting the postgres shell
Open the shell and connect to the city_explorer database:
With no arguments,
psql will start the shell connected to your user-default database.
Creating a database
This will create a new database called “book_wiki”, which can contain many individual tables:
CREATE DATABASE book_wiki;
Quit the postgres shell
Type the backslash character, immediatly followed by a “q”:
Basic shell navigation
List all of your databases
Connect to a database
yourname=# c book_wiki
List all tables (aka: relations) in the database
dt means “describe table”.
Display the schema for a table
yourname=# d table-name
Error - psql not connected to server
psql: could not connect to server: No such file or directory
Solution - Turn Postgres On
See above notes about starting the server.
Error - schema.sql file not found
Postgresql Cheat Sheet Pdf
When connecting your schema.sql to your database:
schema.sql: No such file or directory
Postgresql Sql Cheat Sheet
Psql Cheat Sheet Pdf
You must specify the full path to the .sql file or run the command from the same directory where the .sql file lives.