Skip to content

Instantly share code, notes, and snippets.

@SalahAdDin
Last active May 2, 2019
Embed
What would you like to do?
PostgresSQL commands

Enter to superuser: sudo su
Enter to postgres user: sudo -iu postgres
Enter to user: sudo -iu $username$
Enter to console with authentication: psql -d $databasename$ -U $username$ -W
Enter to postgres console(default database): psql
Enter to postgres console(other database): psql -d $databasename$
Disconnect: \q

List all databases: \l
List all schemas: \dn
List all users: \du

Create a new user: CREATE USER $user$ PASSWORD '$pass$';
Grant privileges in schema: GRANT ALL ON SCHEMA $schemaname$ TO $username$;
Grant privilefes in tables: GRANT ALL ON ALL TABLES IN SCHEMA $schemaname$ TO $username$;
Drop a user: DROP USER $username$

Create a new databe:

CREATE DATABASE $name$
    WITH 
    OWNER = $username$
    TEMPLATE = $database template(postgres by default)$
    ENCODING = '$coding(UTF8 by default(more international))$'
    CONNECTION LIMIT = -1;

Drop a database: DROP DATABASE $databasename$

Create a new schema: CREATE SCHEMA $name$;
Drop a schema: DROP SCHEMA $name$

Create a new table:

CREATE TABLE $schemaname$.$name$
(
    $fields$
)

Drop a table: DROP TABLE $schemaname$.$tablename$;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment