PostgreSQL useful commands

PostgresqlConnect to DB shell

sudo -i -u postgres
psql

Launch interactive session

psql -h server_name -U user_name -d db_name

Create user:

from PSQL shell

CREATE USER user_name WITH password ‘tmppassword’;

by createuser command:

/usr/local/pgsql/bin/createuser user_name
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

Define access privileges

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] table_name [, ...] TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE db_name [, ...] TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

How to find config file? Usually, it here /var/lib/postgresql/data/postgresql . Do it in the PSQL shell

SHOW config_file;

Other useful commands:

CREATE DATABASE db_name;
CREATE TABLE table_name(
id serial NOT NULL,
name character varying NOT NULL,
surname character varying NOT NULL,
tel_c character varying,
ida integer NOT NULL,
CONSTRAINT "pk-db_name" PRIMARY KEY (id));
ALTER TABLE table_name ADD FOREIGN KEY (ida) REFERENCES address(id);
INSERT INTO table_name (name, surname, ida) values ('Name','Surname',1);
select * from db_name;
select name, surname from table_name order by surname desc;
select table_name.name, table_name.surname, table_name_2.street as street from table_name, table_name_2;
select table_name.name, table_name.surname, table_name_2.street as street from table_name, table_name_2 where table_name.ida = street.id and street.id = table_name_2.id;
DROP DATABASE db_name;

List all databases (add “+” for more detail)

\l

List all tables in database with descriptions and size

\dt+

List tables/indexes/sequences/views/system tables

\d{t|i|s|v|S}

List of users

\du

Edit the query buffer (or file) with external editor

\e

Show the contents of the query buffer

\p

Reset (clear) the query buffer

\r

Run an SQL batch script against a database

psql -h server_name -U user_name -d db_name -f /path/to/somefile.sql

Run an SQL batch script against a database and send output to file

psql -h localhost -U user_name -d db_name -f /path/to/scriptfile.sql -o /path/to/outputfile.txt

Run a single statement against a DB

psql -U postgres -d db_name -c "CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);"

Output data in HTML format

psql -h server_name -p 5432 -U user_name -d db_name -H -c "SELECT * FROM sometable" -o mydata.html

Leave a comment