Postgresql for beginners

There are default values here that you can change as you work down the page. Although once you've used a value, stick with it or you will create an inconsistent system. Insert your own values below. The data is used for page generation locally and is not sent back to our servers.

  1. Install and main user.

    sudo apt install postgresql postgresql-contrib
    Change the postgres system user password. Note this is different to the Postgresql application internal user password. sudo passwd postgres If you want to use the postgres user from applications set the postgres user's internal password postgres=# ALTER USER postgres WITH PASSWORD 'password' ; Depending on your use case you may wish to manipulate Postgresql with a different user. su postgres psql postgres=# CREATE USER tommy WITH SUPERUSER; You may want to create a database with this user's name postgres=# CREATE DATABASE tommy OWNER tommy; To quit psql postgres=# \q Switch back to your main user. su tommy
    Enter psql again as user tommy
    psql tommy=# ALTER USER tommy WITH PASSWORD 'password' ;
  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
    tommy=# ALTER DATABASE tommy REFRESH COLLATION VERSION;
    To delete table
    tommy=# DROP TABLE tableName ; To delete all rows tommy=# TRUNCATE tableName ; To drop constraint tommy=# ALTER TABLE users DROP CONSTRAINT yourConstraint ; To delete row tommy=# ALTER TABLE users DROP COLUMN columnName ;

  3. Create users table

    To create table with a good secure key.
    tommy=# CREATE TABLE users ( id uuid DEFAULT uuidv7(), PRIMARY KEY (id)); To display table. tommy=# SELECT * FROM users; To add username tommy=# ALTER TABLE users ADD username VARCHAR(15) UNIQUE NOT NULL; tommy=# ALTER TABLE users ADD CONSTRAINT uNameCheck CHECK(username ~ '[A-Za-z]{4,15}[0-9]{0,4}$'); tommy=# CREATE UNIQUE INDEX usernameLower ON users(lower(username)); To add password tommy=# ALTER TABLE users ADD password VARCHAR(128) NOT NULL; tommy=# ALTER TABLE users ADD CONSTRAINT passwordCheck CHECK(password ~ '\S{4,128}$'); To add status tommy=# CREATE type Status AS ENUM ('User', 'Admin'); tommy=# ALTER TABLE users ADD status Status DEFAULT 'User' NOT NULL; To add user tommy=# INSERT INTO users VALUES(DEFAULT, 'username' , 'password' , 'Admin'); tommy=# INSERT INTO users VALUES(DEFAULT, 'username' , 'password' );