CODEWITHSUNDEEP
stringsqlitetypes
xralf
xralf

Reputation: 3642

String data type in sqlite

I created a table as follows:

create table employers (
    id integer primary key,
    name string,
    surname string,
    description string
);

I noticed here that string is not between data types, should I replace it with something else? Is it alias for some other datatype? What is the maximum number of characters that can this string contain?

I used string and it does not make any problems. I'm only interested how is it possible, that I could use string. Is it some alias? What are the possibilities of this type?

thank you

Upvotes: 3

Views: 11764

Answers (2)

tofutim
tofutim

Reputation: 23374

You want to use TEXT which we use whatever it needs to in order to hold your data. 

CREATE TABLE Employers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    surname TEXT,
    description TEXT
);

See http://www.sqlite.org/datatype3.html and note:

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

That noted:

SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

Finally:

Maximum length of a string or BLOB

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

-DSQLITE_MAX_LENGTH=123456789 The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.

During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface.

Upvotes: 13

Dave G
Dave G

Reputation: 9767

Declare your field as "TEXT" and I believe you can specify a length restriction.

so you can declare name TEXT(255) as a 255 character TEXT value.

Upvotes: 0

Related Questions