I have a specific need to ''fixate'' my
''roles'' table sequence when
migrating.
at migration #4, I have 28 records in my roles tables and I can manually
reset the roles_id_seq but it stands to reason that since I am doing
everything else in the migration, I might as well reset the sequence
there too.
so I tried this in my migration file...
execute ''SELECT setval ( "roles_id_seq", 0 );''
and it crashed with...
-- execute("SELECT setval ( \"roles_id_seq\", 0 );")
rake aborted!
PGError: ERROR: column "roles_id_seq" does not exist
: SELECT setval ( "roles_id_seq", 0 );
How do I accomplish this inside of a migration?
Craig
At a guess, what you need to do is (assuming you''ve got a controller
named Foo) something like:
Foo.find_by_sql(''SELECT setval ("roles_id_seq",
0);'')
Note that find_by_sql lets you execute arbitrary SQL statements.
Regards
Dave M.
On 15/06/06, Craig White <craigwhite@azapple.com>
wrote:> I have a specific need to ''fixate'' my
''roles'' table sequence when
> migrating.
>
> at migration #4, I have 28 records in my roles tables and I can manually
> reset the roles_id_seq but it stands to reason that since I am doing
> everything else in the migration, I might as well reset the sequence
> there too.
>
> so I tried this in my migration file...
>
> execute ''SELECT setval ( "roles_id_seq", 0
);''
>
> and it crashed with...
>
> -- execute("SELECT setval ( \"roles_id_seq\", 0 );")
> rake aborted!
> PGError: ERROR: column "roles_id_seq" does not exist
> : SELECT setval ( "roles_id_seq", 0 );
>
> How do I accomplish this inside of a migration?
>
> Craig
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
seemed possible but the result is still the same... PGError: ERROR: column "roles_id_seq" does not exist : SELECT setval ( "roles_id_seq", 29 ); and of course there is no column ''roles_id_seq'' as it is the postgresql sequence for automatically numbering additions to table. Is there a way to set this within a migration statement? Craig On Thu, 2006-06-15 at 15:38 +1000, David Mitchell wrote:> At a guess, what you need to do is (assuming you''ve got a controller > named Foo) something like: > > Foo.find_by_sql(''SELECT setval ("roles_id_seq", 0);'') > > Note that find_by_sql lets you execute arbitrary SQL statements. > > Regards > > Dave M. > > On 15/06/06, Craig White <craigwhite@azapple.com> wrote: > > I have a specific need to ''fixate'' my ''roles'' table sequence when > > migrating. > > > > at migration #4, I have 28 records in my roles tables and I can manually > > reset the roles_id_seq but it stands to reason that since I am doing > > everything else in the migration, I might as well reset the sequence > > there too. > > > > so I tried this in my migration file... > > > > execute ''SELECT setval ( "roles_id_seq", 0 );'' > > > > and it crashed with... > > > > -- execute("SELECT setval ( \"roles_id_seq\", 0 );") > > rake aborted! > > PGError: ERROR: column "roles_id_seq" does not exist > > : SELECT setval ( "roles_id_seq", 0 ); > > > > How do I accomplish this inside of a migration? > > > > Craig > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Posting solution for anyone that follows in my footsteps...this was big.
execute ''ALTER SEQUENCE roles_id_seq RESTART WITH 29;''
migration statement for postgresql to re-set sequence
Craig
On Thu, 2006-06-15 at 08:44 -0700, Craig White wrote:> seemed possible but the result is still the same...
>
> PGError: ERROR: column "roles_id_seq" does not exist
> : SELECT setval ( "roles_id_seq", 29 );
>
> and of course there is no column ''roles_id_seq'' as it is
the postgresql
> sequence for automatically numbering additions to table.
>
> Is there a way to set this within a migration statement?
>
> Craig
>
> On Thu, 2006-06-15 at 15:38 +1000, David Mitchell wrote:
> > At a guess, what you need to do is (assuming you''ve got a
controller
> > named Foo) something like:
> >
> > Foo.find_by_sql(''SELECT setval ("roles_id_seq",
0);'')
> >
> > Note that find_by_sql lets you execute arbitrary SQL statements.
> >
> > Regards
> >
> > Dave M.
> >
> > On 15/06/06, Craig White <craigwhite@azapple.com> wrote:
> > > I have a specific need to ''fixate'' my
''roles'' table sequence when
> > > migrating.
> > >
> > > at migration #4, I have 28 records in my roles tables and I can
manually
> > > reset the roles_id_seq but it stands to reason that since I am
doing
> > > everything else in the migration, I might as well reset the
sequence
> > > there too.
> > >
> > > so I tried this in my migration file...
> > >
> > > execute ''SELECT setval ( "roles_id_seq", 0
);''
> > >
> > > and it crashed with...
> > >
> > > -- execute("SELECT setval ( \"roles_id_seq\", 0
);")
> > > rake aborted!
> > > PGError: ERROR: column "roles_id_seq" does not exist
> > > : SELECT setval ( "roles_id_seq", 0 );
> > >
> > > How do I accomplish this inside of a migration?
> > >
> > > Craig
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
Craig White wrote:> seemed possible but the result is still the same... > > PGError: ERROR: column "roles_id_seq" does not exist > : SELECT setval ( "roles_id_seq", 29 ); > > and of course there is no column ''roles_id_seq'' as it is the postgresql > sequence for automatically numbering additions to table. > > Is there a way to set this within a migration statement? > > CraigWhich version of PGSQL are you using? You might want to try variations of SELECT setval(''roles_id_seq''::text, 29) I know the documentation always uses single quotes not double, and older versions liked the explicit cast to text, newer versions (8.1.x) should work without the case, I think. Eric -- Posted via http://www.ruby-forum.com/.
On Thu, 2006-06-15 at 17:58 +0200, Eric D. Nielsen wrote:> Craig White wrote: > > seemed possible but the result is still the same... > > > > PGError: ERROR: column "roles_id_seq" does not exist > > : SELECT setval ( "roles_id_seq", 29 ); > > > > and of course there is no column ''roles_id_seq'' as it is the postgresql > > sequence for automatically numbering additions to table. > > > > Is there a way to set this within a migration statement? > > > > Craig > > Which version of PGSQL are you using? You might want to try > variations of > SELECT setval(''roles_id_seq''::text, 29) > > I know the documentation always uses single quotes not double, and older > versions liked the explicit cast to text, newer versions (8.1.x) should > work without the case, I think.---- I''m on RHEL so it''s PostgreSQL 7.4.8-1 (yeah, I know I could upgrade and maybe will upgrade for autovacuum and stuff but production usage is light and development usage is heavy) It actually works as stated and that makes me happy...I probably need to update my PostgreSQL book though because I think it pre-dates 7.4 and I think it set me off with the wrong terminology...it wasn''t until I examined the terminology used by pgadmin3 that I found a better way. Thanks Craig
There is a method in the Postgresql adapter called reset_pk_sequence. I haven''t used it yet but it sounds like this is what you are looking for. -- Posted via http://www.ruby-forum.com/.
Lance wrote in post #93083:> There is a method in the Postgresql adapter called reset_pk_sequence. I > haven''t used it yet but it sounds like this is what you are looking for.I used it: Account.delete_all Account.reset_pk_sequence but, it didn''t work. The next Account.save had the :id = last :id + 1 At least it didn''t crash :-) I''m still looking for a solution. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit https://groups.google.com/groups/opt_out.
Bob Gustafson wrote in post #1092090:> Lance wrote in post #93083: >> There is a method in the Postgresql adapter called reset_pk_sequence. I >> haven''t used it yet but it sounds like this is what you are looking for. > > I used it: > > Account.delete_all > Account.reset_pk_sequence > > but, it didn''t work. The next Account.save had the :id = last :id + 1 > > At least it didn''t crash :-) > > I''m still looking for a solution.Here is the solution: Account.delete_all Account.connection.execute "ALTER SEQUENCE accounts_id_seq RESTART WITH 1" Hope this helps Bob G -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit https://groups.google.com/groups/opt_out.