Getting column size with JDBC in SQLite

Question

So I was wondering is there anyway I could retrieve column size in JDBC such as if the column type is varchar(5) it would return 5. I did some research and found out that SQLite ignore the size but I'm still dying to know is there anyway I could achieve this. I tried ResultSet.getInt("COLUMN_SIZE") and also Databasemetadata.getColumnDisplaySize() but it still doesn't work.

Answer 1

You could use the length function. However, that would be on a per row basis, as there no concept of a single column size; as each column per row has a specific size according to the value stored in the unique column/row combination.

• note that each column irrespective of it's declared type affinity can store any type of value in any type of column (except for the rowid column and therefore an alias thereof) and that the column type affinity itself can be virtually anything with some limitations. This is more comprehensively explained in How flexible/restricive are SQLite column types?

e.g.

SELECT length(mycolumn) AS column_size FROM mytable;  

You could use the following to get the maximum length used. This utilises the max function :-

SELECT max(length(mycolumn)) AS max_column_size FROM mytable;

• this would return 1 row i.e as the max function with just 1 parameter is an aggregate function and that the GROUP BY is effectively all rows, then there is effectively 1 GROUP, and thus 1 result is returned.
• you may find following the provided links useful e.g. avg may be useful.