Data Types
This section will cover common datatypes in PostgreSQL and show us how to determine the data type of a column in an existing table. For detailed list of data types, please visit: https://www.postgresql.org/docs/current/datatype.html
1. Common Data Types
1.1. Character Data Types
CHAR
(fixed length)VARCHAR
(variable-length with limit)TEXT
(unlimited length)
1.2. Numeric Data Types
INT
(4 bytes)DECIMAL
(variable)SERIAL
(2 bytes)

1.3. Date/Time Data Types
DATE
(stores date data. ISO 3601 format standard: 'yyyy-mm-dd'; e.g. '2024-01-01'),TIME
(stores time data with microsecond precision with or without time zone, ex '03:15:33.467'),TIMESTAMP
(stores date and time data with or without timezone. e.g. '2023-01-21 02:06:11.86123+00' )INTERVAL
(stores date and time data as a period of time in years, months, days, hours, seconds, etc. e.g. '7 days')
Example including a week interval:
SELECT
rental_date,
-- Calculate the 7-day return date
rental_date + INTERVAL '7 days' AS return_date
FROM books;
For more data manipulation examples check out SQL Data Wrangling page.
1.4. Arrays
Much like arrays in typical programming languages, we have the capability to generate multi-dimensional arrays with different lengths for any native data type. To illustrate how to use arrays let us first create a table and populate it:
CREATE TABLE students (
student_id int,
full_name varchar(100),
email text[][], -- array of email type (personal, student) and email addresses
scores int[] -- array of exam scores: math, physics, chemistry, biology
);
INSERT INTO students VALUES (1, 'Keanu Frees', '{{"personal","keanu.frees@gmail.com"},{"student","kf@dshub.edu"}}', '{98,95,100,91}');
INSERT INTO students VALUES (2, 'Natalie Parton', '{{"personal","natalie.parton@gmail.com"},{"student","np24@dshub.edu"}}', '{87,85,93,90}');
INSERT INTO students VALUES (3, 'SJ Brown', '{{ARRAY[NULL],ARRAY[NULL]},{"student","sj@dshub.edu"}}', '{100,99,89,95}');
INSERT INTO students VALUES (4, 'Morgan Freewill', '{{"personal","morgan.rebel@gmail.com"},{"student","morganr@dshub.edu"}}', '{93,94,91,97}');
-- Accessing ARRAYs
SELECT
student_id,
full_name,
email[1][1] AS email_type,
email[1][2] AS email_address,
scores[1] AS math_score
FROM student_arrays
-- 1) Filter only students with 'personal' emails
-- WHERE email[1][1]='personal';
-- or
-- WHERE 'personal' = ANY(email);
-- or with 'contains operator @>'
WHERE email @> ARRAY['personal'];
/* OUTPUT
student_id full_name email_type email_address math_score
1 Keanu Frees personal keanu.frees@gmail.com 98
2 Natalie Parton personal natalie.parton@gmail.com 87
4 Morgan Freewill personal morgan.freewill@gmail.com 93
*/
SELECT
student_id,
full_name,
email[1][1] AS email_type,
email[1][2] AS email_address,
scores[1] AS math_score
FROM student_arrays
-- Filter students with 'student' emails
WHERE email[1][1]='student';
/* OUTPUT
NULL because indexing is incorrent
*/
2. Displaying Existing Tables' Columns and Their DataTypes
2.1. Displaying All TABLES existed in a Database
-- Select all columns from the TABLES system database
SELECT *
FROM INFORMATION_SCHEMA.TABLES
-- Filter by schema
WHERE table_schema = 'public'
-- Sort by table name
ORDER BY table_name;

2.2. Displaying All COLUMNs in a Table
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'city';

2.3. Displaying only the Column Names and the Data Types in a Table
SELECT
column_name,
data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'city';

Last updated
Was this helpful?