[SpatiaLite-Users] dropping column in a table

[srinivas m]

Hi all,

can anybody help me how to drop a column in a table.




Regards,
Srinivas.M

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

[a.furieri]

Hi Srinivas,

I'm really sorry, but you cannot DROP a COLUMN:
SQLite doesn't allows this operation.

As you surely know SQLite has lots of specific
idiosyncrasies: and this is one.
Dropping column isn't supported: and this is
the end of the history.

Anyway, you can quite easily circumvent this
limitation doing something like:

BEGIN TRANSACTION;
ALTER TABLE some-table RENAME TO old-table;
CREATE TABLE some-table (...);
INSERT INTO some-table (...) SELECT ... FROM old-table;
DROP TABLE old-table;
COMMIT TRANSACTION;
VACUUM;

----

1) the complete operation is performed within
a TRANSACTION: so, if some error occurs, DB
integrity will be safely preserved

2) first you have to rename your target table

3) now you can create a new table (using the
original name): obviously you'll omit to
declare the column(s) you intend to DROP

4) INSERT INTO SELECT FROM will copy any row
from the old table into the new one

5) and when the new table has been populated
then you can DROP the old table.

6) VACUUMing the DB may help in order to reclaim
any unused page.

Voila: all done.

bye Sandro

[Alessandro Sarretta]

Hi again,
thanks for the reply Sandro (I forgot to include the whole list in the previous mail...).
Using the Spatialite v1.4.0 (in Ubuntu 11.04) I can see the "Drop column" command when I right click a "normal" column, but I can't see it when I try to do the same with a geometry column.
Looking at your cookbook I found that using the command
SELECT DiscardGeometryColumn('test_geom', 'the_geom');
I can "transform" the 'test_geom' column in a non geometry one and then drop it (or do a "Recover geometry column").
Is there a more direct way to do it?
ciao
Ale


On 07/27/2011 12:50 PM, a.fu...@lqt.it wrote:

Hi Ale, how are you ?
Glad to hear from you :-)

the *big* problem is simply this one; SQLite doesn't supports:
ALTER TABLE DROP COLUMN
http://www.sqlite.org/lang_altertable.html

obviously, as you correctly state, when removing a "Geometry"
column several further operations are implied: dropping the
SpatialIndex (if any), cleaning metadata tables, removing
triggers and so on.

but even after performing such operations as required, your
Geometry column will be still present, because the SQL engine
is completely unable to drop any column once defined.

so you are required to apply a "dirty trick":
a) rename the original table
b) create again a new table (old name), avoiding to
  define any column you intend to 'drop'
c) then copy any row from the first to the second table
   (INSERT INTO ... SELECT)
d) and finally you can drop the original table: DONE
e) VACUUMing the DB may be a damn good idea ;-)

Please note well: using SpatiaLite-GUI all this (complex)
workflow is silently performed behind the scenes, because
the app-code performs any required operation in the right
sequence for you.

Highly recommended (and really useful) :-D

bye Sandro

.

[a.fu...@lqt.it]

On Wed, 27 Jul 2011 22:28:36 +0200, Alessandro Sarretta wrote

> I can see the "Drop column" command when I right click a "normal" column,
> but I can't see it when I try to do the same with a geometry column.
>

there is no compelling technical reason.
Simply I forgot to support DROP for geometries ;-)
thanks for noticing this odd inconsistency in the GUI.

bye,
Sandro

[Richard Males]

Am I correct that this is still the case in the gui version 1.8 0 development, i.e. drop geometry column not supported?

Thanks.

Dick

[a.fu...@lqt.it]

On Wed, 12 Nov 2014 16:48:21 -0800 (PST), Richard Males wrote:
> Am I correct that this is still the case in the gui version 1.8 0
> development, i.e. drop geometry column not supported?
>
> Thanks.
>
> Dick
>
> On Friday, July 29, 2011 2:49:27 AM UTC-4, sandro furieri wrote:
>

>> ON WED, 27 JUL 2011 22:28:36 +0200, ALESSANDRO SARRETTA WROTE

>>> I can see the "Drop column" command when I right click a "normal"
>> column,
>>> but I can't see it when I try to do the same with a geometry
>> column.
>>>
>>
>> there is no compelling technical reason.
>> Simply I forgot to support DROP for geometries ;-)
>> thanks for noticing this odd inconsistency in the GUI.
>>

Hi Dick,

finally implemented and immediately available from the Fossil
repository
thanks for remembering this never fixed issue ;-)

bye Sandro

[Richard Males]

Sandro:

Many thanks.   Are pre-built windows binaries available?   I have not explored fossil repository as yet.  Not asking you to do it if not available.

Dick

[a.fu...@lqt.it]

On Wed, 19 Nov 2014 07:56:25 -0800 (PST), Richard Males wrote:
> Sandro:
>
> Many thanks. Are pre-built windows binaries available? I have not
> explored fossil repository as yet. Not asking you to do it if not
> available.
>

Hi Dick,

I hope to be able to release a 4.2.1 release candidate during
this week end (including Windows binaries)

bye Sandro