How to Verify Large Lists of Records in Odoo19 Using PostgreSQL

Working with huge amounts of information in Odoo 19, particularly in modules like Sales, requires more than just the user interface. Inconsistencies may occur during migrations, integrations such as Shopify synchronization, and bulk data imports that are not immediately apparent. Missing clients, duplicate orders, incorrect totals, and incomplete order lines can all have a significant impact on business operations if not identified early. This is where PostgreSQL-level validation comes in helpful, since it allows you to audit and validate large volumes of data quickly and precisely.

High-performance validation queries can be carried out without the overhead of the ORM thanks to PostgreSQL's direct access to Odoo's underlying data structure. You can validate thousands or even millions of documents in a matter of seconds rather than manually reviewing each one. For consultants and developers working on large-scale deployments where data quality is crucial before going live, this method is especially helpful.

You can develop a reusable PostgreSQL function that audits important problems in sale orders and the order lines that go with them in order to streamline and standardize this validation process. The most important data inconsistencies, such as missing partners, orphan connections, orders without lines, duplicate order references, mismatched totals, and inaccurate line data, are the subject of the subsequent function.

Audit Function Code:

CREATE FUNCTION audit_sale_orders_detailed()
RETURNS TABLE (
    total_orders BIGINT,
    missing_partner BIGINT,
    orphan_partner BIGINT,
    orders_without_lines BIGINT,
    duplicate_orders BIGINT,
    mismatched_totals BIGINT,
    zero_amount_orders BIGINT,
    negative_amount_orders BIGINT,
    lines_missing_product BIGINT,
    zero_quantity_lines BIGINT
) AS
$$
BEGIN
    SELECT COUNT(*) INTO total_orders FROM sale_order;
    SELECT COUNT(*) INTO missing_partner
    FROM sale_order WHERE partner_id IS NULL;
    SELECT COUNT(*) INTO orphan_partner
    FROM sale_order so
    LEFT JOIN res_partner rp ON so.partner_id = rp.id
    WHERE so.partner_id IS NOT NULL AND rp.id IS NULL;
    SELECT COUNT(*) INTO orders_without_lines
    FROM sale_order so
    LEFT JOIN sale_order_line sol ON sol.order_id = so.id
    WHERE sol.id IS NULL;
    SELECT COUNT(*) INTO duplicate_orders
    FROM (
        SELECT name FROM sale_order
        GROUP BY name HAVING COUNT(*) > 1
    ) dup;
    SELECT COUNT(*) INTO mismatched_totals
    FROM (
        SELECT so.id
        FROM sale_order so
        JOIN sale_order_line sol ON sol.order_id = so.id
        GROUP BY so.id, so.amount_total
        HAVING ROUND(so.amount_total::numeric, 2) != 
               ROUND(SUM(sol.price_total)::numeric, 2)
    ) mismatch;
    SELECT COUNT(*) INTO zero_amount_orders
    FROM sale_order WHERE amount_total = 0;
    SELECT COUNT(*) INTO negative_amount_orders
    FROM sale_order WHERE amount_total < 0;
    SELECT COUNT(*) INTO lines_missing_product
    FROM sale_order_line WHERE product_id IS NULL;
    SELECT COUNT(*) INTO zero_quantity_lines
    FROM sale_order_line WHERE product_uom_qty = 0;
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

After the function is established, a straightforward select statement may be used to run it straight from the PostgreSQL terminal. You can rapidly assess the general quality of your sales data by looking at the result, which will produce a single row with aggregated numbers of various data inconsistencies.

SELECT * FROM audit_sale_orders_detailed();

As seen in the graphic below, the result usually consists of a summarized row that indicates how many records fit into each category of potential problem. This allows you to see right away whether your dataset is clean or needs more research. Before moving on with business activities or system go-live, for instance, a non-zero value in orders without lines or mismatched totals signals serious issues that need to be fixed.

In practical situations, this type of audit is particularly helpful following data migrations, in which records are moved from previous Odoo versions, or during integrations with external platforms, such as Shopify, where data synchronization may result in errors. Additionally, it is quite helpful following bulk imports, which increase the possibility of errors by inserting big amounts of data into the system quickly. You can rapidly verify if the synced or imported data satisfies the required integrity standards by using this function.

The effectiveness of this strategy is another crucial component. It avoids the cost of Odoo's ORM and drastically cuts down on execution time because the validation is carried out directly at the database level. Because of this, it may be applied to very big datasets where conventional validation techniques would be too slow or unfeasible.

To read more about How to Create PL/pgSQL Functions for Odoo 19 Operations, refer to our blog How to Create PL/pgSQL Functions for Odoo 19 Operations.