I have a database dumped to sql with create table commands and I need to create a script to apply a given command to some of the objects, such as dropping the table as an example.

In the case of dropping the tables for instance I scan for lines starting with create table and extract the 3rd field with the cut command. After that I want to prefix each output line with a command such as drop table followed by cascade; or another suitable commands.

The command I use now is cat datadump.sql | cut -d ' ' -f 1-3 | grep 'CREATE TABLE' | uniq | cut -d ' ' -f 3.

This outputs a list like


which I want to convert to

drop table table1 cascade;
drop table table2 cascade;

etc. and save it a file to be run when needed. In fact I'd like to make the prefix and suffix conditional on the input but for now simple literal text should suffice.

Here is some sample input as requested in the comments:

    agrecordid integer DEFAULT nextval(('agency0_pk_seq'::text)::regclass) NOT NULL,
    agidcode1 character(20) NOT NULL,
    agaddress1 character(40),
    agtown character(40),
    agfax2 character(20),
    agemail character(20)

CREATE TABLE agency_contact (
    recordid integer DEFAULT nextval(('agencon0_pk_seq'::text)::regclass) NOT NULL,
    agencyid integer,
    name character varying(35),
    email2 character varying(30),
    deptorperson character(1),
    notes text
  • 2
    awk '/create table/ {printf "%s\n" "drop table " $3 " cascade;"}' file > newfile – jasonwryan May 11 '15 at 0:29
  • awk is something I will need to make some time to learn. Is there anything closer to my example. I am quite a grep,sed,cat and uniq man, though it looks like learning awk might solve all those issues in one go. – vfclists May 11 '15 at 0:33
  • 2
    Without seeing your input, it is difficult to say, but that command searches for create table and prints the third field of any matches, with the other stuff around it... – jasonwryan May 11 '15 at 0:35
  • awk will do almost anything that grep, can and uniq will do, and a large proportion of what sed will do, all in one bundle. Somewhat more readable too. Well worth learning. Must admit, I would have written that: awk '/CREATE +TABLE/ { printf ("drop table %s cascade;\n", $3); }' datadump.sql – Paul_Pedant Dec 9 '19 at 12:49

Using Awk (and assuming that the output you wish to capture is the third field):

awk '/CREATE TABLE/ {printf "%s\n", "drop table "$3" cascade;"}' datadump.sql
drop table agency cascade;
drop table agency_contact cascade;
  • you missed the comma after the printf in the comment. I tried it earlier but got an error. I think it is time to leam awk :) – vfclists May 11 '15 at 1:11
  • @vfclists yes: I made that one up on the fly :) Awk is ideal for text processing and quite a simple language. – jasonwryan May 11 '15 at 1:13

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.