Re: defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5

On Fri, Dec 1, 2017 at 2:36 AM, Bogdan Stepanenko <bscheshir(at)gmail(dot)com>
wrote:

> related
> https://github.com/yiisoft/yii2/issues/15247
> https://github.com/yiisoft/yii2/issues/15254
>
> 1. create table with
>
> CREATE TABLE "type" (
> int_col integer NOT NULL,
> int_col2 integer DEFAULT '1',
> smallint_col smallint DEFAULT '1',
> char_col char(100) NOT NULL,
> char_col2 varchar(100) DEFAULT 'something',
> char_col3 text,
> float_col double precision NOT NULL,
> float_col2 double precision DEFAULT '1.23',
> blob_col bytea,
> numeric_col decimal(5,2) DEFAULT '33.22',
> time timestamp NOT NULL DEFAULT '2002-01-01 00:00:00',
> bool_col boolean NOT NULL,
> bool_col2 boolean DEFAULT TRUE,
> ts_default TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
> bit_col BIT(8) NOT NULL DEFAULT B'10000010',
> bigint_col BIGINT
> );
>
> 2. Get declaration of `types` from the information schema throw PDO
> 3. Get data about `smallint_col`
> Result: Default value of `smalint_col` is a raw string '1', not convert to
> int 1
>
> I try with this versions (official docker images like `image:
> postgres:9.4`):
> 9.4 is convert string to int.
> 9.5 and 10.1 is not convert string to int.
>

​I've updated a relevant yii2 pull request here:

https://github.com/yiisoft/yii2/pull/15266?_pjax=%23js-repo-pjax-container

Minimal example, 9.3 vs 9.6

CREATE TABLE st_tbl ( si smallint default '1' );

SELECT
d.nspname AS table_schema,
c.relname AS table_name,
a.attname AS column_name,
t.typname AS data_type,
CAST(pg_get_expr(ad.adbin, ad.adrelid) AS varchar) AS column_default
FROM
pg_class c
LEFT JOIN pg_attribute a ON a.attrelid = c.oid
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum =
ad.adnum
LEFT JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_namespace d ON d.oid = c.relnamespace
LEFT join pg_constraint ct on ct.conrelid=c.oid and ct.contype='p'
WHERE
a.attnum > 0 and t.typname != ''
and c.relname = 'st_tbl'
and d.nspname = 'public'
ORDER BY
a.attnum;

9.3: <1::smallint>
9.6: <'1'::smallint>

(defining the default as simply <1> in create table results in <1> being
output for the query above)

There are single quotes present in 9.6 whereas they were not present in 9.3.

If nothing else a release note patch is probably warranted but while my
first reaction is that 9.6 is the correct output I can find argument for
the 9.3 result as well. A hacker is going to need to chime in as to our
broader "default expression interpretation rules" here. e.g., "now()" is
resolved at CREATE TABLE but "current_timestamp" is resolve during
insert...the former supports the 9.3 behavior while the later supports the
9.6 behavior.

David J.