Explanation of PostgreSQL PgAdmin Interface

This is what the postgres pgadmin looks like in the left side bar:

Pretty different than phpmyadmin and confusing as well.

Let's just go though the menu items and find out what do they mean.

First one is the server, second one is the databases-- which holds all your databases.

Postgres creates default database with the name "postgress" under databases.

So, all those terms should be pretty straight forward.

casts

Next is "casts". You control how Postgres casts from one datatype to another here.

Casts prescribe how to convert from one data type to another.

In PostgreSQL, you can create your own casts and override or enhance the default casting behavior.

For example, imagine you’re converting zip codes (which are five digits long in the US) to character from integer. You can define a custom cast that automatically prepends a zero when the zip is between 1000 and 9999.

Casting can be implicit or explicit. Implicit casts are automatic and usually expand from a more specific to a more generic type. When an implicit cast is not offered, you must cast explicitly.

Take a look at casting examples in this post: Casting In PostgreSQL

catalogs

Catalogs are system schemas that store PostgreSQL builtin functions and metadata.

Every database contains two catalogs: pg_catalog , which holds all functions, tables, system views, casts, and types packaged with PostgreSQL; and information_schema , which offers views exposing metadata in a format dictated by the ANSI SQL standard. The information_schema catalog is one you’ll find in MySQL and SQL Server as well.

PostgreSQL practices what it preaches. You will find that PostgreSQL itself is built atop a self-replicating structure. All settings to finetune servers are kept in system tables that you’re free to query and modify. This gives PostgreSQL a level of extensibility impossible to attain by proprietary database products.

The most commonly used views in the PostgreSQL information_schema are columns, which list all table columns in a database; tables, which list all tables (including views) in a database; and views, which list all views and the associated SQL to rebuild the view.

event triggers

Event triggers detect data-change events. When PostgreSQL fires a trigger, you have the opportunity to execute trigger functions in response. A trigger can run in response to particular types of statements or in response to changes to particular rows, and can fire before or after a data-change event.

Create trigger functions to respond to firing of triggers. Trigger functions differ from regular functions in that they have access to special variables that store the data both before and after the triggering event. This allows you to reverse data changes made by the event during the execution of the trigger function. Because of this, trigger functions are often used to write complex validation routines that are beyond what can be implemented using check constraints.

Side Note: Trigger technology is evolving rapidly in PostgreSQL. Starting in 9.0, a WITH clause lets you specify a boolean WHEN condition, which is tested to see whether the trigger should be fired. Version 9.0 also introduced the UPDATE OF clause, which allows you to specify which column(s) to monitor for changes. When data in monitored columns changes, the trigger fires. In 9.1, a data change in a view can fire a trigger. Since 9.3, data definition language (DDL) events can fire triggers. For a list of triggerable DDL events, refer to the Event Trigger Firing Matrix. pgAdmin lists DDL triggers under the Event Triggers branch. Finally, as of version 9.4, you may place triggers against foreign tables.

extensions

Extensions allow developers to package functions, data types, casts, custom index types, tables, attribute variables, etc., for installation or removal as a unit. Extensions are similar in concept to Oracle packages and have been the preferred method for distributing add-ons since PostgreSQL 9.1.

foreign data wrappers

Foreign tables are virtual tables linked to data outside a PostgreSQL database. Once you’ve configured the link, you can query them like any other tables. Foreign tables can link to CSV files, a PostgreSQL table on another server, a table in a different product such as SQL Server or Oracle, a NoSQL database such as Redis, or even a web service such as Twitter or Salesforce.

Foreign data wrappers (FDWs) facilitate the magic handshake between PostgreSQL and external data sources. 

languages

Create functions using a PL. PostgreSQL installs three by default: SQL, PL/pgSQL, and C. You can easily install additional languages using the extension framework or the CREATE PRODCEDURAL LANGUAGE command. Languages currently in vogue are PL/Python, PL/V8 (JavaScript), and PL/R.

schemas

Schemas are part of the ANSI SQL standard. They are the immediate next level of organization within each database. If you think of the database as a country, schemas would be the individual states (or provinces, prefectures, or departments, depending on the country). Most database objects first belong to a schema, which belongs to a database.

