Обсуждение: Setting Sequence Values
After porting tables and data from FrontBase I now find that I need to update all the values for the sequences that were created to reflect the data in the tables. Is there an easy way to do this?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 21 Dec 2007 23:57:21 -0000
Martin <martin@cornhobble.com> wrote:
> After porting tables and data from FrontBase I now find that I
> need to update all the values for the sequences that were created
> to reflect the data in the tables. Is there an easy way to do
> this?
Take a look at setval.
postgres=# select setval('foo_id_seq',(select max(id) from foo));
setval
- --------
100
(1 row)
postgres=# select currval('foo_id_seq');
currval
- ---------
100
(1 row)
Sincerely,
Joshua D. Drake
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: don't forget to increase
> your free space map settings
>
- --
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)
iD8DBQFHbGQhATb/zqfZUUQRAkGqAJ9jJMF/1aYY8q7L4C35ogVnSsowAACgnwH3
J0HpPengi7eo4n4b+YLaCZA=
=uQZ6
-----END PGP SIGNATURE-----
Joshua Drake spake thusly:
>
> On Fri, 21 Dec 2007 23:57:21 -0000
> Martin <martin@cornhobble.com> wrote:
>
> > After porting tables and data from FrontBase I now find that I
> > need to update all the values for the sequences that were created
> > to reflect the data in the tables. Is there an easy way to do
> > this?
>
> Take a look at setval.
>
> postgres=# select setval('foo_id_seq',(select max(id) from foo));
> setval
> - --------
> 100
> (1 row)
<...>
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.
Cheers!
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
In article <20071221171055.5082a107@commandprompt.com>, Joshua D. Drake <jd@commandprompt.com> wrote: >> After porting tables and data from FrontBase I now find that I >> need to update all the values for the sequences that were created >> to reflect the data in the tables. Is there an easy way to do >> this? >Take a look at setval. I know all about setval. The problem is getting all the sequences set without doing each one by hand.
On Sat, 22 Dec 2007, Martin wrote: > The problem is getting all the sequences set without doing each one by > hand. See if this helps you: http://archives.postgresql.org/pgsql-general/2007-10/msg00969.php -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 21 Dec 2007 18:14:43 -0700
D"Gregory Williamson" <Gregory.Williamson@digitalglobe.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-----
Joshua Drake shaped the aether to say:
>
> 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.
A thing of beauty ! Is it portable or tied to certain versions ? (not familiar enough with system tables and changes therein to have my own opinion)
Greg W.
In article <alpine.SOC.1.00.0712212136330.20305@westnet.com>, Greg Smith <gsmith@gregsmith.com> wrote: >See if this helps you: >http://archives.postgresql.org/pgsql-general/2007-10/msg00969.php Yes! That's exactly what I needed. I new there had to be an easy way ;)
"Gregory Williamson" <Gregory.Williamson@digitalglobe.com> writes:
> Joshua Drake shaped the aether to say:
>> 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';
> A thing of beauty ! Is it portable or tied to certain versions ? (not familiar enough with system tables and changes
thereinto have my own opinion)
Offhand I believe that this would work in every PG version since 7.3.
It would definitely not work before that (7.2 had neither pg_namespace
nor pg_depend).
[pokes at it for a bit...] Actually the deptype='a' bit is not so
robust; we used to use 'i' for serial dependencies. I'd leave that test
out entirely, I think --- it doesn't seem essential, because there
isn't any other reason for a sequence to depend on a table column.
Otherwise the query seems correct.
As for possible future breakage, who can say? There's nothing else here
that I foresee problems for, but I don't have a crystal ball.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 21 Dec 2007 23:56:55 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > [pokes at it for a bit...] Actually the deptype='a' bit is not so > robust; we used to use 'i' for serial dependencies. I'd leave that > test out entirely, I think --- it doesn't seem essential, because > there isn't any other reason for a sequence to depend on a table > column. Otherwise the query seems correct. 8.2 is type 'a' (and I assume 8.3), 8.1 is 'i'. I don't know about anything older. 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) iD8DBQFHbK0vATb/zqfZUUQRAknrAKChvcHWdSpJSvCIl4nXXuJLF1CPQgCcDLVn 0ZQAn4F5mSyUaWWcaztJJC4= =jE0v -----END PGP SIGNATURE-----