# 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'; ```
--- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://dshub.gitbook.io/ds-hub/sql/sql-basics/data-types.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.