Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Unanswered: Input / Data Mask

    Hello everyone,

    My company is making the jump from using Access for our database needs into PostgreSQL and I was tasked with preparing the old database for migration. One of the hurdles I need to jump is to be able to enforce limitations on what can be entered into certain fields. Specifically, in this case, the Employee ID field. An employee ID, for example, could be 012345 or 000123 and so on. When I converted it to an integer field (so that ONLY numbers can be input into it), it dumped out all the zeroes at the beginning of employee IDs (so 000123 became just 123).

    Any advice on what I can do to both limit the field to integers being input in there AND to make sure it maintains all the zeroes at the front?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are two ways to approach your problem.

    First and foremost, if the datatype is defined to be an integer, then store the data as an integer. Leading zeros are a formatting issue, and should be handled at the client rather than in the database. This is the most "relationally correct" answer as far as I know.

    If you truly want to store the Employee ID as a character column, that poses problems but they can be solved. The way to accomplish this is a CHECK CONSTRAINT. See http://www.postgresql.org/docs/9.3/s...eatetable.html for details, but the general gist of the idea would be something like:
    Code:
    CREATE TABLE badTable (
       badID CHAR(6)
       CHECK (badID NOT SIMILAR TO '%[^0-9]%')
    )
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    2
    Thank you, sir, that constraint worked perfectly! I've spent probably about three hours between Friday and today trying to Google this and figure it out. I appreciate the assistance.

    Just out of curiosity, can you translate what the '%[^0-9]%' is supposed to be meaning? I understand that the end result is that it does not allow alpha characters, but just by casually glancing at it, you'd think that it wouldn't allow numeric ones.

    I have a cursory understanding of SQL, but I'm still learning quite a bit as I work on this project. Any insight or advice would be appreciated.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your definition of what is correct is a "double negative", so you don't want anything that isn't a digit. I created a pattern that matches anything that "isn't a digit", then specified that I wanted something that wasn't similar to it. The whole thing is rather peculiar, but it meets your needs!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually, PostgreSQL has more expressive regular expressions so there's a simpler answer.
    Code:
    CHECK (BadID SIMILAR TO '[0-9]*')
    To have a common expression between between MS-Access (Jet) and PostgreSQL you could also use:
    Code:
    CHECK (BadID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
    ...which both should do the same thing as the previous example, but using positive logic.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    you could also use:
    Code:
    CHECK (BadID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
    That will not work with Postgres.

    The LIKE operator in SQL does not support "ranges" or anything remotely similar to a regular expression (and Postgres sticks to the standard). The only wildcards supported in the LIKE operator are _ (the underscore) for a single character and % for multiple characters.

    So BadID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' does not contain any for the LIKE operator and thus it is the same as writing BadID = '[0-9][0-9][0-9][0-9][0-9][0-9]'

    The SQL standard only defines SIMILAR TO to support something that can be seen as a regular expression (although it does not support the full Posix regex syntax).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Shammat is correct, on a "vanilla" install of PostgreSQL the LIKE operator does not support character ranges like the SIMILAR TO operator does. Only an "enhanced" version of PostgreSQL can support syntax that works against both PostgreSQL (which follows the ISO standard) and MS-Access (MS-SQL and Jet) which adds functionality like character ranges to the standard.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    Only an "enhanced" version of PostgreSQL can support syntax that works against both PostgreSQL and MS-Access (MS-SQL and Jet)
    Which version would that be? I have never heard of such a fork.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This isn't so much of a fork as it is a kludge. One of their systems guys figured out how to patch the code so that LIKE becomes a synonym for SIMILAR TO. My guess is that he did something simple like re-using a token or just patching the dispatch jump table. I'll ask the next time I see him.

    I'm sure that this is a trivial patch, and it makes it simple for them to support a single code base for client applications between several database products.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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