Select datatype of the field in postgres

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

There are numerous ways to find the datatypes of the specific field or all the fields from table. Following is the list of all the ways.

• Using pg_typeof() Function
• Using \d Command
• Using SELECT Statement
• Using information_schema

Using pg_typeof() Function

pg_typeof() Function is a built-in function, it accepts column name as an argument and returns the data type.

Example

SELECT pg_typeof(bike_launch_date)
FROM public.bike_details
LIMIT 1;

LIMIT is set to 1 for avoiding repetitions.

Using \d Command

run the following command accompanied by the table name in order to get the data types of all the columns along with other information.

Example

\d table_name;

To further explore more remaining methods or get better understanding, explore the following article: How to Check Column Types in PostgreSQL

The pg_typeof() method, as illustrated above:

SELECT pg_typeof(bike_launch_date)
FROM public.bike_details
LIMIT 1;

...has one downside: it will fail if bike_details is empty, as the query will return 0 rows.

One way to handle this is:

SELECT pg_typeof(bike_launch_date)
FROM (select) t
LEFT JOIN public.bike_details ON true
LIMIT 1;

The subselect t will return 1 row with 0 columns, so left outer joining our table of interest is guaranteed to return at least 1 row. In combination with LIMIT 1, this will produce exactly 1 row whether the bike_details table is empty or not, and will yield the correct result.