Cookie Consent by Free Privacy Policy Generator 📌 How to check if the PostgreSQL tables of the same structure have identical data using aggregated hash and md5?


✅ How to check if the PostgreSQL tables of the same structure have identical data using aggregated hash and md5?


💡 Newskategorie: Programmierung
🔗 Quelle: dev.to

In this blog, I’ll demonstrate how to check if the PostgreSQL tables of the same structure have identical data using aggregated hash and md5.

Create two tables with the same structure: some_table1 and some_table2:

create table some_table1(val1 serial, val2 varchar(10), val3 timestamp);

create table some_table2(val1 serial, val2 varchar(10), val3 timestamp);

Create a primary key on the tables to ensure the val1 column identifies the table row uniquely:

alter table some_table1 add primary key (val1);

alter table some_table2 add primary key (val1);

Check the structure of the tables:

postgres=# \d some_table1
                                       Table "public.some_table1"
 Column |            Type             | Collation | Nullable |                  Default                  
--------+-----------------------------+-----------+----------+-------------------------------------------
 val1   | integer                     |           | not null | nextval('some_table1_val1_seq'::regclass)
 val2   | character varying(10)       |           |          | 
 val3   | timestamp without time zone |           |          | 
Indexes:
    "some_table1_pkey" PRIMARY KEY, btree (val1)

postgres=# \d some_table2
                                       Table "public.some_table2"
 Column |            Type             | Collation | Nullable |                  Default                  
--------+-----------------------------+-----------+----------+-------------------------------------------
 val1   | integer                     |           | not null | nextval('some_table2_val1_seq'::regclass)
 val2   | character varying(10)       |           |          | 
 val3   | timestamp without time zone |           |          | 
Indexes:
    "some_table2_pkey" PRIMARY KEY, btree (val1)
postgres=#

Populate the tables with some sample data:

insert into some_table1 values(default, 'One', '2022-12-11 19:07:00');
insert into some_table1 values(default, 'Two', '2022-12-11 19:08:00');
insert into some_table1 values(default, 'Three', '2022-12-11 19:09:00');

insert into some_table2 values(default, 'One', '2022-12-11 19:07:00');
insert into some_table2 values(default, 'Two', '2022-12-11 19:08:00');
insert into some_table2 values(default, 'Three', '2022-12-11 19:09:00');

Check if the tables are identical:

with t1 as (select md5(array_agg(md5((t.*)::text))::text) chk_sum
               from (select *
                     from some_table1
                     order by 1) t
           ),
     t2 as (select md5(array_agg(md5((t.*)::text))::text) chk_sum
               from (select *
                     from some_table2
                     order by 1) t
           )
 select 
    case 
      when 
        t1.chk_sum = t2.chk_sum 
      then 
        'Identical' 
      else 
        'Non-Identical' 
      end the_answer
    from t1, t2;

Check the case when the tables are not identical:

postgres=# insert into some_table2 values(default, 'Four', '2022-12-11 19:10:00');
INSERT 0 1
postgres=# 
postgres=# select * from some_table1;
 val1 | val2  |        val3         
------+-------+---------------------
    1 | One   | 2022-12-11 19:07:00
    2 | Two   | 2022-12-11 19:08:00
    3 | Three | 2022-12-11 19:09:00
(3 rows)

postgres=# select * from some_table2;
 val1 | val2  |        val3         
------+-------+---------------------
    1 | One   | 2022-12-11 19:07:00
    2 | Two   | 2022-12-11 19:08:00
    3 | Three | 2022-12-11 19:09:00
    4 | Four  | 2022-12-11 19:10:00
(4 rows)
postgres=# 
with t1 as (select md5(array_agg(md5((t.*)::text))::text) chk_sum
               from (select *
                     from some_table1
                     order by 1) t
           ),
     t2 as (select md5(array_agg(md5((t.*)::text))::text) chk_sum
               from (select *
                     from some_table2
                     order by 1) t
           )
 select 
    case 
      when 
        t1.chk_sum = t2.chk_sum 
      then 
        'Identical' 
      else 
        'Non-Identical' 
      end the_answer
    from t1, t2;

