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

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.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\\)';

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073