Step-by-Step Guide to Bulk Product Insert

Step 1: Prepare the CSV File

• Create a .csv file with the same name as the table: tblproduct.csv.
• Ensure that all the column headings in the .csv file match the table column names and are in the same sequence.

Step 2: Drop Index

• Right-click on the tblproduct table and select the "Script" option.

• Choose "Create Script" and copy the query to drop the index idx_tblproduct_additionalinfo_category.

  The query is:

  DROP INDEX IF EXISTS public.idx_tblproduct_additionalinfo_category;

Step 3: Change Data Type of additionalinfo Column

• Change the datatype of the additionalinfo field from jsonb to text using the following query:

  ALTER TABLE tblproduct 
  ALTER COLUMN additionalinfo TYPE text 
  USING additionalinfo::text;

Step 4: Import the CSV File

• Follow these steps to import the .csv file:
  1. Right-click on the tblproduct table.
  2. Select the "Import" option.
  3. Choose and upload the .csv file, then click "OK".

Step 5: Format additionalinfo Column for Inserted Rows

• After importing the data, check the additionalinfo field. The data might look like:

  [
    {
      Category: Battery,
      KeyValue: {
        type: Lead,
        voltage: 60,
        ampereHour: 32
      }
    }
  ]
  • The required format should be:

  [
    {
      "Category": "Battery",
      "KeyValue": {
        "type": "Lead",
        "voltage": "60",
        "ampereHour": "32"
      }
    }
  ]

• To format the data correctly for the new inserted rows, run the following query:

  UPDATE tblproduct 
  SET additionalinfo = regexp_replace(
      regexp_replace(
          additionalinfo,
          '([a-zA-Z0-9_]+)\s*:\s*([a-zA-Z0-9_]+|\d+)', 
          '"\1": "\2"', 
          'g'
      ), 
      'KeyValue:\s*\{([^\}]+)\}', 
      '"KeyValue": {\1}', 
      'g'
  ) 
  WHERE productcode BETWEEN 'P-SC-00333' AND 'P-SC-00348';

  In this case, you have inserted new products with product codes from P-SC-00333 to P-SC-00348.

Step 6: Convert Data Type Back to JSONB

• After formatting the data, convert the additionalinfo column back to jsonb using the following query:

  ALTER TABLE tblproduct
  ALTER COLUMN additionalinfo TYPE jsonb 
  USING additionalinfo::jsonb;

Step 7: Recreate the Index

• Finally, run the query to create the existing index:

  CREATE INDEX IF NOT EXISTS idx_tblproduct_additionalinfo_category
      ON public.tblproduct USING btree
      ((additionalinfo ->> 'Category'::text) COLLATE pg_catalog."default" ASC NULLS LAST)
      TABLESPACE pg_default;

Step-by-Step Guide to Bulk Product Update

Step 1: Create a Temporary Table

• First, create a temporary table named updateProduct with the necessary fields: productcode, status, and amount.

CREATE TABLE updateProduct (
    productcode text,
    status text,
    amount numeric
);

Step 2: Import the CSV File

• Prepare a updateProduct.csv file containing the data for the update with the same column heading as the updateProduct table has.
• Import this .csv file into the updateProduct table.

How to Easily Create the .csv File?

To quickly create the .csv file, follow these steps:

1. Go to Fleeto EV Product Page and navigate to the Product section.
2. Filter the products for which you want to update status or amount.
3. In the exported file, you will find several columns, including:

   • productcode

   • status

   • amount

4. Delete any unnecessary columns (other than productcode, status, and amount).
5. Now update the status and amount in Excel as you want, and you're ready for the next steps.

Step 3: Update Product Status

• For example, update the status for products in tblproduct where the corresponding status in updateProduct differs from the current status.

UPDATE tblproduct
SET status = u.status
FROM updateProduct u
WHERE tblproduct.productcode = u.productcode
AND u.status <> tblproduct.status;

Step 4: Update Product Amount

• For example, update the amount in tblproduct when the amount in updateProduct is greater than 0, ensuring that the amounts differ and that the item type is 'Scooter'.

UPDATE tblproduct
SET amount = u.amount
FROM updateProduct u
WHERE tblproduct.productcode = u.productcode
AND u.amount <> tblproduct.amount
AND tblproduct.itemtype = 'Scooter'
AND u.amount > 0;

Step 5: Drop the Temporary Table

At last remove the temporary table.

DROP TABLE updateProduct;