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