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.