Migration file: Default value for timestamp column

1

In my Hanami 2.2 app I have a migration file having following code:

# frozen_string_literal: true

ROM::SQL.migration do
  # Add your migration here.
  #
  # See https://guides.hanamirb.org/v2.2/database/migrations/ for details.
  change do
    create_table :orders do
      primary_key :id

      foreign_key :item_id, :items, null: false, default: 0

      column :quantity, :integer, null: false, default: 1

      column :created_at, :timestamp, null: false
      column :updated_at, :timestamp, null: false
    end
  end
end

I want to set default value as dynamic current-time for timestamp columns. So I used default: 'now()' for the timestamp column.

But with that in DB table structure default value found set was like following '2025-07-05 19:23:49.730244'::timestamp without time zone which is unintended.

Following are the additional findings:

  • When using default: 'CURRENT_TIMESTAMP' and running the migration getting following error:
Caused by:
PG::InvalidDatetimeFormat: ERROR:  invalid input syntax for type timestamp: "CURRENT_TIMESTAMP" (PG::InvalidDatetimeFormat)
LINE 1: ...EFAULT 1 NOT NULL, "created_at" timestamp DEFAULT 'CURRENT_T...
                                                             ^
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:171:in 'PG::Connection#exec'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:171:in 'block in Sequel::Postgres::Adapter#execute_query'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/extensions/error_sql.rb:63:in 'Sequel::ErrorSQL#log_connection_yield'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:171:in 'Sequel::Postgres::Adapter#execute_query'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:159:in 'block in Sequel::Postgres::Adapter#execute'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:136:in 'Sequel::Postgres::Adapter#check_disconnect_errors'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:159:in 'Sequel::Postgres::Adapter#execute'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:532:in 'Sequel::Postgres::Database#_execute'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:348:in 'block (2 levels) in Sequel::Postgres::Database#execute'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:555:in 'Sequel::Postgres::Database#check_database_errors'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:348:in 'block in Sequel::Postgres::Database#execute'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/connection_pool/timed_queue.rb:85:in 'Sequel::TimedQueueConnectionPool#hold'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/database/connecting.rb:283:in 'Sequel::Database#synchronize'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/postgres.rb:348:in 'Sequel::Postgres::Database#execute'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/database/query.rb:50:in 'Sequel::Database#execute_dui'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/database/query.rb:43:in 'Sequel::Database#execute_ddl'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/database/schema_methods.rb:710:in 'Sequel::Database#create_table_from_generator'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/database/schema_methods.rb:211:in 'Sequel::Database#create_table'
/home/jignesh/.rvm/gems/ruby-3.4.4@my_app/gems/sequel-5.93.0/lib/sequel/adapters/shared/postgres.rb:468:in 'Sequel::Postgres::DatabaseMethods#create_table'
/..../my_app/config/db/migrate/20250627002000_create_orders.rb:6:in 'block (2 levels) in <top (required)>'
  • When using default: Sequel::CURRENT_TIMESTAMP and running the migration, in DB table structure default value found set is CURRENT_TIMESTAMP which is correct.

Can anybody please explain what difference using Sequel::CURRENT_TIMESTAMP is making compared to using the string literal CURRENT_TIMESTAMP?

Note:

Found about the Sequel constant from Default Sequel::CURRENT_TIMESTAMP vs Safe Prepared Statements · Issue #1168 · jeremyevans/sequel · GitHub; that post I found
while searching for the error “PG::InvalidDatetimeFormat…” I mentioned before.

Thanks.

2

The string literal doesn’t work because CURRENT_TIMESTAMP is not a string literal, it is a function call. So this generates an invalid SQL statement.

If you inspect Sequel::CURRENT_TIMESTAMP, you will see that it is an instance of Sequel::SQL::Constant.

An alternate way to declare a function call in a migration:

column :created_at, :timestamp, null: false, default: Sequel.lit("CURRENT_TIMESTAMP")

Using a SQL literal inserts the string as-is into the query, which will also be interpreted correctly by the database. Essentially, you’re running afoul of the protection against SQL-injection that ensures regular string types are always correctly quoted.