Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: 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.

  2. #2
    Join Date
    May 2008
    Posts
    277
    You either a real or, if you require exact precision, numeric/decimal. The documentation covers when it might be appropriate to use one or other.

    PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Numeric Types

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    As a side note: your INSERT syntax is wrong. Numbers should not be enclosed in single quotes!

  4. #4
    Join Date
    May 2008
    Posts
    277
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by futurity View Post
    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.

    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.

  6. #6
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by shammat View Post
    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.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by futurity View Post
    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)

  8. #8
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by shammat View Post
    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.

  9. #9
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    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?

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    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'

  11. #11
    Join Date
    May 2008
    Posts
    277
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •