[em500]

> I'm also horrified at the auto-conversions that I've just learned about from this link that I'm not protected from.

Most of the common databases that I'm aware of (PostgreSQL, MySQL, SQL Server) do implicit type conversion on insertion (as well as in in many other places). I haven't checked this, but it wouldn't surprise me if that's actually ANSI SQL standard behavior.

[jeltz]

PosttgreSQL has only very limited implicit type conversion (e.g. only between different text types and between different integer types) so I presume that what you refer to is how single quoted SQL literals are untyped and their content will be parsed depending on e.g. what you try to insert them into.

    $ CREATE TABLE t (x int);
    CREATE TABLE
    $ INSERT INTO t (x) VALUES (42);
    INSERT 0 1
    $ INSERT INTO t (x) VALUES ('42');
    INSERT 0 1
    $ INSERT INTO t (x) VALUES (CAST('42' AS text));
    ERROR:  column "x" is of type integer but expression is of type text
    LINE 1: INSERT INTO t (x) VALUES (CAST('42' AS text));
                                           ^
    HINT:  You will need to rewrite or cast the expression.
    $ INSERT INTO t (x) VALUES ('42a');
    ERROR:  invalid input syntax for type integer: "42a"
    LINE 1: INSERT INTO t (x) VALUES ('42a');

    $ INSERT INTO t (x) VALUES (CAST(42 AS bigint));
    INSERT 0 1

[derefr]

ANSI SQL provides “CREATE CAST […] AS ASSIGNMENT”, which will define a cast that gets used implicitly to get from type X to type Y when you have a tuple-field (e.g. table-row column, composite-value member field, stored-proc parameter) of type Y, and an expression-value being passed into it of type X.

Quoting Postgres docs (because ain’t nobody paying for the ANSI SQL standard just to quote it):

> If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type. For example, supposing that foo.f1 is a column of type text, then:

    INSERT INTO foo (f1) VALUES (42);

> will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT, otherwise not. (We generally use the term assignment cast to describe this kind of cast.)

Presumably, in any ANSI SQL-compliant DBMS, you could redefine whatever assignment casts are annoying you to be non-assignment casts. (AFAIK, there’s no ANSI SQL standardization on what casts must be assignment casts; so lowest-common-denominator SQL-standard-compliant queries shouldn’t be relying on the presence of any defined assignment casts.)

[masklinn]

Isn’t what they are referring to more that quoted literals are untyped and the db engine will attempt interpret them based on the target / context, with various levels of flexibility (discarding or munging data) depending on the engine?