pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Changeing Sequence
5+ messages / 4 participants
[nested] [flat]

* Changeing Sequence
@ 2002-03-26 19:55  Peter Atkins <Peter.Atkins@nextcard.com>
  0 siblings, 3 replies; 5+ messages in thread

From: Peter Atkins @ 2002-03-26 19:55 UTC (permalink / raw)
  To: pgsql-sql

All,

I have a table "mytable_id_seq" that looks like so.

    sequence_name     | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called
----------------------+------------+--------------+---------------------+---
--------+-------------+---------+-----------+-----------
 mytqble_id_seq |          4 |            1 | 9223372036854775807 |
1 |           1 |      30 | f         | t


I have data that must be entered into the table "mytable" with pre-defined
ids (i.e. 1,3,5,7,25). Is it possible to change the sequence to start at
"26" the last value of the previous data?

Please tell me YES! I get an error: "ERROR:  You can't change sequence
relation t_staff_staff_id_seq"

Any Help would be great!

Thanks,
-pete



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changeing Sequence
@ 2002-03-26 20:11  Dan Langille <dan@langille.org>
  parent: Peter Atkins <Peter.Atkins@nextcard.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Dan Langille @ 2002-03-26 20:11 UTC (permalink / raw)
  To: Peter Atkins <Peter.Atkins@nextcard.com>; +Cc: pgsql-sql

On 26 Mar 2002 at 11:55, Peter Atkins wrote:

> Please tell me YES! I get an error: "ERROR:  You can't change sequence
> relation t_staff_staff_id_seq"

Yes.  The above is merely a permission issue:

grant update on t_staff_staff_id_seq to X...

where X is you...  See the docs for more details on the grant command.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changeing Sequence
@ 2002-03-26 20:11  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Peter Atkins <Peter.Atkins@nextcard.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2002-03-26 20:11 UTC (permalink / raw)
  To: Peter Atkins <Peter.Atkins@nextcard.com>; +Cc: pgsql-sql

On Tue, 26 Mar 2002, Peter Atkins wrote:

> I have a table "mytable_id_seq" that looks like so.
>
>     sequence_name     | last_value | increment_by |      max_value      |
> min_value | cache_value | log_cnt | is_cycled | is_called
> ----------------------+------------+--------------+---------------------+---
> --------+-------------+---------+-----------+-----------
>  mytqble_id_seq |          4 |            1 | 9223372036854775807 |
> 1 |           1 |      30 | f         | t
>
>
> I have data that must be entered into the table "mytable" with pre-defined
> ids (i.e. 1,3,5,7,25). Is it possible to change the sequence to start at
> "26" the last value of the previous data?

select setval('mytqble_id_seq', 25);

should make the next result 26 I believe.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changeing Sequence
@ 2002-03-26 20:16  PG Explorer <pgmail@pgexplorer.com>
  parent: Peter Atkins <Peter.Atkins@nextcard.com>
  2 siblings, 0 replies; 5+ messages in thread

From: PG Explorer @ 2002-03-26 20:16 UTC (permalink / raw)
  To: Peter Atkins <Peter.Atkins@nextcard.com>; pgsql-sql

When you play around with the sequence you violate the meaning of the word
sequence
But in any case in ver 7.2 you can use
SELECT setval('mytqble_id_seq', 25);
and the next value of the sequence will be 26

in older versions you can
CREATE SEQUENCE 'mytqble_id_seq'  START 25;
or execute select nextval('mytqble_id_seq' ) 25 times

http://www.pgexplorer.com
Making PostgreSQL development a breeze



----- Original Message -----
From: "Peter Atkins" <Peter.Atkins@nextcard.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, March 26, 2002 9:55 PM
Subject: [SQL] Changeing Sequence


