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?

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...0/msg00969.php

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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...0/msg00969.php

Yes! That's exactly what I needed. I new there had to be
an easy way

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.

"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 therein to 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq