Setup Postgres on Slackware

TODO: Add Slackware install

with user postgres run psql

CREATE DATABASE dbname;

GRANT ALL ON DATABASE dbname TO postgres;

To create another user, with limited privleges, do;

createuser --interactive --pwprompt

GRANT CONNECT ON DATABASE dbname TO username;

Connect to the database;

\connect dbname;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

For indexe ref:https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm

To backup a database;

pg_dump dbname > dumpfile

To restore a database;

psql dbname < dumpfile

NOTE: database must be created (see above) before restoring.

For remote connections;

edit /var/lib/pgsql/10.2/data/postgresql.conf

listen_address = '*'

edit /var/lib/pgsql/10.2/data/pg_hba.conf

host all all 0.0.0.0/0 md5

Other common commands/queries;

INSERT INTO table (col1, col2, col3) VALUES ('value1','value2','value3');

UPDATE table
SET columnValue = null
WHERE (length(columnValue)<1);

DELETE FROM table
WHERE columnValue LIKE ' %'; 

ALTER TABLE table ADD COLUMN columnName dataType CONSTRAINT;
ALTER TABLE table ADD COLUMN columnName VARCHAR NOT NULL; 

ALTER TABLE table ADD PRIMARY KEY ("column");

ALTER TABLE table ADD COLUMN "ID_PK" SERIAL PRIMARY KEY;

ALTER TABLE table ADD FOREIGN KEY ("column") REFERENCES table2;
This will reference the PRIMARY KEY in table2

CREATE INDEX index_name ON table (column ASC);