> All,
>
> I have a table "mytable_id_seq" that looks like so.
>
>     sequence_name     | last_value | increment_by |      max_value      |
> min_value | cache_value | log_cnt | is_cycled | is_called
> ----------------------+------------+--------------+---------------------+-
--
> --------+-------------+---------+-----------+-----------
>  mytqble_id_seq |          4 |            1 | 9223372036854775807 |
> 1 |           1 |      30 | f         | t
>
>
> I have data that must be entered into the table "mytable" with pre-defined
> ids (i.e. 1,3,5,7,25). Is it possible to change the sequence to start at
> "26" the last value of the previous data?
>
> Please tell me YES! I get an error: "ERROR:  You can't change sequence
> relation t_staff_staff_id_seq"
>
> Any Help would be great!
>
> Thanks,
> -pete
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changeing Sequence
@ 2002-03-26 20:32  PG Explorer <pgmail@pgexplorer.com>
  0 siblings, 0 replies; 5+ messages in thread

From: PG Explorer @ 2002-03-26 20:32 UTC (permalink / raw)
  To: Peter Atkins <Peter.Atkins@nextcard.com>; pgsql-sql

Why dont you insert the original values.
The FK should be intact if not you are already screwed.
After you have done migrating then you create the sequence with start max of
table.ids.
do an
ALTER TABLE table
    ALTER COLUMN  column  SET DEFAULT 'seq';

Whola

http://www.pgexplorer.com



----- Original Message -----
From: "Peter Atkins" <Peter.Atkins@nextcard.com>
To: "'PG Explorer'" <pgmail@pgexplorer.com>
Sent: Tuesday, March 26, 2002 10:26 PM
Subject: RE: [SQL] Changeing Sequence


> Understood!
>
> In this case it's a data integrity issue. Data in other tables(FK) rely on
> the ids being the same as in the previous database.
>
> Is there another way?
>
> -p
>
> -----Original Message-----
> From: PG Explorer [mailto:pgmail@pgexplorer.com]
> Sent: Tuesday, March 26, 2002 12:17 PM
> To: Peter Atkins; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Changeing Sequence
>
>
> When you play around with the sequence you violate the meaning of the word
> sequence
> But in any case in ver 7.2 you can use
> SELECT setval('mytqble_id_seq', 25);
> and the next value of the sequence will be 26
>
> in older versions you can
> CREATE SEQUENCE 'mytqble_id_seq'  START 25;
> or execute select nextval('mytqble_id_seq' ) 25 times
>
> http://www.pgexplorer.com
> Making PostgreSQL development a breeze
>
>
>
> ----- Original Message -----
> From: "Peter Atkins" <Peter.Atkins@nextcard.com>
> To: <pgsql-sql@postgresql.org>
> Sent: Tuesday, March 26, 2002 9:55 PM
> Subject: [SQL] Changeing Sequence
>
>
> > All,
> >
> > I have a table "mytable_id_seq" that looks like so.
> >
> >     sequence_name     | last_value | increment_by |      max_value
|
> > min_value | cache_value | log_cnt | is_cycled | is_called
>
> ----------------------+------------+--------------+---------------------+-
> --
> > --------+-------------+---------+-----------+-----------
> >  mytqble_id_seq |          4 |            1 | 9223372036854775807 |
> > 1 |           1 |      30 | f         | t
> >
> >
> > I have data that must be entered into the table "mytable" with
pre-defined
> > ids (i.e. 1,3,5,7,25). Is it possible to change the sequence to start at
> > "26" the last value of the previous data?
> >
> > Please tell me YES! I get an error: "ERROR:  You can't change sequence
> > relation t_staff_staff_id_seq"
> >
> > Any Help would be great!
> >
> > Thanks,
> > -pete
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-03-26 20:32 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-26 19:55 Changeing Sequence Peter Atkins <Peter.Atkins@nextcard.com>
2002-03-26 20:11 ` Dan Langille <dan@langille.org>
2002-03-26 20:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-26 20:16 ` PG Explorer <pgmail@pgexplorer.com>
2002-03-26 20:32 Re: Changeing Sequence PG Explorer <pgmail@pgexplorer.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