Scribd
Upload
12 views1 page

Generate Postgres Checksum SQL Queries

The document contains SQL commands to create a temporary table for storing checksum SQL statements. It populates this table by generating checksum SQL for each table in a specified schema, excluding the first column. Finally, it selects and displays the generated checksum SQL statements from the temporary table.

Uploaded by

Saravanan N Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
12 views1 page

Generate Postgres Checksum SQL Queries

The document contains SQL commands to create a temporary table for storing checksum SQL statements. It populates this table by generating checksum SQL for each table in a specified schema, excluding the first column. Finally, it selects and displays the generated checksum SQL statements from the temporary table.

Uploaded by

Saravanan N Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SET client_min_messages TO WARNING;

-- Drop the temporary table if it exists


DROP TABLE IF EXISTS temp_checksum_sql;

-- Create a temporary table to store the SQL statements


CREATE TEMP TABLE temp_checksum_sql (
table_name TEXT,
checksum_sql TEXT
);

-- Insert the checksum SQL statements into the temporary table


INSERT INTO temp_checksum_sql (table_name, checksum_sql)
SELECT
t.table_schema || '.' || t.table_name AS table_name,
FORMAT(
'\copy (SELECT %I, md5(concat(%s)) AS checksum FROM %I.%I) TO %L DELIMITER
''|'' CSV HEADER',
pc.column_name,
string_agg(
'COALESCE(' || c.column_name || ', '''')',
', '
ORDER BY c.ordinal_position
),
t.table_schema,
t.table_name,
:'base_path' || '/Postgres/Post/cksum/' || t.table_name || '.csv' -- Use
the base path parameter here
) AS checksum_sql
FROM
information_schema.tables t
JOIN
information_schema.columns c
ON t.table_name = c.table_name AND t.table_schema = c.table_schema
JOIN
(
SELECT
table_name,
table_schema,
column_name
FROM
information_schema.columns
WHERE
dtd_identifier = '1'
) AS pc
ON t.table_name = pc.table_name AND t.table_schema = pc.table_schema
WHERE
t.table_schema = :'schema_name'
AND
c.column_name != pc.column_name -- Exclude the first column from string_agg
GROUP BY
t.table_schema,
t.table_name,
pc.column_name;

-- Select from the temporary table to display the checksum SQL statements
SELECT checksum_sql FROM temp_checksum_sql;

You might also like