MalcolmChalmers.com

Database Tips and Tricks

HOME

Commands

# Built in commands

\? - help
\i - run script

Links

# Link
www.mockaroo.com - generates database data ?

PostgreSQL Tips

# install postgres
dnf module enable postgresql:15
dnf install postgresql-server

# init database (in /data instead of default location)
initdb -D /data

# enable it to auto start
systemctl enable postgresql
systemctl start postgresql
firewall-cmd –permanent –add-service=postgresql
firewall-cmd –reload

# set SELinux permissions on folder
semanage fcontext -a -t postgresql_db_t “/data(/.*)?”
restorecon -Rv /data

# backup a database
pg_dump dbname > dumpfile

# restore a database
psql -X dbname < dumpfile

# IN
SELECT * FROM person WHERE country_of_birth IN ('China', 'Brazil','Frabnce');

# BETWEEN
SELECT * FROM person WHERE date_of_birth BETWEEN '2000-01-01' AND '2015-01-01';

# LIKE and ILIKE
SELECT * FROM person WHERE email LIKE '%google.com';
SELECT * FROM person WHERE email LIKE '%google.%';
% any number of characters
_ any single character

SELECT * FROM person WHERE country_of_birth ILIKE 'p%';
return p* and P*

# GROUP BY
SELECT country_of_birth, COUNT(*) FROM person GROUP BY county_of_birth;

# HAVING
SELECT country_of_birth, COUNT(*) FROM person GROUP BY county_of_birth HAVING COUNT(*) > 5;

# MAX, MIN, AVG and SUM
SELECT MAX(price) FROM car;
SELECT MIN(price) FROM car;
SELECT AVG(price) FROM car;
SELECT ROUND(AVG(price)) FROM car;
SELECT make, model, MIN(price) FROM car GROUP BY make, model;

SELECT make, SUM(price) FROM car GROUP BY  make;


HOME