sql: implement the column numeric_precision_radix in information_schema.columns

[techknowlogick]

As documented here: https://www.postgresql.org/docs/10/static/infoschema-columns.html

numeric_precision_radix | cardinal_number | If data_type identifies a numeric type, this column indicates in which base the values in the columns numeric_precision and numeric_scale are expressed. The value is either 2 or 10. For all other data types, this column is null.

This is used by go-xorm and go-gitea, for example using the following query:

SELECT column_name, column_default, is_nullable, data_type, character_maximum_length, numeric_precision, numeric_precision_radix ,
    CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
    CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
FROM pg_attribute f
    JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
WHERE c.relkind = 'r'::char AND f.attnum > 0 ORDER BY f.attnum

Linked GH Issues:
go-gitea/gitea#3765
go-xorm/xorm#802

[knz]

Unfortunately the column numeric_precision_radix is implemented for information_schema.sequences but not information_schema.columns which your query is using. This is why the query fails.

Arguably we should add it. I'll alter the description of the issue accordingly.

[knz]

cc @awoods187 for prioritization.
XOrm ( http://xorm.io/ ) is an ORM for Go.

[knz]

@techknowlogick There is a possible workaround though. In CockroachDB the only type that uses the radix 10 is decimal (also called numeric) and every other type uses radix 2. (this needs to be checked in the source code of CockroachDB) Is there any way you can use a CASE expression to determine this in your query instead of using numeric_precision_radix?

[techknowlogick]

Thanks @knz.

I've been creating test tables in both postgres (specifically 9.6) and cockroachDB (v2.0), and found your assumption to likely be correct (but this was done very unscientifically and still needs more rigid confirmation).

Going forward with that assumption I've come up with the following CASE that I'll try with XOrm, and I'll run it through its tests.

CASE 
 	 WHEN numeric_precision IS NOT NULL AND lower(data_type) = 'decimal' THEN 10
 	 WHEN numeric_precision IS NOT NULL AND lower(data_type) != 'decimal' THEN 2
 	 ELSE NULL
 	 END AS numeric_precision_radix,

Then of course when this ticket is completed the above case can be reverted in xorm.

[pkit]

@techknowlogick created PR that adds radix to columns schema: #28467