how to get sequence alter query for all tables in postgresql

Question

As following up my previous question's answer.
i need write script to alter sequences for all my tables(100+) given below is an example for table gcompany

alter sequence seq_gcompany_id owned by company.companyid;

how to get this Alter query for all the sequence ?

hint: all the sequences name starts with seq_g

Vivek S. · Accepted Answer · 2014-08-01T08:37:23.767

Try this.

Select  'alter sequence '||_seq||' owned by '||_table||'.'||_column||';' FROM (SELECT   
  n.nspname AS _schema,
  c.relname AS _table,
  a.attname AS _column,
  SUBSTRING(d.adsrc FROM E'nextval\\(''([^'')]+)''') AS _seq
FROM pg_attrdef       AS d
  JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
  JOIN pg_class     AS c ON c.oid = d.adrelid
  JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE adsrc LIKE 'nextval(''%') t;

and execute the output as pgScript

Read about pg_class and pg_attribute

how to get sequence alter query for all tables in postgresql

1 Answers1