Retrive data which column containes the data Using LIKE in PSQL

# 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 %';
# or 
SELECT * FROM employees WHERE department like 'C% thing %';

# 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

#ref below
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    | ssymonds2@hhs.gov             | 2010-05-17 | Clothing          | F      |  95313 |         4
           7 | Ardeen     | Curwood    | acurwood6@1und1.de            | 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 | bstanbrooke1b@netvibes.com    | 2016-09-12 | Clothing          | F      |  77259 |         1
          54 | Verney     | McQuirk    | vmcquirk1h@ning.com           | 2012-06-17 | Clothing          | M      |  97156 |         2
          55 | Norina     | Yea        | nyea1i@cnet.com               | 2005-06-14 | Children Clothing | F      |  45959 |         1
          56 | Wilek      | Cossor     | wcossor1j@merriam-webster.com | 2014-09-17 | Children Clothing | M      |  91859 |         4
          62 | Lek        | Camplin    |                               | 2007-07-16 | Children Clothing | M      | 124190 |         6

docker exec -it postgres psql -U postgres -d postgres -c 'SELECT * FROM "dataelement"'

$ psql -h <host> -p <port> -U <username> <database>

# 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/

#create database.
CREATE DATABASE <db-name>;

#to check databases
\d

# 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]: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.

test_db=>

# end here, in above observe the 'test_db' its remote and successfully we connected.

#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'
/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 
https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

# 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

# Export PostgreSQL `postgres` db without the access log
pg_dump --dbname=postgres --port=5432 --host=<dbserver>.postgres.database.azure.com -T "cron.*" > output.sql

var arrData = [String]() // This is your data array
var arrSelectedIndex = [IndexPath]() // This is selected cell Index array
var selectedSkills = [String]() // This is selected cell data array

extension ViewController: UICollectionViewDelegate, UICollectionViewDataSource
{
    func collectionView(_ collectionView: UICollectionView, numberOfItemsInSection section: Int) -> Int {
        return arrData.count
    }

    func collectionView(_ collectionView: UICollectionView, cellForItemAt indexPath: IndexPath) -> UICollectionViewCell {
        let cell : CollectionViewCell = collectionView.dequeueReusableCell(withReuseIdentifier: "CollectionViewCell", for: indexPath) as! CollectionViewCell
          if selectedSkills.contains(arrData[indexPath.row]) {
            cell.skillsView.backgroundColor = cell.skillsView.hexStringToUIColor(hex: "FEDA22")
        }
      return cell
    }
  
    func collectionView(_ collectionView: UICollectionView, didSelectItemAt indexPath: IndexPath) {
        print("You selected cell #\(indexPath.item)!")
        
            if let cell = collectionView.cellForItem(at: indexPath) as? SkillsCollectionViewCell {

                let selectedCategory = subCategoriesPopUpArray[indexPath.item]

                if selectedSubCategories.contains(selectedCategory) {
                    cell.skillsView.backgroundColor = cell.skillsView.hexStringToUIColor(hex: "F0F0F0")
                    selectedSubCategories = selectedSubCategories.filter {
                        $0 != selectedCategory
                    }
                } else {
                    selectedSubCategories.append(selectedCategory)
                    cell.skillsView.backgroundColor = cell.skillsView.hexStringToUIColor(hex: "FEDA22")
                }
        }
    }
}

     let currentSelectedCell = playersTableView.cellForRow(at: indexPath) as! PlayerDetailsTableViewCell
        
        if selectedRow == -1 {
            currentSelectedCell.playerDetailsView.layer.borderColor = hexStringToUIColor(hex: "F34141").cgColor
            currentSelectedCell.playerDetailsView.layer.borderWidth = 1
            selectedRow = indexPath.row
        } else if indexPath.row == selectedRow {
            currentSelectedCell.playerDetailsView.layer.borderWidth = 0
            selectedRow = -1
        } else {
            currentSelectedCell.playerDetailsView.layer.borderColor = hexStringToUIColor(hex: "F34141").cgColor
            currentSelectedCell.playerDetailsView.layer.borderWidth = 1
            
            let newIndexPath = IndexPath(item: selectedRow, section: 0)
            if let cell = playersTableView.cellForRow(at: newIndexPath) as? PlayerDetailsTableViewCell {
                cell.playerDetailsView.layer.borderWidth = 0
            }
            selectedRow = indexPath.row
        }

extension ServiceProviderProfileViewController : UICollectionViewDelegate, UICollectionViewDataSource {
    
    func collectionView(_ collectionView: UICollectionView, numberOfItemsInSection section: Int) -> Int {
        return categories.count
    }

    func collectionView(_ collectionView: UICollectionView, cellForItemAt indexPath: IndexPath) -> UICollectionViewCell {
        
        let cell = categoryCollectionView.dequeueReusableCell(withReuseIdentifier: "CategoryCollectionViewCell", for: indexPath) as! CategoryCollectionViewCell

            cell.categoryLabel.text = categories[indexPath.row]
        
            if indexPath.row == selectedCellIndex {
                cell.categoryView.layer.borderColor = UIColor.blue.cgColor
                cell.categoryLabel.textColor = UIColor.blue
            } else {
                cell.categoryView.layer.borderColor = UIColor.lightGray.cgColor
                cell.categoryLabel.textColor = UIColor.gray
            }
            return cell
    }
    
    func collectionView(_ collectionView: UICollectionView, didSelectItemAt indexPath: IndexPath) {
        selectedCellIndex = indexPath.row
        self.categoryCollectionView.reloadData()
    }
    
}

/** select-text.js
 * Set selected text to the element passed to the function.
 */
function selectText(el) {
	let sel, range;
	// Browser compatibility
	// older ie
	if (window.getSelection && document.createRange) {
		sel = window.getSelection();
		// creates range object
		// set range to elements range
		// remove window range
		// set window range
		window.setTimeout(function() {
			range = document.createRange();
			range.selectNodeContents(el);
			sel.removeAllRanges();
			sel.addRange(range);
		}, 1);
	} else if (document.selection) {
		sel = document.selection.createRange();
		// creates range object
		// set raselectnge
		// call  event listener
		if (sel.text == '') {
			range = document.body.createTextRange();
			range.moveToElementText(el);
			range.select();
		}
	} else {
		// do nothing
	}
	return;
}