PostgreSQL is an open source database and is giving quite a competition to the likes of Oracle , SQL server and other vended databases out there. These next series of blogs will delve into PostgreSQL and its features.
PostgreSQL comes with a fairly extensive set of data types and you can add your own by using the create type statement. A few notable examples would be json for textual , jsonb for json in binary etc, cidr for ip address, macaddr for macaddresses etc . Postgres actually creates types for any tables you define. Third party providers use this feature to provide domain specific constructs and make it efficient and performant.
Postgres has a fairly complex security mechanism and is a full fledged database so its not really suited for embedded or low foot print solutions.
you can use psql for its command line , pgAdmin for gui , and phpPgAdmin for web based Gui tool for administration.
in some cases of installation of pgAdmin , you may run into a problem where the web page keeps saying its loading infinitely , this is because of a java script issue and you need to update the registry key to javascript from plain text , the specifics are in the pgadmin web site.
Some interesting things about postgres
- Tables are inheritable – since it creates a custom data type , you can treat tables as a class
- you can update a view as long as its derived from a single table
- Extensions are like packages and you can extend these extensions to create new ones. its best to create a separate schema for extensions , since it installs all of its objects, its best to keep it separate.
- Functions can be created using PLs and stored procedures are also called functions . The default language for functions are SQL , pl/pgSQL and C. you can add additional languages ( using extensions of course ! )
- Operators are symbolically named aliases for functions , you can assign special meaning to symbols such as * & + etc
- Foreign tables are virtual tables linked to data outside the database like in flat files, webs service , or a table in another database. It implements the management of external Data Standard . Foreign Data Wrappers ( FDW) for different data sources are already implemented and once the extension is installed its available for use. See this link for implementing FDW to Oracle
- Triggers are special functions that give access to special variables that store data before and after the triggering event.
- Catalogs store system schemas that store functions and metadata
- FTS – full text search is natural language search , see image above for the components associated with it – FTS configuration, FTS dictionaries , FTS parsers, and FTS templates.
- Types – postgres has composite data types and we can make new ones too , my instance has 91 of these
- Cast – used to convert data from one data type to another . this can be implicit or explicit
