Error when adding column with numeric name

Question

I currently have a MariaDB database with columns named after dates : 20200105, 20200914 etc. If I try to add a column using ALTER TABLE dates ADD COLUMN IF NOT EXISTS (test VARCHAR(255));, it works and the test column is created.

If I type ALTER TABLE dates ADD COLUMN IF NOT EXISTS (20201205 VARCHAR(255));, though (so, with a number replacing "test"), the creation does not work anymore and MariaDB tells me that there is an error with my SQL syntax. I have tried to put quotes around the column name, but that does not work (not even with "test").

Is there something obvious I am missing ?

Did you try using ` ?

palindrom
– palindrom

2021-09-07 10:07:58 +00:00
Commented Sep 7, 2021 at 10:07 — palindrom
– palindrom, Commented Sep 7, 2021 at 10:07

Tim Biegeleisen · Accepted Answer · 2021-09-07 10:08:13Z

1

Use backticks to escape the column name:

ALTER TABLE dates ADD COLUMN IF NOT EXISTS (`20201205` VARCHAR(255));

But really best practice frowns upon the use of naming your database objects with mandatory backticks. The reason for using a name like 20201205 as a column name is that you will forever be needing to escape it using backticks. Also, from a data design point of view, your data should grow with new dates in terms of increasing the number of records, not columns.

answered Sep 7, 2021 at 10:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Coding Penguin Over a year ago

Totally working, thanks ! That's right, but these dates actually correspond to versions (versions named after the date they are released), and my records are the number of devices in each version :) I'll wait for the mandatory 15 minutes then mark the answer as solving

Tim Biegeleisen Over a year ago

@TheCodingPenguin Then in that case disregard my rant about date column names and good design -- it sounds as though you've vetted well your table design already.

Collectives™ on Stack Overflow

Error when adding column with numeric name

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related