TIL: SQL Updates and Deletes and Postgres User Management

Thursday, May 3, 2018 346 words 2 minutes

More postgresql today. Today I learned about updating records, deleting records, and then how to create and manage users within postgres. Like yesterday, all of this is coming from the notes I took while working through it.

Update Record

To update a record, you need to make sure that you use a where clause to specify an exact record, otherwise EVERY record is going to be updated.
Update syntax: update <table> set <col1> = <val1> where <condition>;

1

update employees set salary = 26000 where id=2;

Delete Record

Just like update, need to ensure that we use the where clause to specify exact record, or everything will be deleted!
Delete syntax: delete from <table> where <condition>;

1

delete from sports where id=6;

User Management

Details of users

1
2
3
4
5
6
7


\du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Create User

1
2
3
4
5
6


create user bob with password 'password';

 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 bob       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Grant Privileges

privileges: SELECT, INSERT, UPDATE, DELETE, RULE, ALL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


grant all privileges on database company to bob;

\l

                                  List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-------------+----------+----------+------------+------------+-----------------------
 blockbuster | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 company     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
             |          |          |            |            | postgres=CTc/postgres+
             |          |          |            |            | bob=CTc/postgres

Revoke Privileges

grant privileges on different databases.

1

revoke all privileges on database company from bob;

Alter

changes a users roles.

1
2
3
4
5
6
7
8
9


alter user bob with superuser;

\du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 bob       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Remove User

1

drop user bob;

💚