10

I am currently playing around with Liquibase and have created a table for users and a table for roles. Each user has a unique id which is auto generated (by a auto increment sequence) when a user is inserted. The roles in the role table are then referencing this id.

Now I try to insert a default user using the Liquibase. First I insert the user and then I want to insert a role which is referring to this newly created user.

The problem is that I can't find a way in Liquibase to retrieve which id was generated for the new user, so that I can use it when inserting the new role.

Is this possible? If it is, how should it be done?

Want to do something like following:

<insert tableName="xxx_user">
    <!-- Id is auto incremented -->
    <column name="user_name" value="XXX" />
    <column name="password" value="XXX" />
    <column name="first_name" value="XXX" />
    <column name="last_name" value="XXX" />
</insert>

<insert tableName="user_role">
    <column name="user_id" value="<point at the auto generated id in previous insert>" />
    <column name="role" value="A_ROLE" />
</insert>
eternitysharp
  • 479
  • 5
  • 16
  • Possible duplicate of [Postgresql function for last inserted id](http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id) – Lukasz Szozda Nov 02 '15 at 09:07
  • Please show us your Liquibase change set, but you most probably are looking for `lastval()` or `currval()` - or you can do it with just a single data modifying CTE. –  Nov 02 '15 at 09:09
  • This can be easily done with pure vendor specific SQL. But I try to do it in Liquibase where it is done by using XML and is not specific to any vendor. – eternitysharp Nov 02 '15 at 09:11
  • Have added an Liquibase example of what I want to do – eternitysharp Nov 02 '15 at 09:22
  • 1
    You could try `valueComputed="lastval()"` but that would be vendor specific again. I don't think Liquibase has any support for this kind of things. Unrelated, but: do you _really_ have table named `user` - that is a bad idea because `user` is a reserved word and [requires to be quoted](http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) each time you use it. –  Nov 02 '15 at 09:54
  • Thx, will try it out. The table and column names are actually different in my code :) – eternitysharp Nov 02 '15 at 09:56

1 Answers1

12

I use Mysql DB. It is not an elegant solution but works...

    <column name="user_id" valueComputed="(SELECT MAX(id) FROM xxx_user)"/>
Juan Miguel Gomez
  • 141
  • 1
  • 5
  • 1
    Welcome to SO! Please see [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) – akshat May 28 '18 at 08:11
  • 3
    I think the downvotes are undeserved here. This is the only successful method of retrieving an ID that I have found so far. As the author noted, it is not an elegant solution but it does bridge the gap of finding the auto-generated ID. – Gaʀʀʏ Jul 03 '18 at 18:41