Basic data types in Postgresql

Postgresql Data Types

Strings

char(n):

• Allocates a fixed amount of space, where n represents the number of characters.
• Use this when you know the field will always contain exactly n characters.
• Example: char(16) is suitable for storing a PAN number, which always has 16 characters.

varchar(n):

• Dynamically allocates space based on the actual length of the data.
• Ideal when the length of the data varies but should not exceed n characters.

text:

• Designed for storing large amounts of text, such as paragraphs.
• Use when the size is indeterminate or could be large, like for blog posts or comments.

Numbers

smallint:

• Stores numbers ranging from -32,768 to +32,767.
• Suitable for cases where you need to store smaller integer values.

integer:

• Handles numbers up to 2 billion.
• The standard choice for most integer storage needs.

bigint:

• Accommodates very large numbers, up to approximately 10¹⁸.
• Use this for scenarios involving extremely large values.

Float

real (32-bit):

• Provides approximately 7 digits of precision.
• Useful for applications where a small amount of precision is sufficient.

double precision (64-bit):

• Offers around 14 digits of precision.
• Ideal for scientific calculations or financial data requiring high accuracy.

numeric(accuracy, decimal):

• Allows you to specify both the total number of digits and the number of digits after the decimal point.
• Essential when exact precision is needed, such as in financial transactions.

Conditional

Boolean:

• Stores true / false.

Dates

timestamp 'YYYY-MM-DD HH:MM:SS':

• Stores both date and time information.
• Ideal for logging events with time details.

date 'YYYY-MM-DD':

• Stores just the date, without time.
• Useful for storing birthdays, deadlines, or any event that doesn’t require a time component.

time 'HH:MM:SS':

• Stores only the time of day.
• Suitable for tracking times, such as office hours or time slots.

PostgreSQL also provides the NOW() function, which returns the current timestamp in the format of 'YYYY-MM-DD HH:MM:SS'. This can be used to record the exact time a row was created or updated.