CTE Query copy value if exists into an existing table I got the following query:

WITH table_data AS (
             SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description,
                    UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) AS code,
                    UNNEST(array[NOW(), NOW(), NOW()]) AS updated,
                    UNNEST(array[NOW(), NOW(), NOW()]) AS created,
                    UNNEST(array[0.15, 350.0, 5.0]) AS sales_price,
                    UNNEST(array[0.51, 550.0, 10.0]) AS price,
                    UNNEST(array['Tax0', 'cdc', '54289hijlj']) AS code_p,
                    UNNEST(array['242', 'ded', 'fjDIashdukah']) AS sales_c
           ),
        parts_inserted AS (
         INSERT INTO parts (description, item_code, created, updated, sale_p,
                                    price, code_p_id, s_id_code)
         SELECT table_data.description, table_data.code, table_data.created, table_data.updated,
                table_data.sales_price, table_data.price, rates_p.id, rates_c.id
           FROM table_data
           LEFT JOIN rates AS rates_p
              ON table_data.code_p = rates_p.code
           LEFT JOIN rates AS rates_c
              ON table_data.sales_c = rates_c.code
          JOIN parts p ON p.item_code = table_data.code
          WHERE p.company_id = 65312322
         RETURNING *
       )
       SELECT * FROM parts_inserted;


The query selects the data ( WITH table_data AS... ) and inserts it into the parts table.

Table parts:

CREATE TABLE
    parts
    (
        id INTEGER DEFAULT nextval('p_id_seq'::regclass) NOT NULL,
        company_id INTEGER,
        item_code CHARACTER VARYING,
        price DOUBLE PRECISION,
        code_p_id INTEGER,
        sale_p DOUBLE PRECISION,
        s_id_code INTEGER,
        profit DOUBLE PRECISION,
        margin DOUBLE PRECISION,
        created TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        updated TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        taxable BOOLEAN DEFAULT true,
        origin CHARACTER VARYING,
        origin_id CHARACTER VARYING,
        description TEXT,
        created_by_id INTEGER,
        name CHARACTER VARYING,
        tsv TSVECTOR
    );



If code_p exists on the rates table, the query must insert the rates.id into parts.p_code_id
If sales_c exists on the rates table, the query must insert the rates.id into parts.s_id_code
If none of them exists, the p_code_id and s_id_code must be blank.


Table rates:

CREATE TABLE
    rates
    (
        id INTEGER DEFAULT nextval('r_id_seq'::regclass) NOT NULL,
        company_id INTEGER,
        name CHARACTER VARYING,
        rate DOUBLE PRECISION,
        created TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        updated TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        type CHARACTER VARYING,
        origin CHARACTER VARYING,
        origin_id CHARACTER VARYING,
        code CHARACTER VARYING,
        is_payable BOOLEAN DEFAULT false,
        description CHARACTER VARYING,
        active BOOLEAN DEFAULT true,
        has_component BOOLEAN DEFAULT false
    );



Currently only valid code_p and sales_c row gets inserted, if  code_p/sales_c exists: 
( 
LEFT JOIN rates AS rates_p
          ON table_data.code_p = rates_p.code
       LEFT JOIN rates AS rates_c
          ON table_data.sales_c = rates_c.code
) it is inserted into parts table.
If code_p or sales_c does not matches with the rates table, the row must be inserted with blank(NULL) code_p/sales_c... and this is not happening.


What should the query do?

if code_p or sales_c matches with the rates table:

LEFT JOIN rates AS rates_p
              ON table_data.code_p = rates_p.code
           LEFT JOIN rates AS rates_c
              ON table_data.sales_c = rates_c.code


the parts.code_p_id and parts.s_id_code columns must be updated with the rates.id value.

if code_p or sales_c doesn't match with the rates table, the parts.code_p_id and parts.s_id_code columns must be blank/null.

Question:

What am I missing?
What you have so far can be rewritten to this 100 % equivalent query:

INSERT INTO parts
        (description, item_code, created,   updated,   sale_p     ,   price, code_p_id, s_id_code)
SELECT t.description, t.code , t.created, t.updated, t.sales_price, t.price, rp.id    , rc.id
FROM  (
   VALUES 
     ('Small Bus', '1dfsdf4', now(), now(), 0.15 , 0.51 , 'Tax0'      , '242')
   , ('Window'   , 'QSF-312', now(), now(), 350.0, 550.0, 'cdc'       , 'ded')
   , ('Test 1'   , 'EXD-TUH', now(), now(), 5.0  , 10.0 , '54289hijlj', 'fjDIashdukah')
   ) t(description, code, updated, created, sales_price, price, code_p, sales_c)
JOIN   parts       p ON pa.item_code = t.code  -- why?
LEFT   JOIN rates rp ON rp.code      = t.code_p
LEFT   JOIN rates rc ON rc.code      = t.sales_c
WHERE  p.company_id = 65312322                 -- why?
RETURNING *;


The query already inserts NULL into code_p_id and s_id_code respectively if no matching row in rates is found.

You don't need CTEs here.
Be aware that the RETURNING clause returns actual resulting values of new rows after applying any rules or triggers there might be.

You description is still unclear.
The join to parts seems highly suspicious. parts.company_id is not defined UNIQUE, the join will probably multiply rows. I seriously doubt that's intended.

You might really mean to UPDATE selected rows in table parts, not INSERT new rows ...