Select datatype of the field in postgres

How do I get datatype of specific field from table in postgres ? For example I have the following table, student_details ( stu_id integer, stu_name varchar(30 ), joined_date timestamp );

In this using the field name / or any other way, I need to get the datatype of the specific field. Is there any possibility ?

You can get data types from the information_schema (8.4 docs referenced here, but this is not a new feature):

=# select column_name, data_type from information_schema.columns
-# where table_name = 'config';
    column_name     | data_type 
--------------------+-----------
 id                 | integer
 default_printer_id | integer
 master_host_enable | boolean
(3 rows)

You can use the pg_typeof() function, which also works well for arbitrary values.

SELECT pg_typeof("stu_id"), pg_typeof(100) from student_details limit 1;

Try this request :

SELECT column_name, data_type FROM information_schema.columns WHERE 
table_name = 'YOUR_TABLE' AND column_name = 'YOUR_FIELD';

run psql -E and then \d student_details

If you like 'Mike Sherrill' solution but don't want to use psql, I used this query to get the missing information:

select column_name,
case 
	when domain_name is not null then domain_name
	when data_type='character varying' THEN 'varchar('||character_maximum_length||')'
	when data_type='numeric' THEN 'numeric('||numeric_precision||','||numeric_scale||')'
	else data_type
end as myType
from information_schema.columns
where table_name='test'

with result:

column_name |     myType
-------------+-------------------
 test_id     | test_domain
 test_vc     | varchar(15)
 test_n      | numeric(15,3)
 big_n       | bigint
 ip_addr     | inet

The information schema views and pg_typeof() return incomplete type information. Of these answers, psql gives the most precise type information. (The OP might not need such precise information, but should know the limitations.)

create domain test_domain as varchar(15);

create table test (
  test_id test_domain, 
  test_vc varchar(15), 
  test_n numeric(15, 3), 
  big_n bigint,
  ip_addr inet
);

Using psql and \d public.test correctly shows the use of the data type test_domain, the length of varchar(n) columns, and the precision and scale of numeric(p, s) columns.

sandbox=# \d public.test
Table "public.test"
Column  |         Type          | Modifiers
---------+-----------------------+-----------
test_id | test_domain           |
test_vc | character varying(15) |
test_n  | numeric(15,3)         |
big_n   | bigint                |
ip_addr | inet                  |

This query against an information_schema view does not show the use of test_domain at all. It also doesn't report the details of varchar(n) and numeric(p, s) columns.

select column_name, data_type 
from information_schema.columns 
where table_catalog = 'sandbox'
  and table_schema = 'public'
  and table_name = 'test';

column_name |     data_type
-------------+-------------------
test_id     | character varying
test_vc     | character varying
test_n      | numeric
big_n       | bigint
ip_addr     | inet

You might be able to get all that information by joining other information_schema views, or by querying the system tables directly. psql -E might help with that.

The function pg_typeof() correctly shows the use of test_domain, but doesn't report the details of varchar(n) and numeric(p, s) columns.

select pg_typeof(test_id) as test_id, 
       pg_typeof(test_vc) as test_vc,
       pg_typeof(test_n) as test_n,
       pg_typeof(big_n) as big_n,
       pg_typeof(ip_addr) as ip_addr
from test;

test_id   |      test_vc      | test_n  | big_n  | ip_addr
-------------+-------------------+---------+--------+---------
test_domain | character varying | numeric | bigint | inet

Pulling data type from information_schema is possible, but not convenient (requires joining several columns with a case statement). Alternatively one can use format_type built-in function to do that, but it works on internal type identifiers that are visible in pg_attribute but not in information_schema. Example

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 a.attnum > 0 -- hide internal columns
AND NOT a.attisdropped -- hide deleted columns
AND b.oid = 'my_table'::regclass::oid; -- example way to find pg_class entry for a table

Based on https://gis.stackexchange.com/a/97834.

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

> \gdesc Shows the description (that is, the column names and data > types) of the result of the current query buffer. The query is not > actually executed; however, if it contains some type of syntax error, > that error will be reported in the normal way. > > If the current query buffer is empty, the most recently sent query is > described instead.

So you can table student_details limit 0 \gdesc Output occupied less space than \d