find column number in a SQL table in IBM netezza database

Question

I would like to know the column number in a SQL data table in IBM netezza.

I do not find it in the following sql query:

 select *
 from _v_obj_relation_xdb
 join _v_sys_object_dslice_info on (objid = tblid)
 where objname like 'my_table'

Any help would be appreciated.

thanks !

Answer 1

You can get that information by joining the _v_table and _v_relation_column views.

SELECT tablename,
   attname       AS COL_NAME,
   b.FORMAT_TYPE AS COL_TYPE,
   attnum        AS COL_NUM
FROM _v_table a
   JOIN _v_relation_column b
   ON a.objid   = b.objid
WHERE tablename = 'TEST1'
ORDER BY attnum;

 TABLENAME | COL_NAME | COL_NUM
-----------+----------+---------
 TEST1     | COL1     |       1
 TEST1     | COL2     |       2
 TEST1     | COL3     |       3
 TEST1     | SEQ_COL  |       4
(4 rows)