PostgresSQL Commands

About

Icon

PostgreSQL is a powerful, open source object-relational database system.It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC.

---

@Commands :

#Login to postgresql:
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W

#Default Admin Login:
sudo -u postgres psql -U postgres

#List databases on postgresql server:
psql -l [-U myuser] [-W]

#Turn off line pager pagination in psql:
\pset pager

#Determine system tables:
select * from pg_tables where tableowner = 'postgres';

#List databases from within a pg shell:
\l;

#List databases from UNIX command prompt:
psql -U postgres -l;

#Describe a table:
\d tablename

#Quit psql:
\q;

#Switch postgres database within admin login shell:
\connect databasename;

#Reset a user password as admin: 
alter user usertochange with password 'new_passwd';

#Show all tables:
\dt;

#List all Schemas: 
\dn;

#List all users:
\du;

#Load data into posgresql:
psql -W -U username -H hostname < file.sql

#Dump (Backup) Data into file:
pg_dump -W -U username -h hostname database_name > file.sql

#Increment a sequence:
SELECT nextval('my_id_seq');

#Create new user:
CREATE USER tom WITH PASSWORD 'myPassword';

#Change user password: 
ALTER USER Postgres WITH PASSWORD 'mypass';

#Grant user createdb privilege: 
ALTER USER myuser WITH createdb;

#Create a superuser user:
create user mysuper with password '1234' SUPERUSER

# or even better
create user mysuper with password '1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;

#Upgrade an existing user to superuser: 
alter user mysuper with superuser;

# or even better
alter user mysuper with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION

#Show Database Version:
SELECT version();

#Change Database Owner:
alter database database_name owner to new_owner;

#Copy a database: 
CREATE DATABASE newdb WITH TEMPLATE originaldb;

#View Database Connections:
SELECT * FROM pg_stat_activity;

#View show data directory (works on 9.1+; not on 7.x):
show data_directory;

#Show run-time parameters:
show all;
select * from pg_settings;

#If you wish to list all tables, you must use:
\dt *.*;

#connect to the database, then list the tables:
\c liferay;
\dt;

#You can combine those two commands onto a single line, if you prefer:
\c liferay \dt;