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)

Numeric Data Type List (Credit: postgresql.org)

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')

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

To check the timezones, abbreviations, offsets from the UTC, and whether the timezone follows daylight saving time procedure, you can query PostGRES' pg_timezone_names table.

SELECT * FROM pg_timezone_names

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:

Note that Array indexing starts at 1 (not 0)

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, '{{"personal","[e-mail]"},{"student","[e-mail]"}}', '{98,95,100,91}');
INSERT INTO students VALUES (2, '{{"personal","[e-mail]"},{"student","[e-mail]"}}', '{87,85,93,90}');
INSERT INTO students VALUES (3, '{{ARRAY[NULL],"student","[e-mail]"}}', '{100,99,89,95}');
INSERT INTO students VALUES (4, '{{"personal","[e-mail]"},{"student","[e-mail]"}}', '{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	[e-mail]		98
2	Natalie Parton	personal	[e-mail]	87
4	Morgan Freewill	personal	[e-mail]	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';