Postgresql creating table query with specifically types

Question

I'm making a car number registration and I would like to transfer the car numbers to database. The car number correct format is ABC123 3 letters and 3 numbers. I just started data science, or how could I call the sql science. So the thing is that I don't know how to create the table with the CARNUM Data type. For example here's the code which I use to create an table.

 create_table_query = '''CREATE TABLE Vartotojai
      (ID INT PRIMARY KEY     NOT NULL,
      CARNUM            TEXT    NOT NULL,
      TIME         TIME); '''

cursor.execute(create_table_query)
connection.commit()
print("Table created successfully in PostgreSQL ")

In this CARNUM TEXT NOT NULL, I use Text, but I think this is not what I need to use for 3x Letters and 3x Numbers. Also do I need to use the NOT NULL? Cause I'm getting the numbers from user input so I use this as return for car numbers:

while True:
    car_numb = input("Input car number:")

    if car_numb_re.match(car_numb) and car_numb.isupper():
        # matches!
        print("Match")
        Registration = True
        TicketReg(car_numb)
        break
    elif car_numb.islower():
        print("Wrong number format. Should be (XXX123)")
        continue

Format in python for car numbers is something like this.

car_numb_re = re.compile(r'^[A-Z]{3}\d{3}$')

Use varchar(6) instead of text, also null is depends on your application — Kurose
– Kurose, Commented Sep 3, 2019 at 15:54
Text type is fine, the unique difference between varchar and text is that in varchar you are constraining the max lenght of a characters that you can store into the colum, text is unlimited char size but in your case you are constraining with a regex before insert. — lennhv
– lennhv, Commented Sep 3, 2019 at 16:45

Belayer · Accepted Answer · 2019-09-03 19:51:08Z

1

You could additionally (or instead) put a constraint directly in the table itself using essentially the same regular expression. The advantage of doing so it that even if somehow your app is circumvented (a fix perhaps that isn't quite correct) the rule will still be enforced.

create_table_query = '''CREATE TABLE Vartotojai
      (ID INT PRIMARY KEY     NOT NULL,
      CARNUM            TEXT    NOT NULL,
      TIME         TIME, 
      constraint carnum_format_check check (carnum ~ '^[A-Z]{3}\d{3}$')
      ); ''';

answered Sep 3, 2019 at 19:51

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql creating table query with specifically types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related