> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-basics/data-types.md). # 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: ## 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') {% hint style="info" %} 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. ```sql SELECT * FROM pg_timezone_names ``` {% endhint %} Example including a week interval: ```sql 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](/ds-hub/sql/sql-data-manipulation.md) 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: {% hint style="info" %} Note that Array indexing starts at 1 (not 0) {% endhint %} ```sql 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 ```sql -- 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 ```sql SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'city'; ```
### 2.3. Displaying only the Column Names and the Data Types in a Table ```sql SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'city'; ```