How to execute SQL SELECT query in migrations

Questions / Help
, ,
1

I am using Triplex package for multi-tenants. when i create a tenant. its execute all migrations. after table creation when i execute seed migrations. it gives me error "{:error, "ERROR 42P01 (undefined_table) relation \"t2.tenants\" does not exist\n\n query: (SELECT MAX(id) from \"t2.tenants\")"} on select query. if remove select query, insert queries execute successfully.

defmodule TudoChat.Repo.Migrations.SeedTableTenants do
  use Ecto.Migration
  
  def up do
    execute("INSERT INTO t2.tenants (\"id\", \"inserted_at\", \"name\", \"status\", \"updated_at\") values ('1', '2019-07-08 10:37:28', 'tudo_', 'active', '2019-07-08 10:37:30')")
    execute("INSERT INTO t2.tenants (\"id\", \"inserted_at\", \"name\", \"status\", \"updated_at\") values ('2', '2019-07-08 10:38:19', 'test_', 'active', '2019-07-08 10:38:22')")
    execute("(SELECT MAX(id) from \"t2.tenants\")")
  end

  def down do
  end
end

please help me how to execute select query?

2

I think the issue is that you are quoting t2.tenants in the SELECT statement. I think it should be:

execute("SELECT MAX(id) from t2.tenants")
3 Likes
3

no, it’s correct. already tried.

4

I’ll respond here too, on top of slack. Also I’m assuming postgres.

@kip is correct, "t2.tenants" is not the same as t2.tenants. However if you really want to quote it, you have to quote the schema and the table separately, "t2"."tenants".

You can test it easily in psql.

create table asd ();
select * from asd;
select * from "asd";
select * from public.asd;
select * from "public"."asd";
select * from "public.asd";

CREATE TABLE
SELECT 0
SELECT 0
SELECT 0
SELECT 0
ERROR:  relation "public.asd" does not exist
LINE 5: select * from "public.asd";
4 Likes
5

Why do you wrap SELECT query in parens? Also I believe that @kip is right, and "t2.tenants" mean select table default."t2.tenants" where you wanted to select from t2.tenants which would be "t2"."tenants".

3 Likes
6

Thank you jola. you are right. have seen your answer on slack as well.

7

thank you.