Rails - copy a Table from one schema to another schema in same database

Question

Following is my setups:

Ruby version: 3.0.1

Rails Version: 6.1.3.2

postgres (PostgreSQL) 9.6.17

I am using gem ros-apartment (2.9.0) to manage tenants. This is what I have in my apartment.rb

  config.tenant_names = -> { Tenant.pluck :app_name }

Problem statement: I have a set of data across tables in the admin tenant. The schema name for the admin tenant is pq-admin. Now whenever a new tenant is created, I need to take all data from selected tables from pq-admin schema and then put in the newly created tenant with schema (say test-tenant1).

To achieve this, the following is what I have written at the tenant.rb

 class Tenant < ApplicationRecord

  after_create :create_apartment_tenant, :populate_data
  after_destroy :delete_apartment_tenant

  private

  def create_apartment_tenant
    Apartment::Tenant.create(app_name)
  end

  def delete_apartment_tenant
    Apartment::Tenant.drop(app_name)
  end

  def populate_data
    %w[
     pc_core_packages
     pc_core_preorders
     pc_core_delivery_times
     pc_core_dynamic_columns
     pc_core_product_items
     pc_core_dynamic_options
     pc_core_dynamic_values
     pc_core_specifications
     pc_core_images
     pc_core_prices
     pc_core_product_trees
     pc_core_read_models_product_trees
     pc_core_read_models_product_items
].each do |table|
      query = "INSERT INTO #{app_name}.#{table} SELECT * FROM  #{ENV['ADMIN_TENANT']}.#{table};"
      ActiveRecord::Base.connection.exec_query(query)
    
    # ENV['ADMIN_TENANT'] = 'pq-admin'
    # the generated query =  "INSERT INTO test-tenant1.pc_core_packages SELECT * FROM  pq-admin.pc_core_packages;"
   
    end
  end
end

The Following is the error I encountered. Need help to resolve above described problem.

SQL (0.6ms)  INSERT INTO test-tenant1.pc_core_packages SELECT * FROM  pq-admin.pc_core_packages;
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "-"
LINE 1: INSERT INTO test-tenant1.pc_core_packages SELECT * FROM  pq-...
                        ^

from /Users/bhagawatadhikari/.rvm/gems/ruby-3.0.1/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params'
Caused by PG::SyntaxError: ERROR:  syntax error at or near "-"
LINE 1: INSERT INTO test-tenant1.pc_core_packages SELECT * FROM  pq-...
                        ^

from /Users/bhagawatadhikari/.rvm/gems/ruby-3.0.1/gems/activerecord-6.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params'

Answer 1

Need to delimit the identifier when including a dash (hyphen) in a schema or table name. Updating your query to...

"INSERT INTO \"#{app_name}.#{table}" SELECT * FROM \"#{ENV['ADMIN_TENANT']}.#{table}\";"

Should do the trick.

See... what's the escape sequence for hyphen (-) in PostgreSQL

Though it might be a better practice to just exclude the hyphen from names to begin with.