Re: [sqlite] CREATE TABLE AS SELECT * FROM changes column definition --bug or feature?

2009-08-02 Thread P Kishor 
On Sun, Aug 2, 2009 at 10:39 PM, Igor Tandetnik 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

Re: [sqlite] CREATE TABLE AS SELECT * FROM changes column definition --bug or feature?

2009-08-02 Thread Igor Tandetnik 
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  descnum
> --  --  --
> 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?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CREATE TABLE AS SELECT * FROM changes column definition -- bug or feature?

2009-08-02 Thread P Kishor 
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  descnum
--  --  --
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’?

-- 
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