Using Postgresql 'nextval' function

[criegod]

Almost 8 years ago, there was this:
https://stackoverflow.com/questions/39589899/how-to-use-postgres-sequences-with-sequel
but more recently:
https://www.rubydoc.info/gems/sequel-sequence

The examples in the latter all relate to migrations (which i'm not doing). I was able to put a call to: 'DB.create_sequence :id' into my model (which is probably wrong) and a 'Sequence "public.id" was created (as a Postgres 'bigint'). I already have 'primary_key :id' in the model, but, after running, the two are (unsurprisingly) not related. I want my 'id' field to be a sequence and the primary key, but don't know how to accomplish this. Also, creating a new record (Album.create... using DataMapper) automatically incremented the :id field, but i'm thinking this might not happen with Sequel and i'll have to call DB.nextval("id")... somewhere. But to have this happen automagically when i create a new record...? As always, any help is greatly appreciated.

[jeremyevans]

In psql, can you post the output of \d table_name? That should show what the current default value is for the column. If it isn't already set to use a sequence, you probably just need to run an appropriate alter_table/set_column_default command. Which command to run will depend on the output.

[criegod]

My model has 'primary_key :id' as a line in it, so psql shows this type as 'integer' and the default value as 'generated by default as identity', which i searched for to see what that means but came up with nothing. The example in the link i posted:

https://www.rubydoc.info/gems/sequel-sequence#usage-with-postgresql-and-mariadb

calls create_sequence (outside of the create_table definition):

create_sequence :position_id, if_exists: false, start: 1000

The relevant part of the table definition is:

create_table(:things) do
primary_key :id
# PostgreSQL uses bigint as the sequence's default type.
Bignum :position
end

This sequence isn't used directly, though, but here, in this call, outside of the table definition:

set_column_default_nextval :things, :position, :position_id

So, apparently the field 'position' is now an auto-incrementing (?) sequence, but has nothing to do with the primary key. I want my primary key to be an auto-incrementing sequence. In the table that DataMapper created (from the line: 'property :id, Serial'), psql shows the type of 'id' as 'integer' and the default value as 'nextval('albums_id_seq'::regclass)' and it shows 'albums_id_seq' as a Sequence of type 'integer', with start, min, max, etc. values. What i want seems fairly basic but i haven't been able to find any good examples to guide me.

[criegod]

I thought i'd try this:

'DB = Sequel.connect( ...

DB.create_sequence :sid, if_exists: false

DB.create_table :new_artists do
primary_key :id
String :name, :null => false
end

DB.create_table :new_albums do
primary_key :id
foreign_key :artist_id, :new_artists, :null => false
varchar :name, :null => false
varchar :path
Time :bdate
Integer :year
end

DB.set_column_default_nextval :new_albums, :id, :sid
'
But this gives the error:

....local/share/gem/ruby/3.2.0/gems/sequel-5.84.0/lib/sequel/adapters/postgres.rb:171:in `exec': PG::SyntaxError: ERROR: column "id" of relation "new_albums" is an identity column (Sequel::DatabaseError)

A search for 'ruby sequel identity column' comes up with nothing relevant.

[jeremyevans]

The id column is already an identity column, which is an autoincrementing column. You don't need to manually create a sequence, PostgreSQL will create a sequence internally and manage it. So you don't need to create a sequence or set a column default manually.