0

I have many PostgreSQL dump files such this and because I import these files automatically into another database server (H2) and SET command is not compatible with this database, I need to remove all lines up to the very first INSERT command.

How can I do it in shell script?

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.8
-- Dumped by pg_dump version 9.6.8

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Data for Name: workgroups; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.workgroups.....

Thank you.

1

You can do it using sed:

sed -n '/^INSERT/,$p' file.sql
  • It says print the data from the line which begins with INSERT ... to the end.
    • From desired pattern: /^INSERT/
    • To the ,
    • End $
    • Print p

To modify the file instead of viewing it:

sed -n -i.bk '/^INSERT/,$p' file.sql
  • It would keep the original files with .bk suffix.

To run it on all files:

sed -n -i.bk '/^INSERT/,$p' ~/path/to/sql_dir/*.sql

For example:

Foo
Bar
FooBar
INSERT Foo
Fantastic

Would become:

INSERT Foo
Fantastic
2
  • Perfect! And I'm wondering how not to store backup file and instead replace the original file?
    – Artegon
    Oct 10 '18 at 18:08
  • That would be sed -n -i without .bk like: sed -n -i '/^INSERT/,$p' file.sql
    – Ravexina
    Oct 10 '18 at 18:08
0

You can do this using awk:

awk '/^INSERT/{x=1}x==1' <in >out        # including the INSERT line
awk 'x==1{print}/^INSERT/{x=1}' <in >out # including the INSERT line

This reads the file in line by line and sets x=1 if a line beginning with “INSERT” is found. If x is 1, it prints the currently processed line – in the first expression {print} was omitted as it is the default action. The output is saved in the file out, leave out >out to print to the terminal instead.

Source: awk: print lines after match to end of file

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.