0

I have been using Flask-SQLAlchemy with the help of this class:

class Test(db.Model):

    __tablename__ = "test"

    id = db.Column(db.Integer, primary_key=True, unique=True, nullable=False)
    name = db.Column(db.String(127), nullable=False)
    startDate = db.Column(db.Date, nullable=False)

The following SQL code generated on a PostgreSQL database:

CREATE TABLE IF NOT EXISTS public.test
(
    id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    name character varying(127) COLLATE pg_catalog."default" NOT NULL,
    "startDate" date NOT NULL,

    CONSTRAINT test_pkey PRIMARY KEY (id)

    -- and so forth.......

But what I don't understand is: Why is startDate in quotes ?

funnydman
  • 9,083
  • 4
  • 40
  • 55
ChsharpNewbie
  • 1,040
  • 3
  • 9
  • 21
  • postgres uses double quotes to escape columns – nbk Aug 19 '22 at 14:14
  • 2
    Just guessing, but unquoted identifers are case-insensitive. PostgreSQL stores unquoted identifiers in lowercase (other DBMSes uppercase), so when you specified `startDate`, it probably infers you want a case-sensitive identifier, and hence it gets quoted. Reading [Snowflake table created with SQLAlchemy requires quotes ("") to query](https://stackoverflow.com/questions/68307354/snowflake-table-created-with-sqlalchemy-requires-quotes-to-query) suggests that SQLAlchemy itself considers lowercase case-insensitive, so it will automatically generate quoted identifiers if you have uppercase. – Mark Rotteveel Aug 19 '22 at 14:16
  • @MarkRotteveel Yes that may well be true, I wrote everything in lower case and then there were no quotation marks. – ChsharpNewbie Aug 19 '22 at 14:21
  • @nbk not just Postgres - that's how this is defined by the SQL standard –  Aug 19 '22 at 14:53
  • 1
    @a_horse_with_no_name mysql my most use databse doesn't support double quotes for esacping column names, the rest i believe does, but i don't know. – nbk Aug 19 '22 at 14:54
  • @nbk: you can tell MySQL to comply (at least a bit) with the SQL standard. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d20dd7c46d76d57b32af0aca1fb954f7 –  Aug 19 '22 at 15:04
  • @a_horse_with_no_name there hundreds of hopefully closed question why `"columname" int` that is why we refer to https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – nbk Aug 19 '22 at 15:21

0 Answers0