Introduction to PostgreSQL

-

 Introduction to PostgreSQL

CREATOR: Amruta Ashish Pednekar

What is PostgresQL ?



Database


For

 

Storing Data

Database system which stores data into tables for easy access

Install PostgresQL 

  • Go to https://www.postgresql.org/download/

  • Select your opening system family

  • Download installer

  • Click on downloaded installer and run as administrator

  • Enter admin password. It will start installation.

  • Click on NEXT button after every step

  • It will ask you for a password for database superuser. Enter and note is somewhere for future use

  • Check below option

  • Finish installation

Starting PGAdmin 

  • Go to path where you installed PostgreSQL

  • Go to <version> -> PGAdmin -> bin

  • Open pgAdmin4

  • Set a master password for pgAdmin

  • Connect to server

    • Go to the left panel. Click on to servers

    • Select server . Its PostgreSQL<version>

    • Enter the same password you set while PostgreSQL installation

    • Once you are connected to server, you can see other dropdowns like Tablespaces

  • If you face any error while connecting postgres server on windows, go to service manager and start postgresql 

    • run -> services.msc -> find postgresql-x64-10 -> start the service


Create table

Using pgAdmin4 UI

  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables 
  • Right click and Create -> Table
  • Create and save
  • Create columns
    • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables ->  <Table name>
    • Right click and Create -> Column
    • Add column name and data type
    • Save

Using Query Tool

  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables
  • Right click -> Query Tool
  • Write create table query in Query Editor
    • create table table_name( column_name1 data_type, column_name2 data_type);
    • Execute 
    • Refresh Tables from left panel 

Drop table

  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables
  • Right click -> Query Tool
  • Write create table query in Query Editor
    • drop table if exists table_name;
  • It drops a table from the database if it exists and skips if table doesn't exist 

Alter table

  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables
  • Right click -> Query Tool
  • Write create table query in Query Editor 
    • create table table_name
    • alter table table_name add column “column_name1” data_type,”column_name2” data_type;
    • alter table table_name drop column “column_name”;

Run commands from sql file

  • Create a new file with extension .sql
  • Write sql commands
  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables
  • Right click -> Query Tool  (Query Editor) -> Open file 
  • Execute (It executes all commands listed inside this file)

Copying CSV records into table

  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables
  • Right click -> Query Tool  (Query Editor)
  • COPY table_name(column_name1, column_name2, column_name3, column_name4)
      FROM 'C:\data.csv'
      DELIMITER ','
      CSV HEADER;
  • FROM shows csv file path
  • If there is space in between column names , 
    • Add double quotes. E.g. “Item name”
    • Single quote will not work here. It gives syntax errors.
  • Execute 
  • Refresh Tables from left panel

Using pgAdmin4 UI

  • Go to pgAdmin left panel -> Servers -> PostgreSQL -> Databases -> Schemas -> Tables 
  • Right click to table_name
  • Go to Import/ Export
  • Follow instructions and import from csv file.

Comments

  1. Prashantped31 March 2022 at 21:28

    Congratulations for the Blog on codeing . Wish you all the bey

    ReplyDelete

Post a Comment

Popular posts from this blog

PERSONAL ACCESS TOKEN ON GITHUB

-
Image
  PERSONAL ACCESS TOKEN ON GITHUB  PURPOSE While doing git clone or git pull if you face and issue and getting below error  remote: Support for password authentication was removed on August 13, 2021. Please use a personal access token instead. You have to create a personal access token with your git handle. Authentic in terminal with this PAT ( personal access token) REASON While doing any git operation, now GitHub is  not accepting authentication with password  You need to add PAT. Follow below steps to create PAT and authenticate on your local machine STEPS  CREATE PERSONAL ACCESS TOKEN Login to GitHub from the browser. Go to Settings - > Developer setting -> Personal access token -> Create new token Authenticate Full out required fields Select expiration period We want to access the repository from the command line. For this don't forget to  select "repo" option Click "Generate token" Copy generated token. Your tokens are like your password...
Read more

PostgreSQL Commands

-
  Select Statement   select * from users;  - Selects all column select username from users;  - Selects only username column    select * from users limit 10;  - Selects only first 10 records Here column name is case insensitive  e.g username = USERNAME Columns name with space in between must be same as what is stored in database  e.g.  “User Name” is not equal to “user name”. You should enter “User Name” in query Where Filter   select * from users where country=’europe’;  - Selects all column from users table whose country column values is europe select * from users where country=’europe’ limit 10; - Selects first 10 records from users table whose country column values is europe select * from users where “Annual Salary” > 10000; Use logical operator on number columns to perform numeric operations select * from users where Extract(Year from to_date(birthdate, “MM/DD/YYYY”))=2015 limit 10; Selects first 10 users whose birt...
Read more

Rails Application with Docker

-
Image
Creating Rails App We are creating a rails application without installing rails on our system Create a folder on machine where we want to create Rails app Linux  mkdir ~/docker_rails cd  ~/docker_rails Windows  mkdir docker_rails cd docker_rails Create docker container based on image ruby:2.6   docker run -i -t --rm -v ${PWD}:/usr/src/app ruby:2.6 bash   Where docker run creates a container -i -t OR  -it :    --rm : It's a throwaway container  -v Mounts volume to share local system with container  ${PWD} :  Unix environment variable pointed to current directory Windows user should give current directory path as below docker run -i -t --rm -v C:/Users/amrut/OneDrive/Desktop/Amruta/docker_rails :/usr/src/app ruby:2.6 bash   ${PWD}:/usr/src/app : Mount the current directory inside the container at /usr/src/app Ruby:2.6 Reference docker image bash   After running this command , we can see  root@05d3878962ce:/# This shows...
Read more