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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: defaut value '1' in smallint column is a string value '1' inpgsql >= 9.5
Дата
Msg-id CAKFQuwYxwWQ8z7_nwXUmaXt87EhnLSkYa1VWKtHxNv0NsAczxw@mail.gmail.com
обсуждение исходный текст
Ответ на defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5  (Bogdan Stepanenko <bscheshir@gmail.com>)
Ответы Re: defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
On Fri, Dec 1, 2017 at 2:36 AM, Bogdan Stepanenko <bscheshir@gmail.com> wrote:
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: defaut value '1' in smallint column is a string value '1' inpgsql >= 9.5
Следующее
От: Tom Lane
Дата:
Сообщение: Re: defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5