获取PostgreSQL及GreenPlum表字段信息的方法

        在PostgreSQL和 GreenPlum中获取字段信息的方法是相同的，通过以下的SQL可以获取表字段的基本信息，但是由于serial这种特殊字段类型的存在，一条SQL无法获取完整信息，需要一些判断逻辑才能得到serial类型，为了减少重复造车，可以用Driver的PgResultSetMetaData 来方便的获取一些通过SQL无法直接获取的信息。

select
	c.relname table_name,
	a.attnum column_id,
	a.attname column_name,
	t.typname data_type,
	a.attlen numericPrecision,
	a.atttypmod lengthvar,
	a.attnotnull _notnull,
	b.description ordinalPosition
from
	pg_class c,
	pg_attribute a
left outer join pg_description b on
	a.attrelid = b.objoid
	and a.attnum = b.objsubid,
	pg_type t
where
	a.attnum > 0
	and a.attrelid = c.oid
	and a.atttypid = t.oid
	and c.relkind = 'r'
	and c.relname not like 'pg_%'
	and c.relname not like 'sql_%'
	and not exists (
	select
		1
	from
		pg_partitions
	where
		relname = partitiontablename)

            JdbcTemplate jdbc = new JdbcTemplate(datasource);
            String colSql = "select * from public.t_test_table";

			try {
				PgResultSetMetaData resultSetMetaData = jdbc.query(colSql, (ResultSetExtractor<PgResultSetMetaData>) rs -> (PgResultSetMetaData) rs.getMetaData());
				if(resultSetMetaData.getColumnCount() == 0){
					continue;
				}
				Method getField = PgResultSetMetaData.class.getDeclaredMethod("getField",int.class);
				getField.setAccessible(true);
				for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
					Field field = (Field) getField.invoke(resultSetMetaData,i);
					resultSetMetaData.getColumnName(i);
					resultSetMetaData.getColumnTypeName(i);
					field.getLength();
					resultSetMetaData.getPrecision(i);
					resultSetMetaData.isNullable(i);
					resultSetMetaData.isAutoIncrement(i);
				}
			}catch(Exception e){
				e.printStackTrace();
			}

送你一个大红包