Re: [sqlite] specifying field type, any benefit?
If you have "dot zero(s)" values (such as, 4.0) do not save them in an
INTEGER column because then they will lose their float characteristics.
insert into students values(John, 4.0)
select age from students where first_name = 'John'
4 <-- got converted to integer
Regards
/sd
"Mark
Wyszomierski"To:
sqlite-users@sqlite.org
<[EMAIL PROTECTED]cc:
> Subject: [sqlite] specifying
field type, any benefit?
01/04/2006 03:54
PM
Please respond to
sqlite-users
Hi all,
I switched to sqlite from mysql awhile ago, I maintained the field types
in my sqlite implementation such as:
create table students (first_name TEXT, age INTEGER);
I'm just wondering if there is any point to specifying the field type as if
I try adding a string type into the age field, it seems to be accepted ok:
insert into students values('hello');
Does sqlite have any problem regarding setting a field defined as INTEGER
from a text string (any limits etc?), are there any performance gains to be
had with specifying the field type?
Thanks,
Mark
The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you
receive this in error, please contact the sender and delete the
material from any computer.
For Translation:
http://www.baxter.com/email_disclaimer
Re: [sqlite] specifying field type, any benefit?
Got it, thank you very much all,
Mark
On 1/4/06, Henry Miller <[EMAIL PROTECTED]> wrote:
>
> On Wednesday 04 January 2006 02:54 pm, Mark Wyszomierski wrote:
> > Hi all,
> >
> > I switched to sqlite from mysql awhile ago, I maintained the field types
> > in my sqlite implementation such as:
> >
> > create table students (first_name TEXT, age INTEGER);
> >
> > I'm just wondering if there is any point to specifying the field type as
> if
> > I try adding a string type into the age field, it seems to be accepted
> ok:
> >
> > insert into students values('hello');
> >
> > Does sqlite have any problem regarding setting a field defined as
> INTEGER
> > from a text string (any limits etc?), are there any performance gains to
> be
> > had with specifying the field type?
>
> sqlite does not care about types. You can insert BLOBs into INTEGER
> fields
> if it makes you happy. sqlite will not care.
>
> I recommend you place them in your definitions anyway, for two reasons.
> First, you might want to switch to a different database latter. Second,
> it
> is sometimes helpful to tell your successors what you intend a field to
> be.
>
> Even though sqlite doesn't care, it is a good idea to be more strict
> yourself.
>
> As the other response said, sqlite used NULL for the unspecified
> parameters.
>
Re: [sqlite] specifying field type, any benefit?
On Wednesday 04 January 2006 02:54 pm, Mark Wyszomierski wrote:
> Hi all,
>
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
>
> create table students (first_name TEXT, age INTEGER);
>
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
>
> insert into students values('hello');
>
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?
sqlite does not care about types. You can insert BLOBs into INTEGER fields
if it makes you happy. sqlite will not care.
I recommend you place them in your definitions anyway, for two reasons.
First, you might want to switch to a different database latter. Second, it
is sometimes helpful to tell your successors what you intend a field to be.
Even though sqlite doesn't care, it is a good idea to be more strict yourself.
As the other response said, sqlite used NULL for the unspecified parameters.
Re: [sqlite] specifying field type, any benefit?
Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
>
> create table students (first_name TEXT, age INTEGER);
>
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
>
> insert into students values('hello');
>
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?
>
If you have a table like this:
CREATE TABLE ex1(part_number INTEGER);
And then you insert text that looks like a number but has
leading zeros:
INSERT INTO ex1 VALUES('000123');
The string gets converted into an integer. You can read it
back out as a string if that is what you want, but the leading
zeros will get lost. If you want to preserve the leading zeros,
you have to make sure the type of the column is TEXT:
CREATE TABLE ex1(part_number TEXT);
Other than some really strange corner cases like the above,
the type of the column does not matter in SQLite.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] specifying field type, any benefit?
On Wed, Jan 04, 2006 at 03:54:49PM -0500, Mark Wyszomierski wrote:
> Hi all,
>
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
>
> create table students (first_name TEXT, age INTEGER);
>
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
>
> insert into students values('hello');
>
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?
Well, what's actually happening is that since you didn't supply a value
for age, it's being set to NULL. While that's fine for testing and
what-not, in your code you should really provide a list of fields that
you're inserting into, so there's no ambiguity. IE:
INSERT INTO students(first_name) VALUES('John');
As for inserting text into an int, presumably it will cast it if it can.
So
INSERT INTO students(age) VALUES('John');
would fail, but
INSERT INTO students(age) VALUES('18');
should work. Of course, you're ultimately just making the database do
more work; you should really just insert the int as an int and be done
with it...
INSERT INTO students(age) VALUES(18);
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] specifying field type, any benefit?
http://www.sqlite.org/datatype3.html
[sqlite] specifying field type, any benefit?
Hi all,
I switched to sqlite from mysql awhile ago, I maintained the field types
in my sqlite implementation such as:
create table students (first_name TEXT, age INTEGER);
I'm just wondering if there is any point to specifying the field type as if
I try adding a string type into the age field, it seems to be accepted ok:
insert into students values('hello');
Does sqlite have any problem regarding setting a field defined as INTEGER
from a text string (any limits etc?), are there any performance gains to be
had with specifying the field type?
Thanks,
Mark
