# Using postgres sequences on id columns

**URL:** <https://www.ruby-forum.com/t/using-postgres-sequences-on-id-columns/47507>\
**Category:** Rails\
**Created:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/using-postgres-sequences-on-id-columns/47507 "2005-11-12T12:49:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ljw1001](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/eb9ed0/32.png) [@ljw1001](https://www.ruby-forum.com/u/ljw1001)\
**Post date:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/using-postgres-sequences-on-id-columns/47507/1 "2005-11-12T12:49:14Z")

</div>

I just switched from using a column type of SERIAL to trying to use a  
Postgres Sequence defined as:

CREATE SEQUENCE unique\_sequence START 101;

The column definition went from “id SERIAL NOT NULL” to

"id INTEGER NOT NULL DEFAULT nextval(‘unique\_sequence’), "

When i insert outside of rails, everything works fine. Inside rails,  
however, I get this error message:

ERROR C42P01 Mrelation “project\_id\_seq” does not  
exist Fnamespace.c L201 RRangeVarGetRelid

My question is: Do you have to use Serial types on id columns? I would  
prefer to use a sequence so i can ensure that the ids of rows in  
several tables are entirely unique.

thanks.

---

<div class="post-metadata">

**Author:** ![ljw1001](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/eb9ed0/32.png) [@ljw1001](https://www.ruby-forum.com/u/ljw1001)\
**Post date:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/using-postgres-sequences-on-id-columns/47507/2 "2005-11-12T12:49:14Z")

</div>

SERIAL is just an alias for ‘integer no null default  
nextval(‘sequence’)…’ Looks like Rails is looking  
for a sequence named project\_id\_seq yet you created  
one named unique\_sequence.

CSN

— Larry W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> nextval(‘unique\_sequence’), "  
> prefer to use a sequence so i can ensure that the  
> ids of rows in  
> several tables are entirely unique.
> 
> thanks.
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

* * *

Yahoo! FareChase: Search multiple travel sites in one click.  
[http://farechase.yahoo.com](http://farechase.yahoo.com)

---

<div class="post-metadata">

**Author:** ![ljw1001](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/eb9ed0/32.png) [@ljw1001](https://www.ruby-forum.com/u/ljw1001)\
**Post date:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/using-postgres-sequences-on-id-columns/47507/3 "2005-11-12T12:49:17Z")

</div>

Blair Z. wrote:

> Right, you need the standard sequence name of  
> “#{table\_name}\_#{primary\_key\_column\_name}\_seq”, unless you use AR’s  
> set\_sequence\_name method to name the sequence.
> 
> But you will still run into a problem with these explicitly named  
> sequences in Postgresql that RoR 0.14.2 doesn’t handle:
> 
> [Peak Obsession](http://dev.rubyonrails.com/ticket/2594)

Well I have a standard name for my sequence but it doesn’t work either.

?\> Player.primary\_key =\> “id”

> > Player.connection.default\_sequence\_name “players” =\>  
> > “players\_id\_seq” \>\> Player.connection.pk\_and\_sequence\_for “players” =\>  
> > nil \>\>

table definition:

```
                                  Table "public.players"
   Column | Type | 

```

Modifiers  
------------------±-----------------------±---------------------------------------------------  
id | integer | not null default  
nextval(‘“players\_id\_seq”’::text)  
positie | integer |  
active | boolean | not null default false  
perm\_level | smallint | not null default 1  
email | character varying(150) | not null  
fname | character varying(60) | not null  
lname | character varying(80) | not null

Jeroen

---

<div class="post-metadata">

**Author:** ![ljw1001](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/eb9ed0/32.png) [@ljw1001](https://www.ruby-forum.com/u/ljw1001)\
**Post date:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/using-postgres-sequences-on-id-columns/47507/4 "2005-11-12T12:49:15Z")

</div>

Right, you need the standard sequence name of  
“#{table\_name}\_#{primary\_key\_column\_name}\_seq”, unless you use AR’s  
set\_sequence\_name method to name the sequence.

But you will still run into a problem with these explicitly named  
sequences in  
Postgresql that RoR 0.14.2 doesn’t handle:

[http://dev.rubyonrails.com/ticket/2594](http://dev.rubyonrails.com/ticket/2594)

Regards,  
Blair