10

I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this:

column_name | data_type
------------+--------------
gid         | integer
descr       | character varying(32)
class       | character varying(10)
area        | double precision
geom        | geometry(Polygon,3763)

From a few answers on stackexchange and gis.stackexchange I know that I can get some of the information with the following query:

SELECT 
    g.column_name,
    g.data_type,
    g.character_maximum_length,
    g.udt_name,
    f.type,
    f.srid
FROM 
     information_schema.columns as g JOIN
     geometry_columns AS f 
         ON (g.table_schema = f.f_table_schema and g.table_name = f.f_table_name )
WHERE
    table_schema = 'my_schema_name' and
    table_name = 'my_table_name'

Result:

column_name | data_type         | character_maximum_length | udt_name | type    | srid
------------+-------------------+--------------------------+----------+---------+------
gid         | integer           |                          |          |         |
descr       | character varying | 32                       |          |         |
class       | character varying | 10                       |          |         |
area        | double precision  |                          |
geom        | USER-DEFINED      |                          | geometry | Polygon | 3763

But, is there a proper more practical way to retrieve the information in the format I need? Or must I enter the "world" of CASE WHEN structures and string concatenation to gather all column attributes in one single column in that format?

My fear is if a non expected data type surprises me by needing other attribute from the information_schema.columns table. I.e, in the example table before, I did not used any numeric (15,2) data type, that would need to use another attributes (numeric_precision and numeric_scale) to be parsed by a CASE WHEN.

15

The theory yes, though you could find it very complex indeed.

  • Every table (select * from pg_class) has columns.
  • Every column (select * from pg_attribute) optionally has a "typmod" number.
  • For types with typmod (select * from pg_type) there will be a "typmodout" function.
  • Running the typmod out function on a typmod number will return a string that can be concatenated with the type name to form the kind of user-readable signature you're used to (select 'numeric' || numerictypmodout(786441)) (select geography_typmod_out(1107460))

But, hey, psql generates the strings you want, if we look at what SQL it generates, maybe the answer is in there.

Sure enough, there is a magic function that takes a typeid and typmod and returns the magic string.

select a.attname, format_type(a.atttypid, a.atttypmod) from pg_attribute a where attname = 'geog';

With a join to pg_class you should be able to get this info per-table.

1
  • I don't get any results for where attname = 'geog' but = 'geom' does work. This gives me good results for MultiPolygon, Point and MultiPoint values but I don't see anything for Line or MultiLine types. Are those considered polygons?
    – mhkeller
    Sep 28 '18 at 6:40
7

It can be get using a simple SQL query.

SELECT * from information_schema.columns where table_name='mytablename'

1
  • 1
    This works great! And here's a tip: The output can be kind of long, so you may want to enable the expanded display on your console: \pset pager to turn off the page, then \x to enable the expanded display.
    – modulitos
    Mar 9 '17 at 18:45
7

With Paul Ramsey help I made it this way:

SELECT a.attname as column_name, format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_attribute a
JOIN pg_class b ON (a.attrelid = b.relfilenode)
WHERE b.relname = 'my_table_name' and a.attstattarget = -1;

UPDATE

Meanwhile I have created a function to ask for a certain column data type

CREATE OR REPLACE FUNCTION "vsr_get_data_type"(_t regclass, _c text)
  RETURNS text AS
$body$
DECLARE
    _schema text;
    _table text;
    data_type text;
BEGIN
-- Prepare names to use in index and trigger names
IF _t::text LIKE '%.%' THEN
    _schema := regexp_replace (split_part(_t::text, '.', 1),'"','','g');
    _table := regexp_replace (split_part(_t::text, '.', 2),'"','','g');
    ELSE
        _schema := 'public';
        _table := regexp_replace(_t::text,'"','','g');
    END IF;

    data_type := 
    (
        SELECT format_type(a.atttypid, a.atttypmod)
        FROM pg_attribute a 
        JOIN pg_class b ON (a.attrelid = b.oid)
        JOIN pg_namespace c ON (c.oid = b.relnamespace)
        WHERE
            b.relname = _table AND
            c.nspname = _schema AND
            a.attname = _c
     );

    RETURN data_type;
END
$body$ LANGUAGE plpgsql;

The usage is:

SELECT vsr_get_data_type('schema_name.table_name','column_name')
-1

if you want to check geometry type you can check 'udt_name' in 'INFORMATION_SCHEMA.COLUMNS' and use it!:

select column_name,udt_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name =g

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.