Postgresql for beginners

There are default values here that you can change as you work down the page. Insert your own values below. The data is used for page generation locally and is not sent back to our servers. User Name Computer / host name Database name Operating System UbuntuDeriv CachyOS ArchOther otherOS

1. Install and main user.

   sudo apt install postgresql postgresql-contrib
   Login as the postgres operating system user. Note we do this using sudo, leaving the postgres Linux user with no password. As the postgres user by default you are automatically connected to the postgres database. john@computer:/ sudo su postgres postgres@computer:/ psql postgres=# CREATE USER john WITH SUPERUSER; Quit psql postgres=# \q Switch back to your normal operating system user login. exit Login to psql again under your usual username. This time you must specify the postgres database as there is no database with the same name as your operating system username. john@computer:/ psql postgres Create a new database. The owner is you. The main client of the database maybe a webserver. Give this its own user with maybe more limited capabilities. postgres=# CREATE DATABASE myfirstdb OWNER john; Switch to new database. postgres=# \connect myfirstdb; If you logout of psql at any point, then when you log back in you now use john@computer:/ psql myfirstdb
2. Before continuing, here are some commands to correct and undo things if necessary. At some point you may get:
   database "$uName" has a collation version mismatch
   then enter
   myfirstdb=# ALTER DATABASE myfirstdb REFRESH COLLATION VERSION;
   myfirstdb=# \du+ List users and other roles
   myfirstdb=# \l List databases
   myfirstdb=# \connect otherdbname Switch databases
   myfirstdb=# DROP DATABASE dbname; Remove database
   myfirstdb=# ALTER DATABASE oldname RENAME TO newname; Change database name. Note you can't be logged into the database whose name you are changing.
   myfirstdb=# DROP TABLE tablename ; Delete table
   myfirstdb=# TRUNCATE tablename ; Delete all rows
   myfirstdb=# ALTER TABLE users DROP CONSTRAINT yourconstraint ; Drop constraint
   myfirstdb=# ALTER TABLE users DROP COLUMN columnname ; To delete row

3. Create users table

   To create table with a good secure key.
   myfirstdb=# CREATE TABLE users ( id uuid DEFAULT uuidv7(), PRIMARY KEY (id)); To display table. myfirstdb=# SELECT * FROM users; To add username myfirstdb=# ALTER TABLE users ADD username VARCHAR(15) UNIQUE NOT NULL; myfirstdb=# ALTER TABLE users ADD CONSTRAINT unamecheck CHECK(username ~ '[A-Za-z]{4,15}[0-9]{0,4}$'); myfirstdb=# CREATE UNIQUE INDEX usernamelower ON users(lower(username)); To add password myfirstdb=# ALTER TABLE users ADD password VARCHAR(128) NOT NULL; myfirstdb=# ALTER TABLE users ADD CONSTRAINT passwordcheck CHECK(password ~ '\S{4,128}$'); To add status myfirstdb=# CREATE type status AS ENUM ('User', 'Admin'); myfirstdb=# ALTER TABLE users ADD rank status DEFAULT 'User' NOT NULL; To add user myfirstdb=# INSERT INTO users VALUES(DEFAULT, 'username' , 'password' , 'Admin'); myfirstdb=# INSERT INTO users VALUES(DEFAULT, 'username' , 'password' ); To update status myfirstdb=# UPDATE users SET rank = 'Admin' WHERE username = 'username'; Create our first application database user. Note there is no need to use same name as the operingsystem owner of the application, as this user doesn't need to access the psql terminal. myfirstdb=# CREATE USER app1 WITH PASSWORD 'password1'; myfirstdb=# GRANT USAGE ON SCHEMA public TO app1;