alter table add column

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

alter table add column

fabio spadaro
"Alter table add column" command drop data from table.
Can you keep the data or should I store the data before the alter and then put
them in the table?

--
Fabio Spadaro
www.fabiospadaro.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table add column

Puneet Kishor-2

On May 31, 2011, at 10:11 AM, Fabio Spadaro wrote:

> "Alter table add column" command drop data from table.
> Can you keep the data or should I store the data before the alter and then put
> them in the table?

ALTER TABLE ADD COLUMN does not drop data from the table.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table add column

Stephan Beal-3
In reply to this post by fabio spadaro
On Tue, May 31, 2011 at 5:11 PM, Fabio Spadaro <[hidden email]>wrote:

> "Alter table add column" command drop data from table.
> Can you keep the data or should I store the data before the alter and then
> put
> them in the table?
>

http://www.sqlite.org/lang_altertable.html

says:

"The execution time of the ALTER TABLE command is independent of the amount
of data in the table. The ALTER TABLE command runs as quickly on a table
with 10 million rows as it does on a table with 1 row."

Implicit in that statement is that ALTER TABLE does not modify/delete any
table data. If it did, the runtime would probably be O(N) or worse, not O(1)
(as described above).

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table add column

fabio spadaro
Hi

2011/5/31 Stephan Beal <[hidden email]>

> On Tue, May 31, 2011 at 5:11 PM, Fabio Spadaro <[hidden email]
> >wrote:
>
> > "Alter table add column" command drop data from table.
> > Can you keep the data or should I store the data before the alter and
> then
> > put
> > them in the table?
> >
>
> http://www.sqlite.org/lang_altertable.html
>
> says:
>
> "The execution time of the ALTER TABLE command is independent of the amount
> of data in the table. The ALTER TABLE command runs as quickly on a table
> with 10 million rows as it does on a table with 1 row."
>
> Implicit in that statement is that ALTER TABLE does not modify/delete any
> table data. If it did, the runtime would probably be O(N) or worse, not
> O(1)
> (as described above).
>
> --
> ----- stephan beal
> http://wanderinghorse.net/home/stephan/
>  _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Ok I have been deceived by the anomaly that I found in my application. In
practice I have renamed the table (with alter table), I inserted a row and
then I inserted a new column (with alter table add column) and to my
surprise I saw the empty table. The problem is that my insert is not
successful and what you do not understand why try to make the insert taking the
old name of the table.

To recap:
Step 1: alter table pippo rename to fabio -> ok
step 2: insert into fabio (field1) values ​​('1 ') -> ko
     OperationalError: no such table main.pippo
Step 3: alter table add column fabio field2 integer null -> ok
result:
      empty table
Question: Why does my insert referring to the old table?

use python sqlite vers. 2.5.9


--
Fabio Spadaro
www.fabiospadaro.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table add column

Stephan Beal-3
On Tue, May 31, 2011 at 6:09 PM, Fabio Spadaro <[hidden email]>wrote:

> To recap:
> Step 1: alter table pippo rename to fabio -> ok
> step 2: insert into fabio (field1) values ('1 ') -> ko
>     OperationalError: no such table main.pippo
> Step 3: alter table add column fabio field2 integer null -> ok
> result:
>      empty table
> Question: Why does my insert referring to the old table?
>

Because you typed it that way? If the insert is part of a trigger (you
didn't mention a trigger, but it sounds like you're using one), see:

http://www.sqlite.org/lang_altertable.html

and read the 3rd paragraph. If it is not part of a trigger, then simply
correct the spelling in your program/SQL script.

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table add column

Simon Slavin-3
In reply to this post by fabio spadaro

On 31 May 2011, at 5:09pm, Fabio Spadaro wrote:

> Step 1: alter table pippo rename to fabio -> ok
> step 2: insert into fabio (field1) values ​​('1 ') -> ko
>     OperationalError: no such table main.pippo

How does step 2 know the name 'pippo' ?  You don't seem to supply it in the command.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users