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