PostgreSQL with SQLAlchemy won’t allow empty entries

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.

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