Re: [sqlite] Storing a INTEGER in a TEXT field

2017-02-15 Thread Cecil Westerhof 
2017-02-15 11:12 GMT+01:00 Cecil Westerhof :

> 2017-02-15 5:40 GMT+01:00 Darko Volaric :
>
>> The problem is that you're giving your column a type when you don't want
>> it
>> to have. If the second last line was "message NOT NULL" you'd get exactly
>> what you're asking for.
>>
>
> ​When the table was designed (many years ago) it was logical to make it a
> TEXT field, but a little over a month ago I began also storing data that is
> a count. I will convert the table and look what happens to the data.
>

​Just had to execute:
UPDATE messages
SETmessage = CAST(message AS INTEGER)
WHERE  type = 'download-count'


​I also use the table to store cpu temperature. (Which is a REAL.) I had to
change the code a bit, because it stored the temperature as a string.​

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

Re: [sqlite] Storing a INTEGER in a TEXT field

2017-02-15 Thread Cecil Westerhof 
2017-02-15 5:40 GMT+01:00 Darko Volaric :

> The problem is that you're giving your column a type when you don't want it
> to have. If the second last line was "message NOT NULL" you'd get exactly
> what you're asking for.
>

​When the table was designed (many years ago) it was logical to make it a
TEXT field, but a little over a month ago I began also storing data that is
a count. I will convert the table and look what happens to the data.

The view is still handy, but I could drop the cast.

​Thank you for the idea.

​


> On Wed, Feb 15, 2017 at 1:22 AM, Cecil Westerhof 
> wrote:
>
> > I have the following table:
> > CREATE  TABLE messages(
> > messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
> > dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> > timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> > typeTEXT NOT NULL,
> > message TEXT NOT NULL
> > );
> >
> > But for some data the field message is filled with an integer. An integer
> > takes less room as its text representation and it sorts differently also.
> > Is there a way to store an INTEGER in a TEXT field? Not very important,
> > more nice to have.
> >
> > I just created the following view:
> > CREATE VIEW downloadCount AS
> > SELECT   date AS Date
> > ,time AS Time
> > ,CAST(message AS INTEGER) AS DownloadCount
> > FROM messages
> > WHEREtype = 'download-count'
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing a INTEGER in a TEXT field

2017-02-14 Thread Darko Volaric 
The problem is that you're giving your column a type when you don't want it
to have. If the second last line was "message NOT NULL" you'd get exactly
what you're asking for.

On Wed, Feb 15, 2017 at 1:22 AM, Cecil Westerhof 
wrote:

> I have the following table:
> CREATE  TABLE messages(
> messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
> dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> typeTEXT NOT NULL,
> message TEXT NOT NULL
> );
>
> But for some data the field message is filled with an integer. An integer
> takes less room as its text representation and it sorts differently also.
> Is there a way to store an INTEGER in a TEXT field? Not very important,
> more nice to have.
>
> I just created the following view:
> CREATE VIEW downloadCount AS
> SELECT   date AS Date
> ,time AS Time
> ,CAST(message AS INTEGER) AS DownloadCount
> FROM messages
> WHEREtype = 'download-count'
> ;
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing a INTEGER in a TEXT field

2017-02-14 Thread Cecil Westerhof 
2017-02-15 1:32 GMT+01:00 Simon Slavin :

>
> On 15 Feb 2017, at 12:22am, Cecil Westerhof 
> wrote:
>
> > Is there a way to store an INTEGER in a TEXT field?
>
> No.  But you can do it the other way around.  You can store text in an
> INTEGER field.  Just bind it using sqlite3_bind_textnn() or pass a value
> delimited by single quotes.  You’ll find that SQLite is happy to store and
> retrieve it.
>

​I thought so, but it never hurts to check. ;-)

The table is already defined and it stores mostly text. The fields date,
time and type are also relatively big, so I keep it like this and use the
view I think.

​Thanks.​

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

Re: [sqlite] Storing a INTEGER in a TEXT field

2017-02-14 Thread Simon Slavin 

On 15 Feb 2017, at 12:22am, Cecil Westerhof  wrote:

> Is there a way to store an INTEGER in a TEXT field?

No.  But you can do it the other way around.  You can store text in an INTEGER 
field.  Just bind it using sqlite3_bind_textnn() or pass a value delimited by 
single quotes.  You’ll find that SQLite is happy to store and retrieve it.

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

Re: [sqlite] Storing a INTEGER in a TEXT field

2017-02-14 Thread Richard Hipp 
On 2/14/17, Cecil Westerhof  wrote:
> Is there a way to store an INTEGER in a TEXT field?

No.  It will be automatically converted into TEXT.  Because that is
what PostgreSQL does.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Storing a INTEGER in a TEXT field

2017-02-14 Thread Cecil Westerhof 
I have the following table:
CREATE  TABLE messages(
messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
dateTEXT NOT NULL DEFAULT CURRENT_DATE,
timeTEXT NOT NULL DEFAULT CURRENT_TIME,
typeTEXT NOT NULL,
message TEXT NOT NULL
);

But for some data the field message is filled with an integer. An integer
takes less room as its text representation and it sorts differently also.
Is there a way to store an INTEGER in a TEXT field? Not very important,
more nice to have.

I just created the following view:
CREATE VIEW downloadCount AS
SELECT   date AS Date
,time AS Time
,CAST(message AS INTEGER) AS DownloadCount
FROM messages
WHEREtype = 'download-count'
;

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