pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdating a sequence
10+ messages / 6 participants
[nested] [flat]
* updating a sequence
@ 2011-11-15 16:06 John Fabiani <johnf@jfcomputer.com>
0 siblings, 2 replies; 10+ messages in thread
From: John Fabiani @ 2011-11-15 16:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-15 16:33 Richard Broersma <richard.broersma@gmail.com>
parent: John Fabiani <johnf@jfcomputer.com>
1 sibling, 1 reply; 10+ messages in thread
From: Richard Broersma @ 2011-11-15 16:33 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 00:28 John Fabiani <johnf@jfcomputer.com>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: John Fabiani @ 2011-11-16 00:28 UTC (permalink / raw)
To: pgsql-sql; +Cc: Richard Broersma <richard.broersma@gmail.com>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 00:33 Samuel Gendler <sgendler@ideasculptor.com>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 10+ messages in thread
From: Samuel Gendler @ 2011-11-16 00:33 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql@postgresql.org, Richard Broersma <richard.broersma@gmail.com>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 03:46 Scott Marlowe <scott.marlowe@gmail.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 3 replies; 10+ messages in thread
From: Scott Marlowe @ 2011-11-16 03:46 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: John Fabiani <johnf@jfcomputer.com>; pgsql-sql@postgresql.org, Richard Broersma <richard.broersma@gmail.com>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 03:49 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2011-11-16 03:49 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: John Fabiani <johnf@jfcomputer.com>; pgsql-sql@postgresql.org, Richard Broersma <richard.broersma@gmail.com>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 04:39 Sam Gendler <sgendler@ideasculptor.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Sam Gendler @ 2011-11-16 04:39 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: John Fabiani <johnf@jfcomputer.com>; pgsql-sql; RichardBroersma <richard.broersma@gmail.com>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 15:19 John Fabiani <johnf@jfcomputer.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: John Fabiani @ 2011-11-16 15:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: Scott Marlowe <scott.marlowe@gmail.com>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 15:20 John Fabiani <johnf@jfcomputer.com>
parent: John Fabiani <johnf@jfcomputer.com>
1 sibling, 0 replies; 10+ messages in thread
From: John Fabiani @ 2011-11-16 15:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: updating a sequence
@ 2011-11-16 18:49 Jasen Betts <jasen@xnet.co.nz>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Jasen Betts @ 2011-11-16 18:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-11-16 18:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-15 16:06 updating a sequence John Fabiani <johnf@jfcomputer.com>
2011-11-15 16:33 ` Richard Broersma <richard.broersma@gmail.com>
2011-11-16 00:28 ` John Fabiani <johnf@jfcomputer.com>
2011-11-16 00:33 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-11-16 03:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-11-16 03:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-11-16 04:39 ` Sam Gendler <sgendler@ideasculptor.com>
2011-11-16 15:19 ` John Fabiani <johnf@jfcomputer.com>
2011-11-16 18:49 ` Jasen Betts <jasen@xnet.co.nz>
2011-11-16 15:20 ` John Fabiani <johnf@jfcomputer.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox