Quantcast

Storing a INTEGER in a TEXT field

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Storing a INTEGER in a TEXT field

Cecil Westerhof-5
I have the following table:
CREATE  TABLE messages(
    messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
    date        TEXT NOT NULL DEFAULT CURRENT_DATE,
    time        TEXT NOT NULL DEFAULT CURRENT_TIME,
    type        TEXT 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
WHERE    type = 'download-count'
;

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Storing a INTEGER in a TEXT field

Richard Hipp-3
On 2/14/17, Cecil Westerhof <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Storing a INTEGER in a TEXT field

Simon Slavin-3
In reply to this post by Cecil Westerhof-5

On 15 Feb 2017, at 12:22am, Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Storing a INTEGER in a TEXT field

Cecil Westerhof-5
2017-02-15 1:32 GMT+01:00 Simon Slavin <[hidden email]>:

>
> On 15 Feb 2017, at 12:22am, Cecil Westerhof <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Storing a INTEGER in a TEXT field

Darko Volaric
In reply to this post by Cecil Westerhof-5
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 <[hidden email]>
wrote:

> I have the following table:
> CREATE  TABLE messages(
>     messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
>     date        TEXT NOT NULL DEFAULT CURRENT_DATE,
>     time        TEXT NOT NULL DEFAULT CURRENT_TIME,
>     type        TEXT 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
> WHERE    type = 'download-count'
> ;
>
> --
> Cecil Westerhof
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Storing a INTEGER in a TEXT field

Cecil Westerhof-5
2017-02-15 5:40 GMT+01:00 Darko Volaric <[hidden email]>:

> 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 <[hidden email]>
> wrote:
>
> > I have the following table:
> > CREATE  TABLE messages(
> >     messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
> >     date        TEXT NOT NULL DEFAULT CURRENT_DATE,
> >     time        TEXT NOT NULL DEFAULT CURRENT_TIME,
> >     type        TEXT 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
> > WHERE    type = 'download-count'
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Storing a INTEGER in a TEXT field

Cecil Westerhof-5
2017-02-15 11:12 GMT+01:00 Cecil Westerhof <[hidden email]>:

> 2017-02-15 5:40 GMT+01:00 Darko Volaric <[hidden email]>:
>
>> 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
SET    message = 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...