PostgreSQL Command

Connecting :
# su – postgres

Connect to database :
$ psql
\c databasename
Describe :
\d
help :
\?
Exit :
\q

Creating Roles
Now the PostgreSQL allows access to database based on roles, they are similar to ‘users’ in Linux system. Also we can create a set of roles, which is similar to ‘groups’ in Linux & based on these roles, a user’s access is determined. A role created will be applied globally & we don’t have to create it again for another database on the same server.

To create a role, first connect to database & then we will use command ‘createuser’:
postgres=# CREATE USER test;

Or we can also use the following,
postgres=# CREATE ROLE test

To create a user with password,
$ CREATE USER test PASSWORD ‘enter password here’

Check all roles
postgres=# \du

Delete a role
postgres=# DROP ROLE test;

Create a new Database
postgres=# CREATE DATABASE sysa;

Delete a database
postgres=# DROP DATABASE sysa;

List all database
postgres=# \l
or
postgres=# \list

Connect to a database
$ sudo -i -u test

than connect to database the following command,
$ psql -d sysa

Change to another database
Once connected to a database, we can also switch to another database without having to repeat the whole process of loggin into user & than connecting to different different database. We use the following command,
sysa=> \connect new_database

Create Table
To create a table, first connect to the desired database where the table is to be created. Next create table with the command,
sysa=> CREATE TABLE USERS (Serial_No int, First_Name varchar, Last_Name varchar);

Now insert some records into it,
sysa=> INSERT INTO USERS VALUES (1, ‘Dan’, ‘Prince’);

Check the tables database
sysa=> SELECT * FROM USERS ;
& it will produce all the inserted data from the table USERS.

Delete a table
sysa=> DROP TABLE USERS:

List all the tables in a database
sysa=> \dt

Adding a column to a table
sysa=> ALTER TABLE USERS ADD date_of_birth date;

Updating a Row
sysa=> UPDATE USERS SET date_of_birth = ‘05-09-1999’ WHERE Seriel_No = ‘1’;
sysa=> SELECT * FROM USERS;

Remove a Column
sysa=> ALTER TABLE USERS DROP date_of_birth;

Remove a Row
sysa=> DELETE FROM USERS WHERE Seriel_No = ‘1’;


sanitized from linuxtechlabs

Auteur : Harlok

2020-01-24 11:40:15