Thread: defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5
defaut value '1' in smallint column is a string value '1' in pgsql >= 9.5
From
Bogdan Stepanenko
Date:
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.
Re: defaut value '1' in smallint column is a string value '1' inpgsql >= 9.5
From
Bruce Momjian
Date:
I see you have posted three bug reports related to PDO but the Postgres community has no idea how your report relates to a bug in Postgres. --------------------------------------------------------------------------- On Fri, Dec 1, 2017 at 12:36:48PM +0300, Bogdan Stepanenko 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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: defaut value '1' in smallint column is a string value '1' inpgsql >= 9.5
From
"David G. Johnston"
Date:
9.5 and 10.1 is not convert string to int.I try with this versions (official docker images like `image: postgres:9.4`):Result: Default value of `smalint_col` is a raw string '1', not convert to int 13. Get data about `smallint_col`1. create table with2. Get declaration of `types` from the information schema throw PDOCREATE 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
);
9.4 is convert string to int.
I've updated a relevant yii2 pull request here:
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 9.3: <1::smallint>
> 9.6: <'1'::smallint>
Oh, right, this was an intentional change awhile back. The form without
the quotes is actually an incorrect representation of the internal state:
what that produces, if you feed it back into the parser, is an int4
constant with a run-time conversion to int2. The runtime conversion will
get const-folded during planning, resulting in more or less the same
behavior; but it's nonetheless a different expression tree from the second
form, which is an int2 constant, full stop.
(digs...) It was changed in this 9.5-era commit:
commit 542320c2bd0b3796a8a9a4617cdb23fbad473390
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon Mar 30 14:59:49 2015 -0400
Be more careful about printing constants in ruleutils.c.
The previous coding in get_const_expr() tried to avoid quoting integer,
float, and numeric literals if at all possible. While that looks nice,
it means that dumped expressions might re-parse to something that's
semantically equivalent but not the exact same parsetree; for example
a FLOAT8 constant would re-parse as a NUMERIC constant with a cast to
FLOAT8. Though the result would be the same after constant-folding,
this is problematic in certain contexts. In particular, Jeff Davis
pointed out that this could cause unexpected failures in ALTER INHERIT
operations because of child tables having not-exactly-equivalent CHECK
expressions. Therefore, favor correctness over legibility and dump
such constants in quotes except in the limited cases where they'll
be interpreted as the same type even without any casting.
This results in assorted small changes in the regression test outputs,
and will affect display of user-defined views and rules similarly.
The odds of that causing problems in the field seem non-negligible;
given the lack of previous complaints, it seems best not to change
this in the back branches.
I don't see anything about this commit in the 9.5 release notes, which
perhaps is an oversight.
regards, tom lane