Thank you so much for the detail explanation about datatype. Big thanks, JP
----- Original Message ---- From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Cc: [EMAIL PROTECTED] Sent: Wednesday, May 7, 2008 12:27:21 PM Subject: Re: [sqlite] SQLITE3 datatype On Wed, 7 May 2008, Joanne Pham wrote: > I was wondering what is the different in size between int(8) and INTEGER > datatype. > Thanks, In the sense you are asking, there is no difference, but more importantly, the question reflects a misunderstanding of how SQLite is designed. Most database applcations use datatypes as part of the column definitions; SQLite does not, it uses 'affinities' instead. In other database apps you assign a datatype to a column; the database engine will then reject (or convert) data which does not conform to the declared datatype. In SQLite, any column can store any kind of data. When you create a table, you are not creating 'datatypes, you are defining column 'affintities'. Affinities govern the algorithms SQLite uses when processing inserts & updates. This is clearly discussed in: http://sqlite.org/datatype3.html To illustrate it for yourself, try this: CREATE TABLE ztemp (col1 INTEGER, col2 TEXT); INSERT INTO ztemp VALUES(1234, "abcd"); INSERT INTO ztemp VALUES("abcd", 1234); SELECT * FROM ztemp; output from above: ----------------- 1234,abcd abcd,1234 Notice the second insert. I inserted the text value 'abc' into 'col1', which I had defined with an integer affinity. You can see from the output that the first column contains both data elements, integer and text, because SQLite columns do not have datatypes. Note that in above I could also define the table as: CREATE TABLE ztemp (col1, col2); and the result with be the same. The database creates the same table with or without affinities. The key sentence in the SQLite docs, in section "2. Column Affinity" is: "In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value." You need to re-read that section closely and you will see that the answer to the question you are asking ("What is the differnce between int(8) and INTEGER?") is that there is no difference; the question is not applicable in the context of SQLite because SQLite does not use datatypes. Chris ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Christopher F. Martin School of Medicine Center for Digestive Diseases & Nutrition CB# 7555, 4104 Bioinformatics Bldg. University of North Carolina at Chapel Hill Chapel Hill, North Carolina 27599-7555 Phone: 919.966.9340 Fax: 919.966.7592 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ On Wed, 7 May 2008, Joanne Pham wrote: > Hi , >> "Can you direct us where you find out that "SQLITE3 has bigint and int" >> as datatypes?" > > Not on any website but one of another project in my company using sqlite and > they created one of the using bigint, boolean, int(1) > as the datatype and I have tried these datatype to create the table and the > table is created sucessfully without any problem. So > I was wondering what is the different in size between int(8) and INTEGER > datatype. > Thanks, > JP > > > ----- Original Message ---- > From: P Kishor <[EMAIL PROTECTED]> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Wednesday, May 7, 2008 10:08:54 AM > Subject: Re: [sqlite] SQLITE3 datatype > > On 5/7/08, Joanne Pham <[EMAIL PROTECTED]> wrote: >> Hi All, >> I have read online document regarding SQLITE3 data type and below is list >> of these datatypes: [ Mark Set ] >> * TEXT >> * NUMERIC >> * INTEGER >> * REAL >> * NONE >> But just now I found out SQLITE3 has bigint and int as another datatype. >> Can you direct me where I can find out the complete list of SQLITE >> datatypes and the size of each datatype. > > Can you direct us where you find out that "SQLITE3 has bigint and int" > as datatypes? > > >> Thanks, >> JP >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> >> ____________________________________________________________________________________ >> Be a better friend, newshound, and >> know-it-all with Yahoo! Mobile. Try it now. >> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
