POSTGRES: Create a new database

# Login as superuser

sudo -u postgres psql

-- Create database 'chemicals' for user 'chemuser' with password 'chempassword'

CREATE DATABASE chemicals TEMPLATE template0 ENCODING 'UNICODE';

CREATE USER chemuser WITH PASSWORD 'chempassword';

ALTER DATABASE chemicals OWNER TO chemuser; 

GRANT ALL PRIVILEGES ON DATABASE chemicals TO chemuser;

-- Ctrl+D to log out

# Login to database 'chemicals' as 'chemuser' 

psql -h 127.0.0.1 -d chemical -U chemuser -W

-- switch to database

\c chem_db

-- Create a table 'substances'

CREATE TABLE substances (
    id SERIAL PRIMARY KEY,
    ec_number VARCHAR(15) ,
    cas_number VARCHAR(15) ,
    ec_name VARCHAR,
    mol_formula VARCHAR (100)
);

-- Verify the table creation:

\dt

-- Check the schema:

\d substances