Drizzle Team•17mo ago

how to use insert with select in drizzle?

INSERT INTO your_table (column1, column2, column3, category_id)
SELECT column1, column2, column3, new_category_id
FROM your_table
WHERE category_id = old_category_id;

INSERT INTO your_table (column1, column2, column3, category_id)
SELECT column1, column2, column3, new_category_id
FROM your_table
WHERE category_id = old_category_id;

how to convert it into drizzle?

11 Replies

Andrii Sherman•17mo ago

Currently it's unavailable in drizzle. We have a simple logic for inserts for now But you can use sql

sql`INSERT INTO ${table} (${table.column1}, ${table.column2}, ${table.column3})
SELECT ${table.column1}, ${table.column2}, ${table.column3}
FROM ${table}
WHERE ${table.cat_id} = old_category_id;`

sql`INSERT INTO ${table} (${table.column1}, ${table.column2}, ${table.column3})
SELECT ${table.column1}, ${table.column2}, ${table.column3}
FROM ${table}
WHERE ${table.cat_id} = old_category_id;`

Md Jahidul Islam milonOP•17mo ago

also how to do Union or union all with two query in drizzle? https://discord.com/channels/1043890932593987624/1126421647289749545 is possible now in drizzle?

Andrii Sherman•17mo ago

same thing, Union was a last part in drizzle selects that we didn't implement yet We will fix slow intelisense first and then come back for such features Slow ts server is our priority right now

Md Jahidul Islam milonOP•17mo ago

that's great. it will be really helpful about ts server slowness. thank you for your reply 🙏

dandadan•12mo ago

any updates on this by any chance? was in need of something similar and was wondering if it has been implemented in the meanwhile, sql builder should work fine though

bonniss•12mo ago

sql magic operator like:

sql`INSERT INTO ${articleTags} (${articleTags.articleId}, ${articleTags.tagId})
SELECT ${upserted.id}, ${tags.id}
FROM ${tags}
WHERE ${tags.title} IN ${tagList};`

sql`INSERT INTO ${articleTags} (${articleTags.articleId}, ${articleTags.tagId})
SELECT ${upserted.id}, ${tags.id}
FROM ${tags}
WHERE ${tags.title} IN ${tagList};`

generates query like:

INSERT INTO "article_tags" ("article_tags"."article_id", "article_tags"."tag_id")
SELECT $1, "tags"."id"
FROM "tags"
WHERE "tags"."title" IN ($2, $3);
     -- params: [8, "tag1", "tag2"]

INSERT INTO "article_tags" ("article_tags"."article_id", "article_tags"."tag_id")
SELECT $1, "tags"."id"
FROM "tags"
WHERE "tags"."title" IN ($2, $3);
     -- params: [8, "tag1", "tag2"]

which is invalid:

PostgresError: column "article_tags" of relation "article_tags" does not exist

PostgresError: column "article_tags" of relation "article_tags" does not exist

How to remove table prefix per field in table call?

dandadan•12mo ago

you can just write the names of the columns directly, i havent found another way although in your case you could probably just use db.insert() since the query is straight forward (unless the query you provided is just an example and in reality yours is much more complex)

bonniss•12mo ago

wydm at "just use db.insert()"? select first then insert I guess?

dandadan•12mo ago

nvm, you cant you should either use sql with the embedded column names, or a transaction

Angelelz•12mo ago

There is an open PR for this feature. It's in the works https://github.com/drizzle-team/drizzle-orm/pull/1605

GitHub

Add support for insert into ... select by L-Mario564 · Pull Reque...

Addresses #398. This PR adds support to insert rows from one table into another via the insert into ... select syntax. The following is valid Drizzle syntax: const users = pgTable('users', ...

bonniss•12mo ago

While waiting for new API in the PR, use sql.raw keep the field name as-is to make it work:

sql`INSERT INTO ${articleTags} (${sql.raw(articleTags.articleId.name)}, ${sql.raw(articleTags.tagId.name)})
SELECT ${upserted.id}, ${tags.id}
FROM ${tags}
WHERE ${tags.title} IN ${tagList};`

sql`INSERT INTO ${articleTags} (${sql.raw(articleTags.articleId.name)}, ${sql.raw(articleTags.tagId.name)})
SELECT ${upserted.id}, ${tags.id}
FROM ${tags}
WHERE ${tags.title} IN ${tagList};`

Gaming

Programming

how to use insert with select in drizzle?