Postgres Operation

 1. Switch to postgres account

sudo -i -u postgres

2. access Postgres prompt

psql

Then we can interact with database management

3. Exit out of the PostgreSQL

\q

4. Create new database

createdb sammy

or

sudo -u postgres createdb sammy

5. Create table

CREATE TABLE playground (

    equip_id serial PRIMARY KEY,

    type varchar (50) NOT NULL,

    color varchar (25) NOT NULL,

    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),

    install_date date

);

6. See the table

\d

7. SELECT Operation

SELECT * FROM playground;

8. DELETE Operation

DELETE FROM playground WHERE type = 'slide';

9. Add Columns from a table

ALTER TABLE playground ADD last_maint date;

10. Drop Columns from a table

ALTER TABLE playground DROP last_maint;

11. Insert in a table

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');

INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

12. Update Data in a table

UPDATE playground SET color = 'red' WHERE type = 'swing';

13. Change password

ALTER ROLE postgres WITH PASSWORD '123456';

14. Show all databases

\l

15. Connect to one database

\c <database name>

16. Show all tables in one database

\dt

17. Show all contents in one table

SELECT *

FROM <table name>

;

18. steps to modify database scheme in Django with postgres

1) delete migrations folder

2) log in with postgres account: sudo -u postgres -i

3) delete previous database: dropdb 'postgres'

4) create new database: createdb postgres

5) logout

6) sudo python3 manage.py makemigrations my_ups

7) sudo python3 manage.py migrate