# here we use WHERE column_name but we don't know what is the data
# To fetch result of given string to match in column_data we use LIKE
#syntax SELECT * FROM <table-name> WHERE <column_name> LIKE '%<data-to-fetch>%' with out
#space both sides
SELECT * FROM employees WHERE department LIKE '%Clo%';
# NOTE : %Clo% is departement feches related data which start with that string
course_data=# SELECT * FROM employees WHERE department LIKE '%Clo%' LIMIT 10;
employee_id | first_name | last_name | email | hire_date | department | gender | salary | region_id
3 | Sydney | Symonds | email@example.com | 2010-05-17 | Clothing | F | 95313 | 4
7 | Ardeen | Curwood | firstname.lastname@example.org | 2006-02-19 | Clothing | F | 28995 | 7
10 | Redford | Roberti | | 2008-07-21 | Clothing | M | 72225 | 7
21 | Bernardo | Davage | | 2013-07-11 | Clothing | M | 124949 | 6
38 | Edna | Erwin | | 2003-04-09 | Children Clothing | F | 91397 | 5
48 | Birgitta | Stanbrooke | email@example.com | 2016-09-12 | Clothing | F | 77259 | 1
54 | Verney | McQuirk | firstname.lastname@example.org | 2012-06-17 | Clothing | M | 97156 | 2
55 | Norina | Yea | email@example.com | 2005-06-14 | Children Clothing | F | 45959 | 1
56 | Wilek | Cossor | firstname.lastname@example.org | 2014-09-17 | Children Clothing | M | 91859 | 4
62 | Lek | Camplin | | 2007-07-16 | Children Clothing | M | 124190 | 6
# so the scenario is we have table called employees
# in employees table there is column called department
# there are different departments are included in table
# now we want some department which we dont know how its spelled there so we are just
# trying to fetch the data in column which matched or containes
# ex: word 'Clothing' is in stored in table but we dont know exactly how data spelled there
# so we try to match something like '% clo %'
# or 'C% thing %'
SELECT * FROM emploees WHERE department like '% thing %';
SELECT * FROM employees WHERE department like 'C% thing %';
#opening postgresql config file to enabling from local to everyone
#firt open this file on machine to make changes
sudo vi /etc/postgresql/12/main/postgresql.conf
#to quickly search on editor easily type as '/' & the type word to search 'listen_address'
#by default listen_addresses = 'localhost'
#so we are changing 'localhost' to '*' it means by placing the '*' any one can access.
listen_addresses = '*'
#now change one more config file called 'pg_hba.config' to give access, so open the file as below
sudo vi /etc/postgresql/12/main/pg_hba.conf
# add the line end of the file
host all all 0.0.0.0/0 md5
#for above mentioned line reference in detailed
# now restart the postgres so it will restart the service and apply the changes internally
# first check the status
sudo service postgresql status
# now restart the postgresql service
sudo service postgresql status
CREATE DATABASE <db-name>;
#to check databases
# create a user with password;
# syntax- CREATE USER <user-name> WITH ENCRYPTED PASSWORD 'user-password';
CREATE USER john WITH ENCRYPTED PASSWORD 'john123';
# TO GRANT ALL PRIVILIGES TO ABOVE CREATED USER
# syn - GRANT ALL PRIVILEGES ON DATABASE <db-name> TO <user-name>
GRANT ALL PRIVILEGES ON DATABASE test_db TO john;
#now if check database by running this '\l' command to list out the databases in access privileges column granted user also inclued there to
# connecting to a database with above created user on ubuntu postgres psql terminal
#syntax -> psql -h <host-or-ip-address-or-endpoint> -p 5432 -d <data-base-name> -U <user-name>
psql -h localhost -p 5432 -d test -U dev1
# connecting to a database from local machine with pgadmin & psql on window as well
# below is the reference of connected to remote db from windows psql, observe below..
#when we open psql terminal on window it prompt like below we have to give the all.
# starts here
Server [localhost]: 2X.1XX.2XX.1XX # this is the endpoint-or-ip-address-of-server
Database [postgres]: test_db # this is the database we created above
Port :5432 # port
Username [postgres]: john # above created user
Password for user john: # what ever passwd we created.
psql (14.2, server 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
# end here, in above observe the 'test_db' its remote and successfully we connected.
# here when tries to copy the table to csv file, was getting Erro Like
# /home/ubuntu/file.csv : permission denied
# actually we are trying to copy or do/perform some action like excuting/read/write a new
# file or exsting file, but understand one thing is from any user to any other user
# location directory if we want to perfom any thing we do not have permissions for that.
# we have to have a right access for that users directory.
# so here we are trying to copy/export the table to csv file from postgres to user # Ubuntu's /home/ubuntu Directroy for that we have to have permission of Rwx Permission on # ubuntu for that here we use - ''' sudo chown user:user <path/to/access> ''' to get access of
# ubuntu user.
\COPY person TO /home/ubuntu/data.csv DELIMITER ',' CSV HEADER;
# Gets This error -- /home/ubuntu/data.csv: Permission denied
# now user chown as shown below to get permission of directory-path to write/execute
sudo chown postgres:postgres /home/ubuntu/