# PostgreSQL ### Online free PostgreSQL * [ElephantSQL](https://www.elephantsql.com/) ### Installing PSQL in Ubuntu ``` # this only install the psql client and not the database sudo apt-get update sudo apt-get install postgresql-client ``` ### Connecting to Remote PostgreSQL instance in psql client ``` psql -h -p -d -U -W -W option will prompt for password psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W ``` ### Connecting via Docker * [How To Install and Run PostgreSQL using Docker ?](https://dev.to/shree_j/how-to-install-and-run-psql-using-docker-41j2) ### Install and Configure PSQL using Docker: Run the below command in linux or windows or mac machine from the terminal or command-prompt to pull PSQL from docker-hub.\ \ `docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=somePassword -d postgres`\ \ In the above command replace :
* Optional - *postgresql-container* with a preferable container name if necessary. * *somePassword* with a password to authenticate and connect to the postgres (in application with connection string as well as the PG-admin viewer). ### Install PG-admin using Docker: Download the pgAdmin-4 browser version from docker-hub using the following command.\ \ `sudo docker run --rm -p 5050:5050 thajeztah/pgadmin4`
Now manage your postgres from the browser by launching [http://localhost:5050](http://localhost:5050/) . Verify a new container created and running at 0.0.0.0:5432 with the below command.\ `docker ps -a` ``` $ sudo docker pull postgres $ sudo docker pull postgres:13.2-alpine Running the Docker image for first time $ sudo docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=somePassword -d postgres:13.2-alpine - Where some-postgresh is the name of the container - Now if one has psql installed locally and want to connect to postgresql - Alternatively, Connect to the docker container first $ sudo docker exec -it 35a62e54e14b bash - Connecting a "postgre" username $ psql -h localhost -p 5432 -U postgres -W ENTER the password asked during the setup at line6 ``` ### Clean PostgreSQL terminal ``` Keyborad shortcut : cntrl +L Exit : \q ``` ### Login a Postgresql database ``` psql -U ``` ### List all the PostgreSQL user * [Reference](https://www.postgresqltutorial.com/postgresql-list-users/) ``` postgres=# \du -Relation \ display tables # \dt - Display more data for table # \d+ - Display schema of table # \d+ ``` ### List of All Database ``` $ \l $ \list ``` ### Creating a user and Database ``` # create user harshit WITH PASSWORD 'secret'; # create Database people with owner 'harshit'; Now entering datatabase with as the user you created from the shell $ psql people harshit $ ``` ### Creating a Table ``` CREATE TABLE pg4e_debug ( id SERIAL, query VARCHAR(4096), result VARCHAR(4096), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id) ); SELECT query, result, created_at FROM pg4e_debug; CREATE TABLE pg4e_result ( id SERIAL, link_id INTEGER UNIQUE, score FLOAT, title VARCHAR(4096), note VARCHAR(4096), debug_log VARCHAR(8192), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP ); CREATE TABLE ages ( name VARCHAR(128), age INTEGER ); ``` ### Inserting Data into the table ``` DELETE FROM ages; INSERT INTO ages (name, age) VALUES ('Corinn', 22); INSERT INTO ages (name, age) VALUES ('Cully', 22); INSERT INTO ages (name, age) VALUES ('Ghyll', 14); INSERT INTO ages (name, age) VALUES ('Karyss', 25); INSERT INTO ages (name, age) VALUES ('Tracey', 15); ``` ### Read the data from Table ``` SELECT make.name, model.name FROM model JOIN make ON model.make_id = make.id ORDER BY make.name LIMIT 5; ``` ### Update the value from Table ``` ``` ### Deleting the data from Table ``` DELETE FROM users where email='abc@gmailcom' WARNING DELETE FROM users // will delete all rown ``` ### Ordering in Table ### LIMIT / OFFSET Clause ``` ``` ### Datatypes * Char(n) - allocate the entire space (faster for small string where the lenght is known) (fixing size helps a lot) (fixe to ASCII char set) * Varchar(n) - allocate variables amount of space depending on the data length(less space) * TEXT - pararaph or HTML pages ,generally not used with indexing or sorting and only then limited to prefix * BYTEA(n) up to 255 bytes * SMALLINT (-32768,+32768) * INTEGER (2 Billion) * BIGINT (10\*\*18 ish) * REAL (32 bit) 10\*\*38 with 7 digit of approximation * DOUBLE PRECISION (64 bit) 10\*\*308 with 14 digits of accuracy * NUMERIC (accuracy , decimal)- specified digits if accuracy and digits after decimal point * TIMESTAMP - ' YYYY-MM-DD HH:MM:SS" * DATE -" YYYY- MM- DD " * TIME -" HH: MM :SS " Built in PostgreSQL functions **NOW()** ### Database keys and Indexes in PostgreSQL ``` id SERIAL UNIQUE (logical key) ``` Auto increment key for as data comes in Database decides itself what to use for indexing * Trees (B tree) * Hashes (Modulus % like) are used for Indexes **Analysing Query performance** ``` explain analyze select id from employee where id=2000; ``` ### Loading Data from SQL ``` wget https://www.pg4e.com/tools/sql/library.csv curl -O https://www.pg4e.com/tools/sql/library.csv CREATE TABLE track_raw (title TEXT, artist TEXT, album TEXT, count INTEGER, rating INTEGER, len INTEGER); \copy track_raw(title,artist,album,count,rating,len) FROM 'library.csv' WITH DELIMITER ',' CSV; ``` ### Relational Database #### Keys * Primary Key - integer auto increment field * Logical key - What outside world use for lookup * Foreign key - value point to another table #### Best Practise * Never use logical key as primary key * logical keys can do change , albeit slowly * Relationships that are based on matching string fields are less efficient then integers ### Database Normalization #### 3 NF * Do not replicate Data , instead reference data , point at data * use integer for keys and for references * Add a special "key" column to each table , which you will make references to . ### Joins in SQL * #### One to Many Relations : ``` CREATE TABLE make ( id SERIAL, name VARCHAR(128) UNIQUE, PRIMARY KEY(id) ); CREATE TABLE model ( id SERIAL, name VARCHAR(128), make_id INTEGER REFERENCES make(id) ON DELETE CASCADE, PRIMARY KEY(id) ); make model Chevrolet Cruze Limited Chevrolet Cruze Limited Eco Chevrolet Cruze Premier Volvo V70 FWD Volvo V70 R AWD SELECT make.name, model.name FROM model JOIN make ON model.make_id = make.id ORDER BY make.name LIMIT 5; ``` ### Many to Many relationship ![](/files/-MePjh6u9XiR_VgKKI8e) ![](/files/-MeQEPXlTAu3-7wbZkov) ``` CREATE TABLE student ( id SERIAL, name VARCHAR(128) UNIQUE, PRIMARY KEY(id) ); DROP TABLE course CASCADE; CREATE TABLE course ( id SERIAL, title VARCHAR(128) UNIQUE, PRIMARY KEY(id) ); DROP TABLE roster CASCADE; CREATE TABLE roster ( id SERIAL, student_id INTEGER REFERENCES student(id) ON DELETE CASCADE, course_id INTEGER REFERENCES course(id) ON DELETE CASCADE, role INTEGER, UNIQUE(student_id, course_id), PRIMARY KEY (id) ); Rhylee, si106, Instructor Grant, si106, Learner Kassie, si106, Learner Rahma, si106, Learner Raymond, si106, Learner Xiong, si110, Instructor Fatiha, si110, Learner Neeve, si110, Learner Shelley, si110, Learner Yaris, si110, Learner Tristain, si206, Instructor Lukas, si206, Learner Reeva, si206, Learner Shreeram, si206, Learner Toby, si206, Learner SELECT student.name, course.title, roster.role FROM student JOIN roster ON student.id = roster.student_id JOIN course ON roster.course_id = course.id ORDER BY course.title, roster.role DESC, student.name; ``` ### ### Reference : * [Zerodha Engineering Blog on optimising PostgreSQL](https://zerodha.tech/blog/working-with-postgresql/) * [HOW TO INTERPRET POSTGRESQL EXPLAIN ANALYZE OUTPUT](https://www.cybertec-postgresql.com/en/how-to-interpret-postgresql-explain-analyze-output/) * [Cut Out the Middle Tier: Generating JSON Directly from Postgres](https://blog.crunchydata.com/blog/generating-json-directly-from-postgres) * [Working Around a Case Where the Postgres Planner Is "Not Very Smart"](https://heap.io/blog/when-the-postgres-planner-is-not-very-smart) **Video Reference :** * [Database Indexing Explained (with PostgreSQL)](https://www.youtube.com/watch?v=-qNSXK7s7_w) * [Indexing in PostgreSQL vs MySQL](https://www.youtube.com/watch?v=T9n_-_oLrbM) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://harshityadav95.gitbook.io/system-design/postgresql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.