From b26dfb95222fddd25322bdddf3a5a58d3392d8b1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 18 Sep 2002 21:35:25 +0000 Subject: Extend pg_cast castimplicit column to a three-way value; this allows us to be flexible about assignment casts without introducing ambiguity in operator/function resolution. Introduce a well-defined promotion hierarchy for numeric datatypes (int2->int4->int8->numeric->float4->float8). Change make_const to initially label numeric literals as int4, int8, or numeric (never float8 anymore). Explicitly mark Func and RelabelType nodes to indicate whether they came from a function call, explicit cast, or implicit cast; use this to do reverse-listing more accurately and without so many heuristics. Explicit casts to char, varchar, bit, varbit will truncate or pad without raising an error (the pre-7.2 behavior), while assigning to a column without any explicit cast will still raise an error for wrong-length data like 7.3. This more nearly follows the SQL spec than 7.2 behavior (we should be reporting a 'completion condition' in the explicit-cast cases, but we have no mechanism for that, so just do silent truncation). Fix some problems with enforcement of typmod for array elements; it didn't work at all in 'UPDATE ... SET array[n] = foo', for example. Provide a generalized array_length_coerce() function to replace the specialized per-array-type functions that used to be needed (and were missing for NUMERIC as well as all the datetime types). Add missing conversions int8<->float4, text<->numeric, oid<->int8. initdb forced. --- doc/src/sgml/catalogs.sgml | 27 ++++++++------ doc/src/sgml/datatype.sgml | 68 +++++++++++++++++++++++++---------- doc/src/sgml/ref/create_cast.sgml | 75 +++++++++++++++++++++++++++++---------- doc/src/sgml/release.sgml | 4 ++- doc/src/sgml/typeconv.sgml | 50 ++++++++++---------------- 5 files changed, 144 insertions(+), 80 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index a8fd81e19c9..01dfe6ad73b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ @@ -841,9 +841,8 @@ pg_cast - pg_cast stores data type conversion paths - defined with CREATE CAST plus the built-in - conversions. + pg_cast stores data type conversion paths, + both built-in paths and those defined with CREATE CAST. @@ -879,17 +878,25 @@ oidpg_proc.oid - The OID of the function to use to perform this cast. A 0 is - stored if the data types are binary compatible (that is, no - function is needed to perform the cast). + The OID of the function to use to perform this cast. Zero is + stored if the data types are binary coercible (that is, no + run-time operation is needed to perform the cast). - castimplicit - bool + castcontext + char - Indication whether this cast can be invoked implicitly + + Indicates what contexts the cast may be invoked in. + e means only as an explicit cast (using + CAST, ::, or function-call syntax). + a means implicitly in assignment + to a target column, as well as explicitly. + i means implicitly in expressions, as well as the + other cases. + diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 41ca3c00e53..28d3fcb7ede 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -823,8 +823,19 @@ CREATE TABLE tablename ( - Prior to PostgreSQL 7.2, strings that were too long were silently - truncated, no error was raised. + If one explicitly casts a value to + character(n) or character + varying(n), then an overlength value will + be truncated to n characters without raising an + error. (This too is required by the SQL standard.) + + + + + + Prior to PostgreSQL 7.2, strings that were too long were + always truncated without raising an error, in either explicit or + implicit casting contexts. @@ -897,12 +908,14 @@ INSERT INTO test2 VALUES ('ok'); INSERT INTO test2 VALUES ('good '); INSERT INTO test2 VALUES ('too long'); ERROR: value too long for type character varying(5) +INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation SELECT b, char_length(b) FROM test2; b | char_length -------+------------- ok | 2 good | 5 + too l | 5 @@ -932,7 +945,7 @@ SELECT b, char_length(b) FROM test2;
- Specialty Character Type + Specialty Character Types @@ -2832,29 +2845,39 @@ SELECT * FROM test1 WHERE a; Bit strings are strings of 1's and 0's. They can be used to store or visualize bit masks. There are two SQL bit types: - BIT(x) and BIT - VARYING(x); where - x is a positive integer. + BIT(n) and BIT + VARYING(n), where + n is a positive integer. BIT type data must match the length - x exactly; it is an error to attempt to - store shorter or longer bit strings. BIT VARYING is + n exactly; it is an error to attempt to + store shorter or longer bit strings. BIT VARYING data is of variable length up to the maximum length - x; longer strings will be rejected. - BIT without length is equivalent to - BIT(1), BIT VARYING without length + n; longer strings will be rejected. + Writing BIT without a length is equivalent to + BIT(1), while BIT VARYING without a length specification means unlimited length. - Prior to PostgreSQL 7.2, BIT type data was - zero-padded on the right. This was changed to comply with the - SQL standard. To implement zero-padded bit strings, a - combination of the concatenation operator and the - substring function can be used. + If one explicitly casts a bit-string value to + BIT(n), it will be truncated or + zero-padded on the right to be exactly n bits, + without raising an error. Similarly, + if one explicitly casts a bit-string value to + BIT VARYING(n), it will be truncated + on the right if it is more than n bits. + + + + + + Prior to PostgreSQL 7.2, BIT data was + always silently truncated or zero-padded on the right, with or without an + explicit cast. This was changed to comply with the SQL standard. @@ -2874,9 +2897,16 @@ CREATE TABLE test (a BIT(3), b BIT VARYING(5)); INSERT INTO test VALUES (B'101', B'00'); INSERT INTO test VALUES (B'10', B'101'); -ERROR: bit string length does not match type bit(3) +ERROR: Bit string length 2 does not match type BIT(3) + +INSERT INTO test VALUES (B'10'::bit(3), B'101'); +SELECT * FROM test; + + a | b +-----+----- + 101 | 00 + 100 | 101 -SELECT SUBSTRING(b FROM 1 FOR 2) FROM test; diff --git a/doc/src/sgml/ref/create_cast.sgml b/doc/src/sgml/ref/create_cast.sgml index bc9f71e566e..e64d696f81a 100644 --- a/doc/src/sgml/ref/create_cast.sgml +++ b/doc/src/sgml/ref/create_cast.sgml @@ -1,4 +1,4 @@ - + @@ -15,11 +15,11 @@ CREATE CAST (sourcetype AS targettype) WITH FUNCTION funcname (argtype) - [AS ASSIGNMENT] + [ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION - [AS ASSIGNMENT] + [ AS ASSIGNMENT | AS IMPLICIT ] @@ -49,20 +49,44 @@ SELECT CAST(42 AS text); - A cast can be marked AS ASSIGNMENT, which means that it - can be invoked implicitly in any context where the conversion it - defines is required. Cast functions not so marked can be invoked - only by explicit CAST, + By default, a cast can be invoked only by an explicit cast request, + that is an explicit CAST(x AS + typename), x::typename, or - typename(x) constructs. For - example, supposing that foo.f1 is a column of + typename(x) construct. + + + + If the cast is marked AS ASSIGNMENT then it can be invoked + implicitly when assigning 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 implicit + not. + (We generally use the term assignment + cast to describe this kind of cast.) + + + + If the cast is marked AS IMPLICIT then it can be invoked + implicitly in any context, whether assignment or internally in an + expression. For example, since || takes text + arguments, + +SELECT 'The time is ' || now(); + + will be allowed only if the cast from type timestamp to + text is marked AS IMPLICIT. Otherwise it + will be necessary to write one of + +SELECT 'The time is ' || CAST(now() AS text); +SELECT 'The time is ' || now()::text; + + (We generally use the term implicit cast to describe this kind of cast.) @@ -74,10 +98,11 @@ INSERT INTO foo(f1) VALUES(42); all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same - general type category. For example, int2 to - int4 casts can reasonably be implicit, but be wary of - marking int4 to text or - float8 to int4 as implicit casts. + general type category. For example, the cast from int2 to + int4 can reasonably be implicit, but the cast from + float8 to int4 should probably be + assignment-only. Cross-type-category casts, such as text + to int4, are best made explicit-only. @@ -138,7 +163,18 @@ INSERT INTO foo(f1) VALUES(42); - Indicates that the cast may be invoked implicitly. + Indicates that the cast may be invoked implicitly in assignment + contexts. + + + + + + AS IMPLICIT + + + + Indicates that the cast may be invoked implicitly in any context. @@ -163,10 +199,10 @@ INSERT INTO foo(f1) VALUES(42); data type, returned that data type, and took one argument of a different type was automatically a cast function. This convention has been abandoned in face of the introduction of schemas and to be - able to represent binary compatible casts in the catalogs. The built-in + able to represent binary compatible casts in the catalogs. (The built-in cast functions - still follow this naming scheme, but they have to be declared as - casts explicitly now. + still follow this naming scheme, but they have to be shown as + casts in pg_cast now.) @@ -191,7 +227,8 @@ CREATE CAST (text AS int4) WITH FUNCTION int4(text); The CREATE CAST command conforms to SQL99, except that SQL99 does not make provisions for binary compatible - types. + types. AS IMPLICIT is a PostgreSQL + extension, too. diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index b7f2b4be71b..f373cc6e25d 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -24,6 +24,8 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. --> -If one or more non-unknown inputs are of a preferred type in that category, -resolve as that type. - - - - -Otherwise, resolve as the type of the first non-unknown input. +Choose the first non-unknown input type which is a preferred type in +that category or allows all the non-unknown inputs to be implicitly +coerced to it. @@ -842,15 +838,16 @@ Here, the unknown-type literal 'b' will be resolved as type t -tgl=> SELECT 1.2 AS "Double" UNION SELECT 1; - Double --------- - 1 - 1.2 +tgl=> SELECT 1.2 AS "Numeric" UNION SELECT 1; + Numeric +--------- + 1 + 1.2 (2 rows) -The literal 1.2 is of type double precision, -the preferred type in the numeric category, so that type is used. +The literal 1.2 is of type numeric, +and the integer value 1 can be cast implicitly to +numeric, so that type is used. @@ -858,27 +855,18 @@ the preferred type in the numeric category, so that type is used. Type Conversion in a Transposed Union -Here the output type of the union is forced to match the type of -the first clause in the union: - -tgl=> SELECT 1 AS "All integers" +tgl=> SELECT 1 AS "Real" tgl-> UNION SELECT CAST('2.2' AS REAL); - All integers --------------- - 1 - 2 + Real +------ + 1 + 2.2 (2 rows) - - -Since REAL is not a preferred type, the parser sees no reason -to select it over INTEGER (which is what the 1 is), and instead -falls back on the use-the-first-alternative rule. -This example demonstrates that the preferred-type mechanism doesn't encode -as much information as we'd like. Future versions of -PostgreSQL may support a more general notion of -type preferences. +Here, since type real cannot be implicitly cast to integer, +but integer can be implicitly cast to real, the union +result type is resolved as real. -- cgit v1.2.3