What is the return type for a field for a query in Postgres with 0 rows?

Just_CODING 0
Viewed 25

I have a table accounts in Postgres which had id and name as fields. id is the primary key. I want to fetch the last primary key inserted into the table using this query :-

select id from accounts order by id desc limit 1 ;

Using a preparedStatement I run this query and get the value for my next primary key by incrementing it by 1.

String query="SELECT "+columnName+" FROM "+TableName+" ORDER BY "+columnName+" DESC limit 1";
PreparedStatement stmt=conn.prepareStatement(query);
ResultSet rs= stmt.executeQuery();
int counter= rs.getInt(1);

My question is if there are 0 rows, what will be the counter value?

If the value is null which i don't think can be stored in int, how do I assign it a starting primary int key value say 10 ?

jdbc java postgresql
1 Answers

You shouldn't be doing that, so here's what I believe is the best answer.

Instead, define your id as auto-incrementing which postgres manages:

create table account (
  id serial not null primary key,
  ...
)

Then execute an insert:

insert into account (col1, col2, ...) values (..., ...) -- omit id

And the new account will get the next number, which you can get from the result returned from the insert, or in postgres you can use a regular query to get it

insert into account (col1, col2, ...) values (..., ...) -- omit id
returning id

Note the change in name from accounts to account - naming tables are a singular noun is best practice and makes sense: accounts.surname is nonsense, but account.surname is how we speak and think.

Bohemian0
Related