SQLite and PostgreSql longtext

views:

115

answers:

SQLite and PostgreSql longtext

Hello all,

Does anyone know how to change a column in SQLite and PostgreSQL to LONGTEXT?

I have done so in MySQL successfully with: "ALTER TABLE projects MODIFY description LONGTEXT;"

But this clause doesn't seem to work on SQLite. I tried hard to find documentation on PostgreSQL, but that site's format really makes people puke. SQLite's website is better but the only command I find relevant, alter table, doesn't seem to support changing column data type at all. ( infact, it doesn't even allow changing column name!!!)

Thanks all!

+1 A:

AFAIK, PostgreSQL does not have a limit on blobs, so don't worry.

SQLite3, apparently, doesn't care about the column type in this case (i.e. TEXT is the same as LONGTEXT)

glebm 2010-05-05 02:15:53

I see. Thanks for the note

Nik 2010-05-05 02:31:24

SQLite *does* support ADD COLUMN. You can't remove columns, though.

dan04 2010-05-05 06:55:20

Sqlite supports both, I think.//It is much better than mssql.

Behrooz 2010-05-05 11:50:56

SQLite definitely does not support removing columns.Comparing SQLite to MySQL is like comparing a family car to a Ferrari lol

glebm 2010-05-06 03:34:00

+1 A:

For PostgreSQL, see the doc here (e.g., ALTER TABLE my_table ALTER COLUMN my_col text).

The SQLite doc states

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

And I suppose changing the datatype of the column is out of scope. Probably to support this, you will need to do a SELECT * INTO ... followed by DROP TABLE ... and then create the table and run INSERT INTO ... SELECT * FROM ...

ig0774 2010-05-05 02:19:32

Okay, I am going with your approach, and thanks for the link.

Nik 2010-05-05 02:37:08

+2 A:

I don't know what problem you have with Postresql documentation, it's quite good IMO.

To change a column datatype, here is the syntax.

Postgres supports arbitrary long strings (well, up to 2 GB or so) with the TEXT datatype.

leonbloy 2010-05-05 02:24:44

I must say that finding relevant things in a manual, any manual, has a lot to do with how familiar you are with it. Documentation like PrototypeJS, Rails, Ruby, in my opinion, even with little experience, one can quickly find what he needs. Well, okay, I first went to postgresql's site for documentation, there was a clear link. I went there, and went with 8.4 with comment, then maybe b/c I don't know the terms in DB very well, I instinctively looked for something like modifying a table, but found none. But compare it to MySQL, it is much better indeed.

Nik 2010-05-05 02:36:46

@Nik Hmmm... personally I hate RDoc but find PostgreSQL's format pretty straight-forward... Different strokes, I suppose.

ig0774 2010-05-05 02:39:14

Used to detest RDoc as well, then it became a bit more tolerable, and after a few in-the-zone coding session, it became a lot better. My prejudice, which now I regret in expressing them so carelessly, came from reading MySQL's documentation. Thinking databases' Now that I am looking at the pages you and Leonbloy mentioned, I must say it's looking great. I think it's me who doesn't know "Data Definition" is where you can dig up what I asked about.

Nik 2010-05-05 03:50:56

Perhaps you shouldn't be designing databases without a familiarity with the terms? Using MySQL obviously doesn't count.

MkV 2010-05-05 06:15:57

+1 A:

There's no point in declaring a LONGTEXT column in SQLite. All type names with TEXT or CHAR or CLOB in them are equivalent.

dan04 2010-05-05 06:57:26

ansaurus

tags:

views:

answers:

SQLite and PostgreSql longtext

related questions