Re: [PATCHES] Proposed patch for sequence-renaming problems

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Tom Lane wrote:
> > >> Does that sound like a workable compromise?
> > 
> > > Personally, I _love_ it.  I hope others do as well.  :-)
> > 
> > OK, I'll work up a patch.
> 
> Here is a query that shows nextval(::text) usage as defaults:
> 
>     SELECT    n.nspname, c.relname, a.attname, d.adsrc
>     FROM    pg_namespace n, pg_class c, pg_attribute a, pg_attrdef d
>     WHERE    n.oid = c.relnamespace AND
>         c.oid = a.attrelid AND
>         a.attrelid = d.adrelid AND
>         a.attnum = d.adnum AND
>         d.adsrc ~ '.*nextval\\(''[^'']*''::TEXT\\)';


Sorry, this is the right one:
SELECT    n.nspname, c.relname, a.attname, d.adsrcFROM    pg_namespace n, pg_class c, pg_attribute a, pg_attrdef dWHERE
  n.oid = c.relnamespace AND    c.oid = a.attrelid AND    a.attrelid = d.adrelid AND    a.attnum = d.adnum AND
d.adsrc~ '.*nextval\\(''[^'']*''::text\\)';
 

Followed by the appropriate:
ALTER TABLE sp.test2 ALTER COLUMN x DROP DEFAULT;ALTER TABLE sp.test2 ALTER COLUMN x SET DEFAULT nextval('sp.aa');

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073