POSTGRESQL DATA TYPES FOR DATA ANALYSIS

POSTGRESQL better known as postgres is an advanced open source object relational database management system that combines the power of relational databases and the flexibility of object oriented programming.

In this article I will look at the data types associated with postgres, a summary and the best practices on table creation.

Data Types

These are the elements outlined in John Gruber’s original design document. All Markdown applications support these elements.

TYPE	DESCRIPTION	UTILIZATION
INTEGER	Whole numbers 10,390,1	Age, Quantity
SERIAL	Auto incrementing integer 1,2,3	Primary Keys
NUMERIC(P,S)	Precision Numbers(P-Precision, S-Scale) Numeric(10,2)	Money, Salary, Percentages
DECIMAL	Same as Numeric Decimal(8,2)	Monetary values Accurate Numbers
CHAR(n)	Fixed length string CHAR(13)	Phone numbers, fixed codes
VARCHAR(n)	Variable length string to n VARCHAR(50)	Name addresses, Job titles
TEXT	Unlimited length string Long descriptions,notes	Product descriptions, comments
DATE	Stores date only 2025-04-04	Birth dates, hire dates
TIMESTAMP	Date and Time 2025-04-0415:01_09	Created or updates timestamps
TIME	Time of day only14:51:00	Shift start toime
BOOLEAN	True or False TRUE,FALSE	IS active?
SPECIAL	UUID,JSON,ARRAY UNIQUEIDS,Structured-data,lists	Shift start toime

TABLE CREATION SUMMARY

Singular table names are preferred
Definition of primary keys for every table
Utilization of meaningful names for columns
VARCHAR over CHAR unless fixed length is needed.

CREATE TABLE : Creates a new table

PRIMARY KEY : Uniquely identifies each row

FOREIGN KEY : Links tables together

NOT NULL : Ensures column cannot be empty

VARCHAR(n) : Variable length string

Adding new column to an existing table

ALTER TABLE patient

ADD COLIUMN type VARCHAR(100);

ALTER TABLE patient 

ADD COLUMN quantity INT DEFAULT 1;

Updating existing Data

UPDATE patient

SET city = 'Oklahoma'

WHERE patient_id = 1;

Alternatively

UPDATE patient

SET city = CASE patient_id
    WHEN 1 THEN 'Yorkshire'
    WHEN 2 THEN 'Slovia'
    WHEN 3 THEN 'Kilifi'
    ELSE city
END;

UPDATE patient
SET city = 'Kilifi'
WHERE patient_id = 3;

Deleting Columns

ALTER TABLE patient

DROP COLUMN doctor_name;

DELETING Rows

DELETE FROM patient

WHERE patient+id = 1;

DEV Community