Shows how to get generated key from Postgres with sql2o

postgreSQLTest.java

Sql2o sql2o = new Sql2o(url, user, pass);

// Get generated key from SERIAL column
@Test
public void testKeyKeyOnSerial() {
    
    String createTableSql = "create table test_serial_table (id serial primary key, val varchar(20))";  
    sql2o.createQuery(createTableSql).executeUpdate();                                             
                                                                                                    
    String insertSql = "insert into test_serial_table(val) values ('something')";                       
    Long key = sql2o.createQuery(insertSql, true).executeUpdate().getKey(Long.class);              
                                                                                                    
    assertThat(key, equalTo(1L));                                                                       
                                                                                                    
    key = sql2o.createQuery(insertSql, true).executeUpdate().getKey(Long.class);                   
    assertThat(key, equalTo(2L));                                                                       
}

// Get generated key from integer column with inserted sequence value
@Test
public void testGetKeyOnSequence(){
    String createSequenceSql = "create sequence testseq";                                               
    connection.createQuery(createSequenceSql).executeUpdate();                                          
                                                                                                        
    String createTableSql = "create table test_seq_table (id integer primary key, val varchar(20))";    
    connection.createQuery(createTableSql).executeUpdate();                                             
                                                                                                        
    String insertSql = "insert into test_seq_table(id, val) values (nextval('testseq'), 'something')";  
    Long key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);              
                                                                                                        
    assertThat(key, equalTo(1L));                                                                       
                                                                                                        
    key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);                   
    assertThat(key, equalTo(2L));                                                                       
}