0

I'm trying to alter a table as to add another newly created column being part of the already existing primary key (that includes multiple columns) and this newly created column (id) should auto-increment and also have a default value. Basically, any query inserting data in the table should be able to ignore id in the query and I'd like the first row to have an id of 1.

Here's the script creating the table initially

<createTable tableName="thetable">
            <column name="col1" type="SMALLINT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_thetable"/>
            </column>
            <column name="col2" type="SMALLINT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_thetable"/>
            </column>
            <column name="col3" type="SMALLINT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_thetable"/>
            </column>
        </createTable>

And here's the script trying to add a new column the the PK.

<dropPrimaryKey  tableName="thetable" constraintName="pk_thetable"/>

<addColumn tableName="thetable">
    <column name="id" type="SMALLINT" autoIncrement="true" startWith="1">
        <constraints nullable="true" />
    </column>
</addColumn>

<addPrimaryKey tableName="thetable" columnNames="col1,col2,col3,id" 
         constraintName="pk_thetable"/>

The script runs fine, but in the end, INSERT's have to provide the id when inserting data in the table. Is this even possible in PGSQL terms or it's my script that needs tweaking ?

I've tried both with a nullable or non-nullable id already.

Francis Ducharme
  • 4,848
  • 6
  • 43
  • 81
  • Take a look at this [Question/Answer](https://stackoverflow.com/a/76893784/3422102) A similar approach in your case may satisfy the `id` constraint as well. – David C. Rankin Aug 14 '23 at 16:38

0 Answers0