I have several tables, and I need to create a matching _hist table for each one. For example if I have:

CREATE TABLE public.test (
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()

I would like to create:

CREATE TABLE public.test_hist (
    "action"    text,
    "user"      text,
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()

I'm in the initial phase of the project I'm working on, and requirements keep changing.

As I run the changed script that drops and re-creates tables, I would like for _hist tables to somehow get automatically generated, so I don't need to make the same change in two places.

Is it possible to do that with SQL at the end of my table creation script?

I have postgreSQL 10.

Or should I do it in python?


1 Answer 1


Most of these procedural things can be done using a PL/pgSQL DO Block. So you don't have to get into another language. Assuming your main tables have a naming pattern like 'mt_%', you could do something like the following.

  main_table text;
for main_table in
    'public.' || quote_ident(table_name)
    table_name like 'mt_%'
    and table_schema = 'public'
  execute 'CREATE TABLE ' || main_table || '_hist ("action" text, "user" text, like ' || main_table || ' including all)';
end loop;

Building a long query using concatenation will not be neat for maintainability. So you could replace the single create query with a series of create and alter commands within the loop.

I have tried this on PG 11.4.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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