DB Browser for SQLite

Question

I have installed DB Browser for SQLite on my mac. I need to create a SQLITE database or use an existing database and create a table in the database called "Ages":

CREATE TABLE Ages ( 
  name VARCHAR(128), 
  age INTEGER
)

So far I managed to create a table that looks like this:

CREATE TABLE Ages ( 
  name VARCHAR(128)INTEGER, 
  age INTEGER
)

How do I get rid of the first "INTEGER", so my table looks like the one intended please?

Answer 1

You code use the Execute SQL tab and then use:-

ALTER TABLE Ages RENAME TO Ages_old;
DROP TABLE IF EXISTS Ages;
CREATE TABLE IF NOT EXISTS Ages (name VARCHAR(128), age INTEGER);
INSERT INTO Ages SELECT * FROM Ages_old;

This would leave the Ages_old table (which you may want to DROP). It would also cope with the Ages table containing actual data (the INSERT SQL copies the data from the renamed original table to the newly created table).

However, there is no real need as the columns could contain any type of data, this being a flexibility feature of SQLite (albeit confusing to some).

In fact as the column type varchar(128)integer contains int the column type affinity will be INTEGER anyway (again not that it really matters).

Perhaps consider this demo (executed using DBBrowser):-

DROP TABLE IF EXISTS `?`;
CREATE TABLE IF NOT EXISTS `?` (x somereallystupidcolumntype,y INTEGER,z `VARCHAR(128)INTEGER`);
SELECT * FROM sqlite_master;
INSERT INTO `?` VALUES
    (x'1234567890abcdef',x'1234567890abcdef',x'1234567890abcdef'),(100,100,100),(100.1234,100.1234,100.1234),('whatever','whatever','whatever'),(100||'abc'||100.1234,null,null);
SELECT *,typeof(x),typeof(y),typeof(z) FROM `?`;

• table name is funny/weird but is to all intents and purposes ? (enclosed to allow an otherwise unacceptable name)

• col x is, as the type if read implies, a weird/stupid.unconventional type

• col y is an INTEGER type

• col z is very close to the type you appear to have (enclosed to allow it)

• the INSERT SQL inserts data for all 3 columns for 5 rows

• the SELECT SQL extracts the data asis (i.e. all 3 columns) BUT adds another 3 columns that are the column type of the data for the row for the 3 columns.

When run then DBBrowser shows:-

• the first row has BLOB values (SQLite tools have their way of displaying BLOBs some allow customisation) and the type of each column is BLOB irrespective of the column type used to define the column
• 2nd row is interesting/informative the type for column x is REAL but y and z it is INTEGER, this confirms that VARCHAR(128)INTEGER if it holds an integer will be a type of INTEGER, col x though consider the INTEGER as REAL (see link for explanation)
• 3rd row all data is REAL (decimal) so type is REAL
• 4th row all columns are TEXT as data is TEXT
• 5th row has TEXT for TEXT data in column x, but shows type NULL for the NULL data

You may wish to refer to https://sqlite.org/datatype3.html