Change SERIAL to IDENTITY in PostgreSQL

PostgreSQL 10 implements SQL standard's IDENTITY

In SQL Server is quite common to use IDENTITYs for non-natural primary keys. In PostgreSQL, until version 10, only SERIALs could be used for the same purpose. But that has changed.

Why INDENTITY and not SERIAL and SEQUENCES?

SERIAL is a friendly way to set a SEQUENCE, but at the end, it's a SEQUENCE: an object that doesn't belong to the table but sets a dependency with it. It's not a real data type. It's important to keep it in mind when using it because it has implications:
* Permissions must be given for the sequence, not only to the table.
* When dropping the SEQUENCE, first must be DROP the DEFAULT to the column and then DROP the SEQUENCE.
* When altering the next value, it has to be done in the SEQUENCE, not in the table.
* When cloning the table DDL, the same SEQUENCE would be used.

The new way creates the sequence as a proper internal dependency of the table, so that various weird implementation details of the serial pseudotype are not exposed.

IDENTITYs limitations

I really like the flexibility and the syntax of PostgreSQL's IDENTITY. But I got a surprise that PostgreSQL is more limited than SQL Server with data types that allow IDENTITY property when trying to use it with a NUMERIC data type:

The biggest data type possible is bigint (8 bytes), that has the range from -9223372036854775808 to +9223372036854775807. This means that NUMERIC(19) cannot be converter to IDENTITY and SERIAL must be used.

To find the columns that cannot be straightforward converted to IDENTITY:

For NUMERIC greater than 18 digits, IDENTITYs could be used if it's possible to downcast to BIGINT. This function, available in Github, checks for all NUMERIC greater than 18 digits if it's possible (returned column `downcast_possible') to convert to bigint:

CREATEORREPLACEFUNCTIONnumeric_serials_bigger_than_bigint(schema_nameregnamespace)/*Checks for all NUMERIC greater than 18 digits if it's possible to convert them to bigint (said in column 'downcast_possible')The function can check it in one schema or in all the database:- SELECT * FROM numeric_serials_bigger_than_bigint('my_schema');- SELECT * FROM numeric_serials_bigger_than_bigint(null);*/RETURNSTABLE(table_schemainformation_schema.sql_identifier,--regnamespace,table_nameinformation_schema.sql_identifier,--regclass,column_nameVARCHAR(1000),column_defaultVARCHAR(1000),is_nullableboolean,data_typeVARCHAR(50),numeric_precisionINT,numeric_scaleINT,n_live_tupNUMERIC(100),min_valueNUMERIC(100),max_valueNUMERIC(100),downcast_possibleboolean)LANGUAGEplpgsqlAS$$DECLAREmin_returnNUMERIC(100);max_returnNUMERIC(100);serial_rowRECORD;BEGINCREATETEMPORARYTABLEserials(table_schemainformation_schema.sql_identifierNOTNULL,--regnamespace NOT NULL,table_nameinformation_schema.sql_identifierNOTNULL,--regclass NOT NULL,column_nameVARCHAR(1000)NOTNULL,column_defaultVARCHAR(1000)NOTNULL,is_nullablebooleanNOTNULL,data_typeVARCHAR(50)NOTNULL,numeric_precisionINTNOTNULL,numeric_scaleINTNOTNULL,n_live_tupNUMERIC(100)NOTNULL,min_valueNUMERIC(100),max_valueNUMERIC(100),downcast_possibleboolean)ONCOMMITDROP;INSERTINTOserials(table_schema,table_name,column_name,column_default,is_nullable,data_type,numeric_precision,numeric_scale,n_live_tup)SELECTc.table_schema,c.table_name,c.column_name,c.column_default,CAST(c.is_nullableASboolean),c.data_type,c.numeric_precision,c.numeric_scale,t.n_live_tupFROMinformation_schema.columnscINNERJOINpg_stat_user_tablestONc.table_schema=t.schemanameANDc.table_name=t.relnameWHEREc.data_type='numeric'ANDc.numeric_precision>18ANDc.column_defaultLIKE'nextval(''%'ANDc.table_schema=COALESCE(CAST(schema_nameASinformation_schema.sql_identifier),c.table_schema)ORDERBYc.table_catalog,c.table_schema,c.table_name,c.column_name;FORserial_rowINSELECTs.table_schema,s.table_name,s.column_name,'SELECT MIN('||s.column_name||'), MAX('||s.column_name||') FROM '||s.table_schema||'.'||s.table_nameASsql_codeFROMserialssLOOPEXECUTEserial_row.sql_codeINTOmin_return,max_return;UPDATEserialssSETmin_value=min_return,max_value=max_return,downcast_possible=TRUEWHEREs.table_schema=serial_row.table_schemaANDs.table_name=serial_row.table_nameANDs.column_name=serial_row.column_name;UPDATEserialssSETdowncast_possible=(s.numeric_precision>LOG(s.max_value))WHEREs.max_valueISNOTNULL;ENDLOOP;RETURNQUERYSELECTs.table_schema,s.table_name,s.column_name,s.column_default,s.is_nullable,s.data_type,s.numeric_precision,s.numeric_scale,s.n_live_tup,s.min_value,s.max_value,s.downcast_possibleFROMserialss;END;$$;