From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | "Gregory Williamson" <Gregory(dot)Williamson(at)digitalglobe(dot)com> |
Cc: | "Martin" <martin(at)cornhobble(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Setting Sequence Values |
Date: | 2007-12-22 03:03:29 |
Message-ID: | 20071221190329.2200476c@commandprompt.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Martin | 2007-12-22 04:13:18 | Re: Setting Sequence Values |
Previous Message | Bruce Momjian | 2007-12-22 02:44:38 | Re: Killing a session in windows |