Re: Setting Sequence Values

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 21 Dec 2007 18:14:43 -0700
D"Gregory Williamson" <Gregory(dot)Williamson(at)digitalglobe(dot)com> wrote:

> I think the OP needs a way to do _all_ of the sequences, which can be
> a little dauning if you have lots of tables. I'm sure there's a way
> but I haven't the time to puzzle it out -- off to SF for a(n)
> (im)moderate celebration. I might hack at this later tonight if I am
> capable.

Shout out to AndrewSN for this one (although I was almost there when he
pasted it ;)):

SELECT c1.relname AS sequencename, n.nspname AS schema,
c2.relname AS tablename, a.attname AS columnname
FROM pg_class c1
JOIN pg_depend d ON (d.objid=c1.oid)
JOIN pg_class c2 ON (d.refobjid=c2.oid)
JOIN pg_attribute a ON (a.attrelid=c2.oid AND a.attnum=d.refobjsubid)
JOIN pg_namespace n ON (n.oid=c2.relnamespace)
WHERE c1.relkind='S'
AND d.classid='pg_class'::regclass
AND d.refclassid='pg_class'::regclass
AND d.refobjsubid > 0
AND d.deptype='a';

sequencename | schema | tablename | columnname
- --------------+--------+-----------+------------
foo_id_seq | public | foo | id
(1 row)

- From there, scripting should be easy.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHbH6DATb/zqfZUUQRAlY9AJ9UwlaveD91Hw5FXv5YsHyfzfKIVACgrNQH
jwBU/EglIibnw9Nz9mgzg1w=
=7pot
-----END PGP SIGNATURE-----