There is another version of the query:

select 
    t1.chk_sum chk_sum_table1,
    t2.chk_sum chk_sum_table2,
    case 
      when 
        t1.chk_sum = t2.chk_sum 
      then 
        'Identical' 
      else 
        'Non-Identical' 
      end the_answer
    from (select md5(array_agg(md5((t.*)::text))::text) chk_sum
               from (select *
                     from some_table1
                     order by 1) t
           ) t1, 
           (select md5(array_agg(md5((t.*)::text))::text) chk_sum
               from (select *
                     from some_table2
                     order by 1) t
           ) t2;

Conclusion:

In this blog, I’ve demonstrated SQL query to check if the PostgreSQL tables of the same structure have identical data using aggregated hash and md5.

...

✅ Monomorph - MD5-Monomorphic Shellcode Packer - All Payloads Have The Same MD5 Hash


📈 64.37 Punkte

✅ Identical Twins Are Not So Identical, Study Suggests


📈 47.88 Punkte

✅ SPVM::Digest::MD5 - MD5


📈 35.71 Punkte

✅ How to Apply Sorting on Tables in HTML Using JavaScript: Sortable Paginated Tables


📈 34.46 Punkte

✅ dcipher – Online Hash Cracking Using Rainbow & Lookup Tables


📈 31.96 Punkte

✅ re:publica 2024: „Chatkontrolle“ – Same same, but different, but still same?


📈 31.63 Punkte

✅ HTML Tables: how to create and style tables with HTML


📈 31.42 Punkte

✅ Coding bootcamps and 4-year colleges have nearly identical percentage of alumni employed at Big Five: report


📈 31.14 Punkte

✅ Pivot Challenge: Can you analyze this aggregated data?


📈 31.04 Punkte

✅ Python - Use Hash Tables (Dictionaries) for Fast Data Retrieval


📈 30.44 Punkte

✅ Data Structures - Hash Tables


📈 30.44 Punkte

✅ Understanding Hash Tables: The Backbone of Efficient Data Storage


📈 30.44 Punkte

✅ MD5/SHA1: Sloth-Angriffe nutzen alte Hash-Algorithmen aus


📈 30.3 Punkte

✅ MD5/SHA1: Sloth-Angriffe nutzen alte Hash-Algorithmen aus


📈 30.3 Punkte

✅ IGHASHGPU – GPU Based Hash Cracking – SHA1, MD5 & MD4


📈 30.3 Punkte

✅ MD5/SHA1: Sloth-Angriffe nutzen alte Hash-Algorithmen aus


📈 30.3 Punkte

✅ MD5/SHA1: Sloth-Angriffe nutzen alte Hash-Algorithmen aus


📈 30.3 Punkte

✅ IGHASHGPU – GPU Based Hash Cracking – SHA1, MD5 & MD4


📈 30.3 Punkte

✅ WordPress bis 4.8.2 Password Hash MD5 schwache Verschlüsselung


📈 30.3 Punkte

✅ [shellcode] Linux/x86 - pwrite(/etc/shadow, (md5 hash of agix), 32, 8) Shellcode (89 bytes)


📈 30.3 Punkte

✅ The (non-secure) MD5 Hash Algorithm


📈 30.3 Punkte

✅ How to Generate an MD5 File Hash in Node.js?


📈 30.3 Punkte

✅ CVE-1999-0900 | Linux Kernel MD5 Hash Generator rpc.yppasswdd memory corruption (SBV-1028)


📈 30.3 Punkte

✅ If I log in to a site using my Google account, is that the same as using the same password on two sites?


📈 30.23 Punkte

✅ Microsoft Excel Can Now Turn Pictures of Tables Into Actual, Editable Tables


📈 29.89 Punkte











matomo

Datei nicht gefunden!