Thread: updating a sequence

Hi,
I have need of a statement that updates the sequence but uses a max() to find 
the number.

alter sequence somename restart with (select max(pk) from sometable).

I need this for automating an ETL (using pentaho).

Postgres 8.4

Thanks in advance,
Johnf

On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani <johnf@jfcomputer.com> wrote:

> alter sequence somename restart with (select max(pk) from sometable).
>
> I need this for automating an ETL (using pentaho).

http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNCTIONS-SEQUENCE-TABLE


-- 
Regards,
Richard Broersma Jr.

On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
> On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani <johnf@jfcomputer.com> wrote:
> > alter sequence somename restart with (select max(pk) from sometable).
> > 
> > I need this for automating an ETL (using pentaho).
> 
> http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
> TIONS-SEQUENCE-TABLE


I don't see how that helps answer my problem.  I know how to update a 
sequence.  I want to pass a value for the restart vaue that depends on a query 
- all in one statement.  I would think it is a common problem i.e. migrating 
data.

Thanks,
Johnf

On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani <johnf@jfcomputer.com> wrote:
>>
>> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
>> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani <johnf@jfcomputer.com>
>> > wrote:
>> > > alter sequence somename restart with (select max(pk) from sometable).
>> > >
>> > > I need this for automating an ETL (using pentaho).
>> >
>> >
>> > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
>> > TIONS-SEQUENCE-TABLE
>>
>>
>> I don't see how that helps answer my problem.  I know how to update a
>> sequence.  I want to pass a value for the restart vaue that depends on a
>> query
>> - all in one statement.  I would think it is a common problem i.e.
>> migrating
>> data.
>>
>
> use a subquery to set the value -
> select setval('foo', select max(some_id) from some_table)
> It's all right there in the docs that you were pointed to. We try to
> encourage people to be somewhat self sufficient around here.

You need to wrap a subselect in ():

select setval('foo', (select max(some_id) from some_table));

That works in 9.1.1.  No clue about previous versions off the top of
my head, but I seem to recall it doesn't work in 8.3 and prior
versions.

On Tue, Nov 15, 2011 at 8:46 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
> <sgendler@ideasculptor.com> wrote:
>> On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani <johnf@jfcomputer.com> wrote:
>>>
>>> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
>>> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani <johnf@jfcomputer.com>
>>> > wrote:
>>> > > alter sequence somename restart with (select max(pk) from sometable).
>>> > >
>>> > > I need this for automating an ETL (using pentaho).
>>> >
>>> >
>>> > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
>>> > TIONS-SEQUENCE-TABLE
>>>
>>>
>>> I don't see how that helps answer my problem.  I know how to update a
>>> sequence.  I want to pass a value for the restart vaue that depends on a
>>> query
>>> - all in one statement.  I would think it is a common problem i.e.
>>> migrating
>>> data.
>>>
>>
>> use a subquery to set the value -
>> select setval('foo', select max(some_id) from some_table)
>> It's all right there in the with docs that you were pointed to. We try to
>> encourage people to be somewhat self sufficient around here.
>
> You need to wrap a subselect in ():
>
> select setval('foo', (select max(some_id) from some_table));
>
> That works in 9.1.1.  No clue about previous versions off the top of
> my head, but I seem to recall it doesn't work in 8.3 and prior
> versions.

Was wrong, it definitely works in 8.3.  But only with the parens.

Sent from my iPhone

On Nov 15, 2011, at 7:49 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> On Tue, Nov 15, 2011 at 8:46 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
>> <sgendler@ideasculptor.com> wrote:
>>> On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani <johnf@jfcomputer.com> wrote:
>>>>
>>>> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
>>>>> On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani <johnf@jfcomputer.com>
>>>>> wrote:
>>>>>> alter sequence somename restart with (select max(pk) from sometable).
>>>>>>
>>>>>> I need this for automating an ETL (using pentaho).
>>>>>
>>>>>
>>>>> http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html#FUNC
>>>>> TIONS-SEQUENCE-TABLE
>>>>
>>>>
>>>> I don't see how that helps answer my problem.  I know how to update a
>>>> sequence.  I want to pass a value for the restart vaue that depends on a
>>>> query
>>>> - all in one statement.  I would think it is a common problem i.e.
>>>> migrating
>>>> data.
>>>>
>>>
>>> use a subquery to set the value -
>>> select setval('foo', select max(some_id) from some_table)
>>> It's all right there in the with docs that you were pointed to. We try to
>>> encourage people to be somewhat self sufficient around here.
>>
>> You need to wrap a subselect in ():
>>
>> select setval('foo', (select max(some_id) from some_table));
>>
>> That works in 9.1.1.  No clue about previous versions off the top of
>> my head, but I seem to recall it doesn't work in 8.3 and prior
>> versions.
>
> Was wrong, it definitely works in 8.3.  But only with the parens.


Yes. My version without parens was just a typo. I didn't test it before sending it

On Tuesday, November 15, 2011 07:46:19 pm Scott Marlowe wrote:
> On Tue, Nov 15, 2011 at 5:33 PM, Samuel Gendler
> 
> <sgendler@ideasculptor.com> wrote:
> > On Tue, Nov 15, 2011 at 4:28 PM, John Fabiani <johnf@jfcomputer.com> 
wrote:
> >> On Tuesday, November 15, 2011 08:33:54 am Richard Broersma wrote:
> >> > On Tue, Nov 15, 2011 at 8:06 AM, John Fabiani <johnf@jfcomputer.com>
> >> > 
> >> > wrote:
> >> > > alter sequence somename restart with (select max(pk) from
> >> > > sometable).
> >> > > 
> >> > > I need this for automating an ETL (using pentaho).
> >> > 
> >> > http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html
> >> > #FUNC TIONS-SEQUENCE-TABLE
> >> 
> >> I don't see how that helps answer my problem.  I know how to update a
> >> sequence.  I want to pass a value for the restart vaue that depends on a
> >> query
> >> - all in one statement.  I would think it is a common problem i.e.
> >> migrating
> >> data.
> > 
> > use a subquery to set the value -
> > select setval('foo', select max(some_id) from some_table)
> > It's all right there in the docs that you were pointed to. We try to
> > encourage people to be somewhat self sufficient around here.
> 
> You need to wrap a subselect in ():
> 
> select setval('foo', (select max(some_id) from some_table));
> 
> That works in 9.1.1.  No clue about previous versions off the top of
> my head, but I seem to recall it doesn't work in 8.3 and prior
> versions.

Thanks that did work with the parens.  

I could not get to work earlier.

Johnf

On Tuesday, November 15, 2011 08:06:30 am John Fabiani wrote:
> Hi,
> I have need of a statement that updates the sequence but uses a max() to
> find the number.
> 
> alter sequence somename restart with (select max(pk) from sometable).
> 
> I need this for automating an ETL (using pentaho).
> 
> Postgres 8.4
> 
> Thanks in advance,
> Johnf


Thanks everyone!

Johnf

On 2011-11-16, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> You need to wrap a subselect in ():
>
> select setval('foo', (select max(some_id) from some_table));


I prefer to do it in once select like this:

select setval('foo', max(some_id)) from some_table;


-- 
⚂⚃ 100% natural