On 5/10/24 07:04, Martin Simmons wrote:
On Thu, 9 May 2024 14:45:31 -0400, Phil Stracchino said:
I'm seeing a LOT of columns either defined with a DEFAULT but allowed to
be NULL (in which case the DEFAULT will not be used because NULL is
allowed),
Are you sure the DEFAULT will not be used? I think it will be used if the
column is omitted in an INSERT. If you want to insert NULL and there is a non
NULL DEFAULT then you need to provide NULL for that column explicity in the
INSERT.
OK, so, I can't speak to the internals of PostgreSQL and don't wish to.
But I did just recheck in MariaDB (10.6.17). I've never tried
specifying a DEFAULT value *WITHOUT* NOT NULL, because why would you,
but I just re-checked, and somewhat to my surprise, that is in fact
valid. (I expected weird behavior.)
This is the documented/tested behavior for missing values in MariaDB
10.6 at least, which matches the *documented* behavior of MySQL 8.x:
Column declared with DEFAULT but neither NULL nor NOT NULL:
— Implicit default for the data type is used
— NULLs can be manually inserted
Column declared NULL with no DEFAULT
— NULLs can be manually inserted
— If STRICT SQL is in effect, missing value inserts NULL
— If STRICT is not in effect, the implicit default for data type is used
Column declared with neither NULL/NOT NULL nor DEFAULT
— As for previous case, as far as I can tell
Column declared NOT NULL without DEFAULT (SQL99 violation)
— Missing value creates an error
— NULL creates an error
It should go without saying that STRICT_ALL_TABLES *should* ideally
always be in use. But a lot of times, you *can't* because some
application or another uses non-compliant SQL and breaks with STRICT in
use. And it also should go without saying that best practice is to
explicitly declare whether NULLs are allowed in the column instead of
relying on what the database internals do when it is not specified.
(MySQL 8 and I think MariaDB 10 clamped down a lot on what SQL99
violations were ... let's say tolerated.)
--
Phil Stracchino
Fenian House Publishing
ph...@caerllewys.net
p...@co.ordinate.org
Landline: +1.603.293.8485
Mobile: +1.603.998.6958
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
