PostgreSQL USE on Linux Terminal#

PostgreSQL cheat-sheet 😸

Connection#

postgres āĻ Login#

sudo -u postgres psql

alter postgres user password#

ALTER USER postgres PASSWORD 'root';

āĻ¯āĻĻāĻŋ specific user as (postgres) āĻāĻŦāĻ‚ specific DataBase as (db_demo) āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻ•āĻ°ā§‡ connect āĻ•āĻ°āĻ¤ā§‡ āĻšāĻžāĻ‡ āĻ¤āĻžāĻšāĻ˛ā§‡#

psql -d db_demo -U postgres -W

āĻ¯āĻĻāĻŋ āĻāĻŽāĻ¨ āĻ•ā§‹āĻ¨ DataBase āĻ connect āĻ•āĻ°āĻ¤ā§‡ āĻšā§Ÿ āĻ¯āĻž āĻ†āĻ˛āĻžāĻĻāĻž port āĻĨā§‡āĻ•ā§‡ Host āĻ•āĻ°āĻž ,āĻ¤āĻž āĻšāĻ˛ā§‡ -h āĻŸāĻžāĻ— āĻŸāĻŋ add āĻ•āĻ°āĻ¤ā§‡ āĻšāĻŦā§‡#

psql -h host -d database -U user -W

āĻ•ā§‹āĻ¨ āĻ•āĻžāĻ°āĻŖā§‡ āĻ¯āĻĻāĻŋ SSL key āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻ•āĻ°ā§‡ DataBase āĻ connect āĻ•āĻ°āĻ¤ā§‡ āĻšā§Ÿ ,āĻ¤āĻž āĻšāĻ˛ā§‡ “dbname āĻāĻŦāĻ‚ sslmode” āĻŸāĻžāĻ— āĻŸāĻŋ add āĻ•āĻ°āĻ¤ā§‡ āĻšāĻŦā§‡#

psql -U user -h host "dbname=db sslmode=require"

Create user#

create a user without any rools#

sudo -u postgres createuser <username>

giving user a password#

alter user <username> with encrypted password '<password>';

create user from postgres CLI with password#

    CREATE ROLE <username> LOGIN PASSWORD '<password>' NOINHERIT CREATEDB;

Granting privileges on a database#

grant all privileges on database <dbname> to <username> ;

view all users#

\du

give user specific roles#

  • give super User roles

ALTER USER demo WITH SUPERUSER;

  • give create database roles

ALTER USER demo WITH CreateDB;

you may, for some reason want to do it purely via psql, then these are the three magic commands#

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

Drop or Delete user#

1st replace or remove all dependency attribute from it by delete or reassign properties.

reassign owned by#

REASSIGN OWNED BY target_user to another_user;

Drop User by#

drop user target_user

Create DataBase#

āĻ¨āĻ¤ā§āĻ¨ DataBase as newbd āĻ¤ā§ˆāĻ°āĻŋ āĻ•āĻ°āĻ¤ā§‡ : (generally give smaller letter)#

create database newdb;

List available Data-Base (press q to quite)#

\l

create database with specific user#

āĻāĻ–āĻžāĻ¨ā§‡ specific user “demo” & new data-base “demobd”

CREATE DATABASE demodb OWNER demo;

connected to existing DataBase named newdb by#

\c newdb;

or

\c dbname username;

Drop or Delete a database that has no active connection#

DROP DATABASE database_name;

Create Schema#

create a schema named newschema inside newdb database by#

create schema newschema;

List available schema#

\dn

Create Table#

create a table t1 with columns (id,password) inside schema newschema by#

create table newschema.t1 (id integer, password CHAR(10));

List available tables#

\dt

Describe a table#

To describe a table such as a column, type, modifiers of columns, etc., you use the following command#

\d table_name

now see all created table by#

select * from pg_catalog.pg_tables;

List users and their roles (To list all users and their assign roles, you use \du command#

)

\du

Command history (To display command history, you use the \s command.)#

\s

If you want to save the command history to a file, you need to specify the file name followed the \s command as follows:

\s filename

Execute psql commands from a file (In case you want to execute psql commands from a file, you use \i command as follows:)#

\i filename

Edit command in your own editor#

It is very handy if you can type the command in your favorite editor. To do this in psql, you \e command. After issuing the command, psql will open the text editor defined by your EDITOR environment variable and place the most recent command that you entered in psql into the editor.

\e

After you type the command in the editor, save it, and close the editor, psql will execute the command and return the result.

Quit psql#

\q

necessary Library might need to be install for wotk with postgreSQL
On this page