On Sun, Aug 2, 2009 at 10:39 PM, Igor Tandetnik<itandet...@mvps.org> wrote: > P Kishor wrote: >> SQLite version 3.6.11 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, desc TEXT, num >> INTEGER DEFAULT 0); >> sqlite> INSERT INTO foo (desc) VALUES ('foo'); >> sqlite> INSERT INTO foo (desc) VALUES ('bar'); >> sqlite> INSERT INTO foo (desc) VALUES ('baz'); >> sqlite> SELECT * FROM foo; >> id desc num >> ---------- ---------- ---------- >> 1 foo 0 >> 2 bar 0 >> 3 baz 0 >> sqlite> CREATE TABLE bar AS SELECT * FROM foo; >> sqlite> .s >> CREATE TABLE bar(id INTEGER,"desc" TEXT,num INTEGER); >> CREATE TABLE foo (id INTEGER PRIMARY KEY, desc TEXT, num INTEGER >> DEFAULT 0); >> >> >> Why did the definition of the column ‘num’ change? Where did the >> ‘DEFAULT 0’ part go in the definition of ‘num’? > > You don't make a copy of a table - you make a copy of the resultset of a > SELECT statement. Columns in said resultset don't carry attributes like > DEFAULT, even though columns in the underlying table may. Consider: > > create table bar as > select id + 1, desc || 'xyz', num * id from foo; > > What do you expect the definition of bar to be? >
Hmmm... now that you say so, I am reminded of this topic a short while ago. Makes sense what you say Igor. However, if the "copy of the resultset of a SELECT statement" was smart enough to bring over 'desc TEXT', why was it not quite smart enough to bring over the 'PRIMARY KEY' part of 'id' or the 'DEFAULT 0' part of 'num'? I wonder if there is any technical reason for not doing that? After all, the information is in the schema. I guess 'CREATE TABLE newtable AS SELECT * FROM oldtable' is not a recommended way of duping a table. The most reliable and accurate way well might be to dump the old table, recreate the new table, then import the data from the old table. Kinda pain if the table definition is highly complicated. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users