Home

13: PostgreSQL Complex Datatypes

Data Types

See more on the website.

Some important notes:

  • VARCHAR with no arg is a string of any length.
  • TEXT is same as VARCHAR with no arg.
  • Boolean can cast certain values if a boolean data type is expected - worth noting it is more than 0/1, false/true.

You can also play around in PGAdmin to test out things like casting for DATE or TIME.

SELECT ('12:01'::TIME);
SELECT ('APR 22 1992'::DATE);
SELECT ('20:34 AEST'::TIME WITH TIME ZONE);
SELECT ('20:34 PM'::TIME WITHOUT TIME ZONE);
SELECT ('20:34 AEST'::TIME WITH TIME ZONE);
SELECT ('APR 22 1992 20:34 AEST'::TIMESTAMP WITH TIME ZONE);

Times and dates is very flexible for the interpretation of the data.

Interval

SELECT ('1 D'::INTERVAL);
SELECT ('1 D 20 H 30 M 45 S'::INTERVAL);

Interval can be useful in how it is able to have operations made to compare them.

Repository

https://github.com/okeeffed/developer-notes-nextjs/content/postgresql/SQL-And-PostgreSQL-The-Complete-Developers-Guide/13-PostgreSQL-Complex-Datatypes

Sections

Data Types
Interval

Related

1-Simple-But-Powerful-SQL-Statements
10-Selecting-Distinct-Records
11-Utility-Operators-Keywords-And-Functions
13-PostgreSQL-Complex-Datatypes
14-Database-Side-Validation-And-Constraints
15-Database-Structure-Design-Patterns
16-Building-A-Like-System
17-Building-A-Mention-System
2-Filtering-Records
22-Understanding-The-Internals-Of-PostgreSQL
23-Indexes-For-Performance
24-Basic-Query-Tuning
25-Advanced-Query-Tuning
26-Common-Table-Expressions
27-Recursive-Common-Table-Expressions
28-Simplifying-Queries-With-Views
29-Optimizing-Queries-With-Materialized-Views
3-Working-With-Tables
30-Handling-Concurrency-And-Reversibility-With-Transactions
31-Managing-Database-Design-With-Schema-Migrations
32-Schema-Vs-Data-Migrations
34-Data-Access-Patterns-Repositories
35-Security-Around-PostgreSQL
36-Fast-Parallel-Testing
4-Relating-Records-With-Joins
5-Aggregation-Of-Records
6-Working-With-Large-Datasets
7-Sorting-Records
8-Unions-And-Intersections-With-Sets
9-Assembling-Queries-With-Subqueries
SQL-And-PostgreSQL-The-Complete-Developers-Guide