Search code examples
sqleditingdummy-data

Automatically change the Nth value of a 100+ line long SQL script for automatically inserting dummy data

I have a long SQL script that I'm converting from MySQL syntax to Postgres.

There are about 100+ lines of script for inserting dummy data that look like this:

INSERT INTO product (sku, name, description, image_url, active, units_in_stock,
unit_price, category_id, date_created)
VALUES ('BOOK-TECH-1001', 'Spring Framework Tutorial', 'Learn Spring',
'assets/images/products/placeholder.png'
,1,100,29.99,1, NOW());

INSERT INTO product (sku, name, description, image_url, active, units_in_stock,
unit_price, category_id, date_created)
VALUES ('BOOK-TECH-1002', 'Kubernetes - Deploying Containers', 'Learn Kubernetes',
'assets/images/products/placeholder.png'
,1,100,24.99,1, NOW());

INSERT INTO product (sku, name, description, image_url, active, units_in_stock,
unit_price, category_id, date_created)
VALUES ('BOOK-TECH-1003', 'Internet of Things (IoT) - Getting Started', 'Learn IoT',
'assets/images/products/placeholder.png'
,1,100,29.99,1, NOW());

-- etc...

I would like to change the value for the 4th column that's being inserted to true. In all 100+ insert statements it's currently set to 1 which, in MySQL, would coerce to a boolean value in that column, but does not work in Postgres.

My question is: are there any tools out there that I could use to change only the value of 1 in every statement's 4th column to true?

For example, I want to change the following:

INSERT INTO product (sku, name, description, image_url, active, units_in_stock,
unit_price, category_id, date_created)
VALUES ('BOOK-TECH-1001', 'Spring Framework Tutorial', 'Learn Spring',
'assets/images/products/placeholder.png'
,1,100,29.99,1, NOW());

to this:

INSERT INTO product (sku, name, description, image_url, active, units_in_stock,
unit_price, category_id, date_created)
VALUES ('BOOK-TECH-1001', 'Spring Framework Tutorial', 'Learn Spring',
'assets/images/products/placeholder.png'
,true,100,29.99,1, NOW());

Any suggestions or online tools I could use? Ctrl + f and replacing 1 with true won't work because I have another value in the insert statement that I want to keep at 1.

Solution

  • There are 2 possibilities.

    • You import the value as a number, then do and update set bool_col = true where numb_col = 1 then remove the numb_col and rename bool_col.
    • You use a replace statement and replace
    '
,1,

    with

    '
,true,

    I've intentionally included the single quote and newline precedent so that we only replace the 1 that we need to replace and not any others.