When you create a new database, PostgreSQL automatically creates a schema named public to store objects that you create. If you have few tables, using public would be fine. But if you have thousands of tables, you should organize them into different schemas.

functions (in schemas)

You can program your own custom functions to handle data manipulation, perform complex calculations, or wrap similar functionality.

Create functions using PLs. PostgreSQL comes stocked with thousands of functions, which you can view in the postgres database that is part of every install. PostgreSQL functions can return scalar values, arrays, single records, or sets of records.

views (in schemas)

Almost all relational database products offer views as a level of abstraction from tables.

In a view, you can query multiple tables and present additional derived columns based on complex calculations.

Views are generally read-only, but PostgreSQL allows you to update the underlying data by updating the view, provided that the view draws from a single table. To update data from views that join multiple tables, you need to create a trigger against the view. Version 9.3 introduced materialized views, which cache data to speed up commonly used queries at the sacrifice of having the most up-to-date data.

Tables (in schemas)

Tables are the workhorses of any database.

In PostgreSQL, tables are first citizens of their respective schemas, which in turn are citizens of the database.

PostgreSQL tables have two remarkable talents: first, they are inheritable . Table inheritance streamlines your database design and can save you endless lines of looping code when querying tables with nearly identical structures. Second, whenever you create a table, PostgreSQL automatically creates an accompanying custom data type.

Operators (in schemas)

Operators are nothing more than symbolically named aliases such as = or && for functions. In PostgreSQL, you can invent your own. This is often the case when you create custom data types. For example, if you create a custom data type of complex numbers, you’d probably want to also create addition operators (+,-,*,/) to handle arithmetic on them.

Types (in schemas)

Type is short for data type. Every database product and every programming language has a set of types that it understands: integers, characters, arrays, blobs, etc. PostgreSQL has composite types, which are made up of other types like complex numbers, polar coordinates, vectors, or tensors.

Whenever you create a new table, PostgreSQL automatically creates a composite type based on the structure of the table.

This allows you to treat table rows as objects in their own right. You’ll appreciate this automatic type creation when you write functions that loop through tables. pgAdmin doesn’t make the automatic type creation obvious because it does not list them under the types node, but rest assured that they are there.

Full text search (in schemas)

Full text search (FTS) is a natural language–based search. This kind of search has some “intelligence” built in.

Unlike regular expression search, FTS can match based on the semantics of an expression, not just its syntactical makeup. For example, if you’re searching for the word running in a long piece of text, you may end up with run, running, ran, runner, jog, sprint, dash, and so on.

Three objects in PostgreSQL together support FTS: FTS configurations, FTS dictionaries, and FTS parsers. These objects exist to support the built-in Full Text Search engine packaged with PostgreSQL.

For general use cases, the configurations, dictionaries, and parsers packaged with PostgreSQL are sufficient. But should you be working in a specific industry with specialized vocabulary and syntax rules such as pharmacology or organized crime, you can swap out the packaged FTS objects with your own.

Sequences (in schemas)

Just like we have Sequences in SQL, we have Sequences in Postgres. A sequence controls the autoincrementation of a serial data type. PostgresSQL automatically creates sequences when you define a serial column, but you can easily change the initial value, step, and next available value. Because sequences are objects in their own right, more than one table can share the same sequence object. This allows you to create a unique key value that can span tables. Both SQL Server and Oracle have sequence objects, but you must create them manually.

Collation (in schemas)

The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. 

Conceptually, every expression of a collatable data type has a collation. The built-in collatable data types are text, varchar, and char. User-defined base types can also be marked collatable, and of course a domain over a collatable data type is collatable. If the expression is a column reference, the collation of the expression is the defined collation of the column. If the expression is a constant, the collation is the default collation of the data type of the constant. The collation of a more complex expression is derived from the collations of its inputs.

Domains (in schemas)

PostgreSQL allows us to create user-defined data types (using CREATE DOMAIN and CREATE TYPE statements).

A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner. Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.

Sources:

• https://www.postgresql.org/files/documentation/pdf/10/postgresql-10-A4.pdf
• https://www.postgresql.org/docs/
• https://www.postgresql.org/docs/9.3/infoschema-domains.html
• https://www.postgresql.org/docs/9.1/collation.html
• https://www.oreilly.com/library/view/postgresql-up-and/9781491963401/ch01.html