Basic Commands
- Postgresql Command Line Cheat Sheet Free
- Psql Commands
- Postgres Cheat Sheet
- Postgresql Command Line Cheat Sheet
- Postgresql Command Line Cheat Sheet Pdf
- Psql Cheat Sheet Pdf
Login to postgresql
PostgreSQL Cheat Sheet CREATE DATABASE. Backup all databases (command line) pgdumpall pgbackup.sql Run a SQL script (command line) psql -f script.sql databaseName.
PostGres Cheat Sheet POSTGRES NOTES 1. Always put “ “ around column names and table names. Put ‘ ‘ around values (like when inserting. Using the command line Login command line with psql –d databasename or psql –d database name –U:username 3. Remember at the command line you need a semi colon at the end of all statements. POSTGRESQL 8.3 PSQL CHEAT SHEET psql is located in the bin folder of the PostgreSQL install and PgAdmin III install. This is psql 8.3.5, the PostgreSQL interactive terminal. Usage: psql OPTIONS. DBNAME USERNAME General options:-c COMMAND-d, -dbname=NAME -f, -file=FILENAME -help -l, -list -v NAME=VALUE -version-X. PostgreSQL Command Line Cheat Sheet 02 Oct 2012 » » postgresql, command line. Share this on → Tweet « Testing Mail From the Command Line on Ubuntu Bashprofile vs Bashrc. PostgreSQL Cheat Sheet CREATE DATABASE CREATE DATABASE dbName; CREATE TABLE (with auto numbering integer id) CREATE TABLE tableName ( id serial PRIMARY KEY, name varchar(50) UNIQUE NOT NULL, dateCreated timestamp DEFAULT currenttimestamp ); Add a primary key ALTER TABLE tableName ADD PRIMARY KEY (id); Create an INDEX.
Default Admin Login
List databases on postgresql server
Turn off line pager pagination in psql:
Determine system tables
List databases from within a pg shell
List databases from UNIX command prompt
Describe a table
Quit psql
Switch postgres database within admin login shell
Reset a user password as admin
Show all tables
List all Schemas
List all users
Load data into postgresql
Dump (Backup) Data into file
Increment a sequence
Create new user
Change user password
Grant user createdb privilege
Create a superuser user
Upgrade an existing user to superuser
Show Database Version
Change Database Owner
Copy a database
View Database Connections
View show data directory (works on 9.1+)
Show run-time parameters
Show the block size setting
Show stored procedure source
Grant examples
Restore Postgres .dump file
Find all active sessions and kill them (i.e. for when needing to drop or rename db)
Source: http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it
Handy Queries
Query analysis
Querying Data
From a Single Table
Postgresql Command Line Cheat Sheet Free
From Multiple Tables
Using SQL Operators
Source:
Learn to move your first steps with the PostgreSQL console with this cheat sheet.
PostgreSQL is a fantastic database isn't it? However for all those starting out, PostgreSQL console could be confusing to say the least.
Let's see in this cheatsheet what are the basic commands for moving your first steps it the PostgreSQL console.
First steps with PostgreSQL
First of all you want to login into the console. To do so become the user postgres from the command line with:
Psql Commands
Once inside enter the PostgreSQL console with:
If you use Postgres.app for MacOS, from the terminal you can run psql followed by the default database name:
If you see this prompt you're in:
Interacting with databases
Once inside the PostgreSQL console you can interact with databases. From now on I'll omit the prompt postgres=#. To create a new database:
To list all the databases in the server:
In addition to the shortcut there is also the extended version:
To delete a database (warning zone):
To connect to a database run:
or:
and you should see:
Note that outside the PostgreSQL console you can also create a database with createdb:
Interacting with tables
Once connected to a database you can list all its tables with:
Postgres Cheat Sheet
This command will give you a representation of all the tables in the database:
My example assumes a simple database with two tables connected through a many to one relationship. To describe a table, that is, to see its columns, run:
This command will give you a complete description of the table:
You can see every column in the table with the corresponding type. If there is any primary or foreign key you'll see them as well.
Postgresql Command Line Cheat Sheet
PostgreSQL roles
Database users can interact, modify, update, and see databases. In PostgreSQL they're called roles. To create a new user from the PostgreSQL console run:
Postgresql Command Line Cheat Sheet Pdf
PostgreSQL roles may own one or more database. Once you create a role you can assign an entire database to it:
If you want to give a user the ability to create new databases, run:
When you're done with databases, roles, and tables you can finally exit the console with:
Psql Cheat Sheet Pdf
PostgreSQL: dumping a remote database
It's common when migrating between systems to migrate first the application code, fetch the database from the old system, and load it in the new environment.
With pg_dump you can dump a remote database to the local environment. To do so, set the PGPASSWORD environment:
Then, use pg_dump to dump the remote database on the local machine:
To load the dump on the new machine, create the database and then run:
PostgreSQL console: getting help
The PostgreSQL console has a lot more commands, and there are also a lot of SQL instructions in the PostgreSQL dialect. To see the complete list of all the SQL command available in PostgreSQL you can run:
To see a complete list of PostgreSQL command run: