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

Question

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.

Actually, SQL would be a good tool for this. I would import the value to PostgreSQL as a `smallint`, then add a column for the boolean value, use `UPDATE` to set the boolean column to true where the smallint is 1, then drop the smallint column. — Bill Karwin, Apr 13 '22 at 15:22
Or just using any decent editor write a little macro to search for and amend that value — RiggsFolly, Apr 13 '22 at 15:29
I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — , Apr 13 '22 at 15:48

score 1 · Accepted Answer · 2022-04-13T18:38:41.043

1

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.

edited Apr 13 '22 at 18:38

answered Apr 13 '22 at 15:32

Thank you! This worked for me. I just used nano as the text editor of choice and help down alt + R to replace the `1,` with `true,` and that helped me edit the entire sql script. – kodazys Apr 13 '22 at 16:31

score 0 · Answer 2 · answered Apr 13 '22 at 15:45

You can change the data type to integer before the INSERTs and back afterwards:

CREATE TABLE product (
   sku text NOT NULL,
   name text NOT NULL,
   description text,
   image_url text NOT NULL,
   active boolean NOT NULL,
   units_in_stock integer NOT NULL,
   unit_price numeric(15,2) NOT NULL,
   category_id integer NOT NULL,
   date_created timestamp with time zone NOT NULL
);

ALTER TABLE product ALTER active TYPE integer USING active::integer;

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());

ALTER TABLE product ALTER active TYPE boolean USING active::boolean;

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

2 Answers2