PostgreSQL, also known as Postgres, is a free,powerful and open-source object-relational database management system emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.It extends the SQL language combined with many features that safely store and scale the most complicated data workloads.
Below are some features of PostgreSQL
Major advantages of Postgresql are
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
PostgreSQL provides several index types: B-tree, hash, GiST, SP-GiST, and GIN. Each Index type uses a different algorithm that is best suited to different types of queries.
A string constant in PostgreSQL is an arbitrary sequence of characters bounded by single quotes (') for example 'This is a string constant'.
PostgreSQL supports different types of String Constants like
To start the server
pg_ctl -D /usr/local/var/postgres start
To Stop the server
pg_ctl -D /usr/local/var/postgres stop
To start the server
pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start
To Stop the server
pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" stop
To start the server
sudo service postgresql start
To Stop the server
sudo service postgresql stop
PostgreSQL change column type statement is used to change the data type of a column. It is used with an ALTER TABLE statement.
ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
In PostgreSQL there is no way to change constraints. The easiest way to accomplish this is to drop the constraint and re-add it with the desired parameters. Of course any change of the constraint will be run against the current table data.
Cube Root Operator (||/) in PostgreSQL is used to get the cube root of a number.
SELECT ||/20 AS "Cube Root of 20";
PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.
PostgreSQL trigger can be fired :
A single Postgres server process can manage multiple databases at the same time. Each database is stored as a separate set of files in its own directory within the server’s data directory. To view all of the defined databases on the server you can use the \list meta-command or its shortcut \l.
pgAdmin is a management tool for PostgreSQL and derivative relational databases such as EnterpriseDB's EDB Advanced Server. It may be run either as a web or desktop application.
PostgreSQL Indices are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indices also add overhead to the database system as a whole, so they should be used sensibly.
PostgreSQL ARRAY_TO_STRING() function is used to concatenate array elements using supplied delimiter and optional null string. It is similar to the implode function of PHP.
SELECT array_to_string(ARRAY["Ram", "Kumar","is ", "PostgreSQL", "Learner"], ' ', '*');
Outputs: Ram Kumar is PostgreSQL Learner
Different data types available in PostgreSQL are
DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. It is created as a view to easing porting problems, which allows code to remain compatible with Oracle SQL without obstructing the Postgres parser.
MVCC, which stands for multi version concurrency control, is one of the main techniques Postgres uses to implement transactions. Postgres handles transaction isolation by using MVCC to create a concept called "snapshots". Whenever a query starts, it takes a snapshot of the current state of the database.
A token in PostgreSQL can be a keyword, an identifier, a quoted identifier, a literal (or constant), or a distinctive personality symbol.Tokens are generally separated with the aid of whitespace (space, tab, newline), however, need not be if there is no ambiguity (which is usually only the case if an exceptional persona is adjoining to some other token type).
Table partitioning in PostgreSQL refers to splitting a large table into smaller pieces. A partitioned table is a logical structure used to divide a large table into smaller pieces called partitions. To divide data into partitions, we define a partition key and a partitioning method. The partition key is usually a column in the table, but it can also be an expression. The partitioning method is part of a partitioned table declaration and is determined according to the use case.
The maximum size of the table in PostgreSQL is 64TB. Some operating systems impose a file size limit that prevents files of this size from being created, so PostgreSQL stores table data in multiple files, each 1GB in size.
Write-ahead logging or WAL, is an optimization Postgres uses to minimize disk I/O while still preventing data loss. Intuitively, whenever a transaction completes, a record of every single change that transaction made must have been written out to persistent storage. The changes that need to be written out include every single row that was inserted, deleted, or updated. If transactions completed before writing all of the changes made to disk and the power ever went out before they were, Postgres would suffer data loss.
Here we are listing some reserved words of PostgreSQL:
ABORT, ABS, ASENSITIVE, ASC, DESC, BINARY, BIGINT, INT, BOTH, BREADTH, CACHE, CALL, CATALOG, COLLATE, DATABASE, DEFERRABLE etc .
For complete list you can visit: Reserved Keywords list in PostgreSQL
PostgreSQL sequence is a special type of data created to generate unique numeric identifiers in the PostgreSQL database. It is most often used for the creation of artificial primary keys, sequences are similar but not identical to AUTO_INCREMENT in MySQL. The sequence objects (also known as sequence generators or simply sequences) are single-row tables created via a command from the command line: CREATE SEQUENCE.
PostgreSQL Aliases are used to provide temporary names for columns or tables. You can create a temporary name for a column or a table by using PostgreSQL Alias.
PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement.
Syntax for creating view in PostgreSQL
CREATE VIEW view_name AS query;
A SQL cursor in PostgreSQL is a read-only pointer to a fully executed SELECT statement's result set. Cursors are typically used within applications that maintain a persistent connection to the PostgreSQL backend.
To call a stored procedure in PostgreSQL, you use the CALL statement.
There are basically two types of Cursors available in PostgreSQL. They are
A document in PostgreSQL is the unit of searching in a full-text search system; for example, a magazine article or email message. The text search engine must be able to parse documents and store associations of lexemes (keywords) with their parent document. Later, these associations are used to search for documents that contain query words.
There is no PostgreSQL-imposed limit on the number of indexes you can create on a table. Of course, performance may degrade if you choose to create more and more indexes on a table with more and more columns. PostgreSQL has a limit of 1GB for the size of any one field in a table.
A b-tree index stands for "balanced tree" and is a type of index that can be created in relational databases. It's the most common type of index that is used in Oracle and PostgreSQL databases. It is also the default index type in Oracle.
GiST is an extensible data structure, which allows users to develop indices over any kind of data, supporting any lookup over that data. GiST achieves this by adding an API to Postgres's index system anyone can implement for their specific data type.
pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL database server (Postgres), or displaying the status of a running server.
initdb is a PostgreSQL command that is used to create a new database cluster.
A database cluster is a collection of databases that are managed by a single server instance.
NEXTVAL is a function to get the next value from a sequence. A sequence is an object which returns ever-increasing numbers, different for each call, regardless of transactions, etc. Each time you call NEXTVAL, you get a different number. This is mainly used to generate surrogate primary keys for your tables.