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}$')

• Kurose: Use varchar(6) instead of text, also null is depends on your application
• lennhv: 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.

Answer 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}$')
 ); ''';