Hi!

I am working with a third party database (I didn't create the schema, and can not change it) that has a table holding user id's. For some reason, the column that holds the id has INTEGER affinity (I'm assuming the id's were numerical at some point in the past).

The schema is (simplified): CREATE TABLE identities (_id INTEGER PRIMARY KEY AUTOINCREMENT, address INTEGER UNIQUE)

These days, most id's are a UUID string, but some older ones are phone numbers (output slightly censored):

sqlite> SELECT address, typeof(address) FROM identities LIMIT 2;
        address: +31612345678
typeof(address): text

        address: 03d72dcc-xxxx-xxxx-xxxx-xxxxxxxxxxxx
typeof(address): text

I can not figure out how the string '+31612345678' can be inserted into this column. A simple INSERT INTO identities (address) VALUES ('+31612345678') results in

        address: 31612345678
typeof(address): integer

I have tried some other methods, using ?-placeholders, CAST( AS TEXT), using the C-API sqlite3_bind_text() function, but nothing seems to work. I would have given up if the database I'm working with didn't already have some of these phone numbers inserted (so I know it's possible). I am admittedly tired right now, and maybe not thinking straight, but can someone enlighten me as to how to get such a phone number inserted into the table?

I think I normally understand SQLite's type-system, and appreciate it even. In this case however the conversion is not exactly lossless as for phone numbers, the leading plus sign is meaningful, but discarded.

Thanks!

-EDIT-

I must apologize, it seems I'm mixing up my databases. The one that has the phone numbers inserted has address TEXT UNIQUE. The database with address INTEGER UNIQUE is in an older version of the database, and does not contain these phone numbers. I intend not to start new threads here in the future without having a proper nights sleep beforehand.

Are you certain that there are no trailing or leading whitespace characters, or any additional odd zero-width characters hidden in there anywhere?

I think I am certain, at least doing SELECT HEX(address), LENGTH(address) FROM identities does not indicate any hidden characters. Thanks for the suggestion though.

Run pragma integrity_check; and see if you get any complaints about TEXT value in identities.address.

Thanks for the suggestion.

sqlite> pragma integrity_check;
integrity_check: ok

I wouldn't have thought an integrity check to complain about this, as far as I know any type value can be put into any type column in SQLite without problems.

as far as I know any type value can be put into any type column in SQLite without problems.

INTEGER PRIMARY KEY is special, and it becomes the ROWID. Only integers are allowed.

If you're storing phone numbers in any other field, you should probably use the TEXT type affinity. In an INTEGER type affinity field, if a string looks like an integer, it will be converted and stored as an integer. '+008675309' becomes the integer 8675309, where you probably want to store the original string instead.

STRICT tables are also potentially different, of course, but we're now veering way off topic, which turned out to be a misunderstanding about the table definition.

So, a phone number does not fit SQLite's definition of integer.

I don't think it fits anybody's definition of integer. Leading zeros are never going to be stored properly; in the country where I live, area codes always have one leading zero if they are in the same country, but international area codes have TWO leading zeros.