I have spent a lot of time trying to figure this one out but just cannot seem to find anything. I want to allow empty entries in my database since not all fields are necessary and what is necessary will change depending on the input. I thought nullable entries could be empty…
This is the error I get out. Note that the values are just some dummy values I put in and have no significance. In the list of VALUES in the error, there is a little carrot pointing to the empty ” after ‘133’ as the issue:
(psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type real: “” LINE 1: …mit_date) VALUES (‘3645’, ‘654’, ‘Change’, ‘133’, ”, ‘0’, ‘…
Here is the code I have:
The Flask+SQLAlchemy:
class Request_Table(db.Model): __tablename__ = 'request_table' id = db.Column('id',db.Integer, primary_key=True) serial_num = db.Column('serial_num', db.String(10), nullable=False) meter_reading = db.Column('meter_reading', db.Float(5), nullable=False) request_type = db.Column('request_type', db.String(10), nullable=False) second_feet = db.Column('second_feet', db.Float(5), nullable=True) change_from = db.Column('change_from', db.Float(5), nullable=True) change_to = db.Column('change_to', db.Float(5), nullable=True) form_date = db.Column('form_date', db.String(10), nullable=False) laterals = db.Column('laterals', db.String(10), nullable=False) turnout = db.Column('turnout', db.String(10), nullable=True) remarks = db.Column('remarks', db.String(200), nullable=True) name = db.Column('name', db.String(20), nullable=True) submit_date = db.Column('submit_date', db.DateTime, default=datetime.utcnow) # Might not be needed but makes sense that it should be here. def __init__(self, id, serial_num : str = None, meter_reading : float = None, request_type : str = None, second_feet : float = None, change_from : float = None, change_to : float = None, form_date : str = None, laterals : str = None, turnout : str = None, remarks : str = None, name : str = None, submit_date : str = None): self.id = id self.serial_num = serial_num self.meter_reading = meter_reading self.request_type = request_type self.second_feet = second_feet self.change_from = change_from self.change_to = change_to self.form_date = form_date self.laterals = laterals self.turnout = turnout self.remarks = remarks self.name = name self.submit_date = submit_date # this is what gets returned when an entry is made def __repr__(self): return 'Success, I guess?'
And here is what PGAdmin says the code for the table is:
CREATE TABLE public.request_table ( id integer NOT NULL DEFAULT nextval('request_table_id_seq'::regclass), serial_num character varying(10) NOT NULL, meter_reading real NOT NULL, request_type character varying(10) NOT NULL, second_feet real, change_from real, change_to real, form_date character varying(10) NOT NULL, laterals character varying(10) NOT NULL, turnout character varying(10), remarks character varying(200), name character varying(20), submit_date timestamp without time zone, CONSTRAINT request_table_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE );
It seems like the fields I specified as nullable should allow for no entry, but when I try it fails. I only works when I fill in all fields. What am I doing wrong? Also, if anyone sees any really stupid formatting on my part (this is my first attempt at database stuff), let me know and I’ll fix it.
Advertisement
Answer
The problem actually appears to be that you are trying to insert a value of type String (Varchar) into a field of type Float (Real)
Try this:
INSERT INTO request_table (serial_num, meter_reading, request_type, form_date, laterals, submit_date) VALUES ("12345", 10.0, "POST", "2019-01-01", "Something", "2020-01-01");
or this:
Request_Table( id=123, serial_num='12345', meter_reading=10.0, request_type='Change', form_date='2019-01-01', laterals='something', submit_date='2020-01-01' )