• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • How to Compare both table values in postgres ?

      How to Compare both table values in postgres ?

      • Posted by Nijamutheen J
      • Categories Blog
      • Date February 12, 2019
      • Comments 0 comment

      Finding the Missing Records between two tables :
      I. creating table structure for excel sheet table data’s , table name as temp.
      II. Restore the Excel data on temp table then count the total records .
       Steps for Postgres COPY :

      While Importing CSV files with text fields that contain double quotes using COPY
      1. connecting windows server

      D:
      cd D:\PostgresPlus\9.4AS\bin
      psql -U enterprisedb -d tutorialdba

      2.Sample Data’s :

      "city_name"|"pincode"|"taluka_name"|"district_name"|"state_name"
      "Arong"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
      "Sawai"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
      "Chuckchucha"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
      "Kinyuka"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
      "Big Lapati"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"
      "Perka"|"744301"|"Car Nicobar"|"Nicobar"|"Andaman and Nicobar Islands"

      3.following copy command will neglect the double quote

      2ndquadrant.in=# COPY ehis.temp FROM 'D:\2ndquadrant/nijamnew.csv' DELIMITER '|' CSV HEADER;
      ERROR:  character with byte sequence 0x9d in encoding "WIN1252" has no equivalent in encoding "UTF8"
      CONTEXT:  COPY citytemp, line 358646

      You need to change the character set on your windows command line before running the script with chcp. Or in postgresql you can:
      SET CLIENT_ENCODING TO ‘utf8’;
      Before importing the file.

      2ndquadrant.in=# SET CLIENT_ENCODING TO 'utf8';
      SET
      2ndquadrant.in=# COPY ehis.citytemp FROM 'D:\2ndquadrant/city_master_data.csv' DELIMITER '|' CSV HEADER;
      COPY 679249

      got it below error once time after that changed

      ERROR:  character with byte sequence 0xe0 0xa4 0xaa in encoding "UTF8" has no equivalent in encoding "WIN1252"
      HealthCraft_DC=# set client_encoding to 'WIN1252';
      SET

      For Example,

      if some column values missing means you have to insert null values which column is not values, for this purpose below command will be helpful to restore the data to that table

      Example nijam.csv file contains following data means

      id name
      1  nijam
      2  karthick
         nijam
      4    
      5

      So while copying the values you have to mention value oof null otherwise copy command will throw the error

      COPY schema.table FROM 'D:\2ndquadrant\nijam.csv' delimiter ',' csv  NULL AS 'null';

      III. Count the Already existing table total records, the table name is perm
      IV. comparing both table difference by using following anyone of the query

      select city_name
      from ehis.temp T1
      where not exists (SELECT cityname FROM ehis.perm T2 where T1.city_name = T2.cityname)
               (OR)
      
      select citytemp.city_name
      from ehis.temp,ehis.perm
      where temp.city_name<>perm.cityname
               (OR)
      
      select count(city_name) from ehis.temp
      where city_name not in (select cityname from ehis.perm)
               (OR)
      
      SELECT city_name
        FROM ehis.citytemp
        WHERE NOT EXISTS 
         (SELECT cityname FROM ehis.citymaster 
        WHERE citytemp.city_name = citymaster.cityname);
      
               (OR)
      select * from ehis.temp
      where city_name not in (select cityname from ehis.perm)
      
      ## If you want to create missing table records means you have to use CTAS method below command will be helpful to get missing records to some different table name. 
      
      create table ehis.not_in_perm as select * from ehis.temp
      where city_name not in (select cityname from ehis.perm)
      
      ## This will return without duplication values :
      create table ehis.district as select DISTINCT(district_name),state_name from ehis.temp
      where district_name not in (select districtname from ehis.perm)
      
      

      V. Copying the missing records table by using following postgres COPY command

      copy ehis.not_in_perm to 'D:\2ndqu\nijam1.csv' with delimiter ',' CSV header;

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      While Importing CSV files with text fields that contain double quotes using postgres COPY
      February 12, 2019

      Next post

      monitoring PostgreSQL master and slave server
      February 13, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Tips to Choose the Best Serviced Office for Your Business
      24May2022
      What key considerations do you need to keep in mind when choosing a new serviced office to house your business?
      24May2022
      The Benefits of Coworking
      24May2022
      The Long-Term Impact of Coworking
      24May2022
      Are you spending more money than you earn? Outsource with Ease and Move When You’re Ready
      24May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?