Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Hugo Felipe
Added: Apr 19, 2018 5:47 PM
Modified: May 29, 2018 2:25 PM
Views: 17
Tags: no tags
  1. SELECT 'SELECT setval(''' || n.nspname || '.' ||
  2.        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.adsrc,
  3.                                                                        '(',''),')',''),'::',''),'textregclass',''),'nextval',''),'regclass',''),'''',''),
  4.                n.nspname||'.','')||''',(SELECT MAX('||ab.attname||') FROM '||
  5.        n.nspname|| '.'||c.relname||'),true);' AS seqname
  6. FROM pg_class c
  7.   JOIN pg_attrdef a ON c.oid=a.adrelid
  8.   JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname NOT LIKE 'pg_%'
  9.   JOIN pg_index i ON i.indrelid=c.oid AND i.indisprimary='t'
  10.   JOIN pg_attribute ab ON ab.attrelid=c.oid AND ab.attisdropped='f' AND ab.atthasdef='t' AND i.indkey[0]=ab.attnum AND i.indkey[1] IS NULL
  11. WHERE a.adsrc LIKE 'nextval%';