| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
06-10-11, 09:54
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Orlando, FL
Posts: 184
|
|
Data Type For Storing Weight
|
|
I'm using PostgreSQL to record weight on a daily basis but I am not sure if there's a specific data set I should be using for weight:
Code:
INSERT INTO mold (id, date, dow, lbs, photo)
VALUES
(
'1',
'2011-06-09',
'Thursday',
'182.6',
'Yes'
);
Can someone tell me what data type I should be using in my table? I did a Google search but I didn't see any info on this.
|
|
06-10-11, 10:46
|
|
Registered User
|
|
Join Date: May 2008
Posts: 270
|
|
|
|
06-10-11, 11:10
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
|
|
As a side note: your INSERT syntax is wrong. Numbers should not be enclosed in single quotes!
|
|
06-10-11, 11:47
|
|
Registered User
|
|
Join Date: May 2008
Posts: 270
|
|
Actually, I always use single quotes when working with numeric/decimal values. I don't know if there's an implicit cast going on when I do this, but I seem to recall having had occasional type mismatch errors with unquoted decimals.
|
|
06-10-11, 11:52
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by futurity
Actually, I always use single quotes when working with numeric/decimal values. I don't know if there's an implicit cast going on when I do this
|
Yes sometimes there will be an implicit cast which is somewhat "dangerous" as it might potentially prevent the engine from using an index if used in a WHERE condition.
Quote:
|
But I seem to recall having had occasional type mismatch errors with unquoted decimals.
|
Not with PostgreSQL.
Postgres is very strict about the correct format of literals and putting numeric values in single quotes is never a good idea.
|
|
06-10-11, 12:03
|
|
Registered User
|
|
Join Date: May 2008
Posts: 270
|
|
Quote:
Originally Posted by shammat
Not with PostgreSQL.
Postgres is very strict about the correct format of literals and putting numeric values in single quotes is never a good idea.
|
Just to be clear, I'm speaking specifically about the DECIMAL and NUMERIC data types (which are equivalent). I do not put quotes around the other numeric data types (INT, REAL, etc.).
I don't know, maybe this is wrong. I only started doing it because, as I said, I seem to recall having had occasional type-casting problems which disappeared when I added the quotes.
|
|
06-10-11, 12:10
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by futurity
I don't know, maybe this is wrong. I only started doing it because, as I said, I seem to recall having had occasional type-casting problems which disappeared when I added the quotes.
|
Please show us an example because I cannot image any situation where this would be necessary (or even correct)
|
|
06-10-11, 12:39
|
|
Registered User
|
|
Join Date: May 2008
Posts: 270
|
|
Quote:
Originally Posted by shammat
Please show us an example because I cannot image any situation where this would be necessary (or even correct)
|
This was quite a while ago, so to be honest, I couldn't come up with an example off the top of my head. It was a rather complicated query that I was passing to the server using, I believe, php's pg_query_params() function, and it seemed to choke on the type conversion.
However, this may be of interest:
Code:
create table foo (nr decimal not null);
insert into foo values (1), (1.0);
explain select * from foo where nr = 1;
explain select * from foo where nr = '1';
explain select * from foo where nr = 1.0;
explain select * from foo where nr = '1.0';
Notice that in the first two SELECTs the literals are cast to NUMERIC (presumably from an INT) to do the comparison, while in the second two they are not. So quotes do not seem make a difference, at least in this case.
|
|
06-10-11, 13:12
|
|
Registered User
|
|
Join Date: Oct 2010
Location: Orlando, FL
Posts: 184
|
|
Quote:
Originally Posted by shammat
As a side note: your INSERT syntax is wrong. Numbers should not be enclosed in single quotes!
|
Does that include dates or just numerical data?
|
|
06-10-11, 13:19
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by CarlosinFL
Does that include dates or just numerical data?
|
The best way to specify date literals is to either use to_date() or (my personal preference) ANSI literals, e.g.
Code:
SELECT *
FROM some_table
WHERE some_date_column = DATE '2011-06-10'
|
|
06-13-11, 20:47
|
|
Registered User
|
|
Join Date: May 2008
Posts: 270
|
|
Not that anyone was curious/confused about this other than me, but I find this somewhat interesting:
Code:
postgres=> select pg_typeof('1');
pg_typeof
-----------
unknown
(1 row)
postgres=> select pg_typeof(1);
pg_typeof
-----------
integer
(1 row)
postgres=> select pg_typeof('1.0');
pg_typeof
-----------
unknown
(1 row)
postgres=> select pg_typeof(1.0);
pg_typeof
-----------
numeric
(1 row)
postgres=> select pg_typeof('foo');
pg_typeof
-----------
unknown
(1 row)
Anyway, I guess the take-away is: listen to shammat. Do not quote numbers. 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Data Type For Storing Weight
