PostgreSQL is an object-relational database management system (ORDBMS), It supports most of the SQL standard and provides many other modern features. More and more people use it to replace mysql. Has become the preferred open source relational database for development.
First will introduce data type:
Below is an example how we get data type from table:
INTERVAL data types provide you with a very useful tool for performing arithmetic on date and time data types.For example, let’s say our rental policy requires a DVD to be returned within 3 days. We can calculate the expected_return_date for a given DVD rental by adding an INTERVAL of 3 days to the rental_date from the rental table. We can then compare this result to the actual return_date to determine if the DVD was returned late.
Array
data type, is like the list in python only different is array start from “1”, we can access to the number of array by[].
Below example is we have to confirm that the second item in array is “Deleted Scenes”
Result:
The ANY function allows you to search for a value in any index position of an ARRAY.
WHERE 'search text' = ANY(array_name)Below we can find out “Trailers” in any position of special features column.
Searching an ARRAY with @>
The contains operator @> operator is alternative syntax to the ANY function and matches data in an ARRAY using the following syntax.
WHERE array_name @> ARRAY['search text'] :: type[]Overview of basic arithmetic operators
AGE() Function
Literally, it means age of something.
We can use it in two ways:
1.AGE(timestamp);2.AGE(timestamp,timestamp);
First one you can enter your birth date, then it will show your age.
Second one can now the interval between two time stamp.
INTERVAL will give us timestamp data
select ‘0001–01–01’::date +1 → 0001–01–02
‘0001–12–31 BC’::date + interval ‘1 day’; → 0001–01–01 00:00:00
:: is same as CAST function, it can assign data type.
TEXT
full-text search: tsvector and tsquery
This is being used to find some text and doing text search, below link has detail information for these.
User-defined data types
ENUM or enumerated data types are great options to use in your database when you have a column where you want to store a fixed list of values that rarely change. Examples of when it would be appropriate to use an ENUM include days of the week and states or provinces in a country.
Another example can be the directions on a compass (i.e., north, south, east and west.)
If you enjoy the content, please follow me and give me thump up!
