DEV Community: Pawan Kukreja

Introduction of Apache AGE.

Pawan Kukreja — Mon, 13 Nov 2023 12:11:50 +0000

Overview

Apache AGE, a PostgreSQL extension, delivers robust graph database capabilities. Representing "A Graph Extension" (AGE), it draws inspiration from Bitnine's variant of PostgreSQL 10, AgensGraph—a versatile multi-model database. The project aims to establish a unified storage solution capable of managing both relational and graph model data. This empowers users to seamlessly leverage standard ANSI SQL alongside openCypher, the designated Graph query language.

Graphs

A graph is made up of vertices and edges, and every node and edge has a unique set of attributes associated with it. The fundamental unit of a graph is a vertex, which is independent of all other nodes in the graph. A directed link between two vertices is formed by an edge.

To create a graph, use the create_graph function, located in the ag_catalog namespace.

Syntax: create_graph(graph_name);

Returns:

void

To delete a graph, use the drop_graph function, located in the ag_catalog namespace.

Syntax: drop_graph(graph_name, cascade);

Returns:

void

Simple Datatype

Null
SELECT *
FROM cypher('graph_name', $$
RETURN NULL
$$) AS (null_result agtype);

Integer
SELECT *
FROM cypher('graph_name', $$
RETURN 1
$$) AS (int_result agtype);

Float
SELECT *
FROM cypher('graph_name', $$
RETURN 1.0
$$) AS (float_result agtype);

Numeric
SELECT *
FROM cypher('graph_name', $$
RETURN 1.0::numeric
$$) AS (numeric_result agtype);

Bool
SELECT *
FROM cypher('graph_name', $$
RETURN TRUE
$$) AS (boolean_result agtype);

String
SELECT *
FROM cypher('graph_name', $$
RETURN 'This is a string'
$$) AS (string_result agtype);

SQL Syntax (Part 03)

Pawan Kukreja — Wed, 13 Sep 2023 18:41:16 +0000

Dollar-Quoted String Constants

A dollar-quoted string that follows a keyword or identifier must be separated from it by whitespace; otherwise the dollar quoting delimiter would be taken as part of the preceding identifier.

Although dollar quoting is not supported by the SQL standard, it is frequently more practical than using the single quote syntax to construct complex string literals. It is very helpful for encoding string constants inside of other constants, which is frequently required in the definitions of procedural functions. Each backslash in the above example would have to be expressed using single-quote syntax as four backslashes, which would be reduced to two backslashes in the first parsing of the string constant and then to one in the subsequent parsing of the inner string constant during function execution.

Bit-String Constants

Bit-string constants resemble conventional string constants but lack the whitespace between the beginning quotation and the B (in upper or lower case), for example, B'1001'.

Bitstring constants can only contain the letters 0 and 1.
Bit-string constants can also be written in hexadecimal notation with a leading X (in upper- or lower-case), for example, X'1FF'. Each hexadecimal digit in this notation corresponds to a bit-string constant of four binary digits.
Similar to conventional string constants, both types of bit-string constants can be carried across lines.
A bit-string constant cannot employ dollar quoting.

Numeric Constants

If the value of a numeric constant that lacks either a decimal point or an exponent fits within the type integer (32 bits), the constant is originally assumed to be of type integer. If the value falls within the type bigint (64 bits), the constant is assumed to be of type numeric. The initial assumption is that any constants with exponents and/or decimal points are of type numerical.

The type resolution methods only use the data type that was first allocated to a numeric constant as a starting point. Most of the time, the constant will be automatically forced to the best type based on the situation. When required, you may use casting to compel a numeric value to be interpreted as a certain data type.

Special Characters

Other than being an operator, certain non-alphanumeric characters have a specific purpose.
You may get further information on the usage in the section where the relevant syntactic element is discussed. This section is merely included to inform readers of the characters' existence and to summarise their goals.
launching it.

In the body of a function definition or a prepared statement, a positional parameter is denoted by a dollar sign ($), followed by digits. The dollar symbol can also be used as an identifier or as a dollar-quoted string constant in other places.
The purpose of brackets (()) is typically to group expressions and establish precedence. Sometimes a certain SQL command has a set syntax that includes brackets.
In various syntactical constructions, commas (,) are employed to demarcate the components of a list.
An SQL command comes to an end with a semicolon (;). It can only exist within a string constant or quoted identifier in a command; otherwise, it cannot appear elsewhere.
In order to choose "slices" from arrays, use the colon (:). Refer to Section 8.15. The colon is used to prefix variable names in some SQL dialects (such as Embedded SQL).
In some situations, the asterisk (*) is used to indicate every field of a table row or composite value. When used as the argument of an aggregate function, it also has a specific meaning, indicating that the aggregate does not need any explicit parameters.
Numeric constants and the names of schema, tables, and columns are separated by a period (.).

SQL Syntax (Part 02)

Pawan Kukreja — Wed, 13 Sep 2023 17:58:20 +0000

Constants

In PostgreSQL, there are three categories of implicitly-typed constants: strings, bit strings, and numbers. Additionally, explicit types may be used to specify constants, allowing for a more precise representation and more effective system management. The subsections that follow cover these options.

String Constants

An arbitrary string of characters enclosed in single quotes (') is referred to as a string constant in SQL. Write two adjacent single quotes, such as 'Dianne's horse,' to include a single-quote character in a string constant. Keep in mind that this differs from a double-quote character (").

String Constants with C-Style Escapes

Additionally, PostgreSQL permits "escape" string constants, an addition to the SQL standard. Just before the first single quotation, write the letter E (in upper- or lowercase), for example, E'foo', to specify an escape string constant. (If you want to continue an escape string constant over many lines, only use E.) A backslash character () starts a backslash escape sequence that looks like C within an escape string.

You are accountable for ensuring that the byte sequences you generate, particularly when employing the octal or hexadecimal escapes, encode legitimate characters according to the server character set. Use of Unicode escapes or an alternate Unicode escape syntax is a good substitute.

String Constants with Unicode Escapes

Another form of escape syntax for strings is also supported by PostgreSQL and allows for the specification of any Unicode characters by code point. Before the starting quotation, without any spaces in between, a Unicode escape string constant begins with U& (an upper- or lower-case letter U followed by an ampersand), for instance, U&'foo'. (Remember that the operator & becomes ambiguous because of this. To get around this issue, use spaces around the operator.) A backslash, a four-digit hexadecimal code point number, or, alternatively, a backslash, a plus sign, and a six-digit hexadecimal code point number can be used to specify Unicode characters in escaped form inside of quotations.

Although the existence of the 6-digit form technically negates the need for this, it is possible to specify UTF-16 surrogate pairs to assemble characters with code points bigger than U+FFFF using either the 4-digit or 6-digit escape form. Surrogate pairs are concatenated into a single code point rather than being stored separately (directly).
The Unicode code point identified by one of these escape sequences is translated to the actual server encoding if the server's encoding is not UTF-8; if that is not feasible, an error is returned.

Additionally, the standard_conforming_strings configuration parameter must be enabled in order for the Unicode escape syntax for string constants to function. This is necessary because, in the absence of it, the syntax may mislead clients that interpret SQL statements, which may result in SQL injections and other security problems. This syntax will be denied with an error notice if the argument is set to off.

SQL Syntax (Part 01)

Pawan Kukreja — Mon, 11 Sep 2023 13:18:29 +0000

Lexical Structure:

A list of instructions makes up a SQL input sequence. A command is made up of a series of tokens, each of which is followed by a semicolon (";"). A command comes to a conclusion when the input stream is finished. Depending on the syntax of the particular command, some tokens are valid.

Key words, identifiers, quoted identifiers, literals (or constants), and special character symbols can all be considered tokens. Whitespace (space, tab, newline) is typically used to separate tokens, however it is not required if there is no ambiguity (which is typically only the case if a special character is close to another token type).

Regarding which tokens identify instructions and which are operands or arguments, the SQL syntax is not particularly consistent. The command name often appears in the first few tokens, thus in the example above, we would typically refer to the commands "SELECT," "UPDATE," and "INSERT." But this kind of INSERT also needs a VALUES to be finished, just as the UPDATE command always needs a SET token to exist in a certain spot. Part VI details each command's explicit syntactic requirements.

Identifiers and Key Words

Key words, or words with a predetermined meaning in the SQL language, include tokens like SELECT, UPDATE, or VALUES in the example above. Tokens like MY_TABLE and A serve as identifiers. Depending on the command they are used in, they indicate names of tables, columns, or other database objects. As a result, they are occasionally only referred to as "names". Since identifiers and key words have the same lexical structure, it is impossible to determine whether a token is an identifier or a key word without first understanding the language. In Appendix C, there is a comprehensive list of keywords.

A letter (a-z, but also letters with diacritical markings and non-Latin characters) or an underscore (_) must come before any SQL identifiers or key words. An identification or key word may contain additional letters, underscores, numerals (0–9), or dollar signs ($). The usage of dollar signs may make applications less portable because they are not permitted in identifiers per the letter of the SQL standard. Identifiers of this type are protected from potential conflict with future additions of the standard since the SQL Standard does not specify a key word that contains numbers or that begins or finishes with an underscore.

Advanced Features of PostgreSQL (Part 02)

Pawan Kukreja — Mon, 11 Sep 2023 10:46:32 +0000

Window Functions

A window function does a computation over a group of table rows that are connected to the current row in some way. This is akin to the kind of calculation that an aggregate function may perform. However, unlike non-window aggregate calls, window functions do not lead to the grouping of rows into a single output row. Instead, the rows continue to be distinct from one another. The window method has access to more rows of the query result than simply the current row in the background.
The OVER clause always follows the window function's name and any arguments in a window function call. This is what sets it apart from a typical function or non-window aggregate syntactically. The window function's exact division of the query's data into subsets for processing depends on the OVER clause. In the OVER clause, the PARTITION BY clause separates the rows into partitions based on the values of the PARTITION BY expression(s). The window function is calculated across the rows that are a part of the same partition as the current row for each row.
You can also control the order in which rows are processed by window functions using ORDER BY within
OVER.

The idea of window functions also includes the idea that each row has a group of rows within its partition known as its window frame. Some window functions don't affect the entire partition; instead, they simply affect the rows of the window frame. If ORDER BY is not specified, the frame will by default include all rows from the beginning of the partition to the current row, as well as any subsequent rows that are identical to the current row in accordance with the ORDER BY clause. The default frame, when ORDER BY is absent, contains every row in the partition.

Only the SELECT list and the ORDER BY clause of the query are allowed to employ window functions. Other places, like the GROUP BY, HAVING, and WHERE clauses, restrict them. This is due to the fact that they logically follow the processing of those phrases. In addition, aggregate functions that are not window functions run first. This means that a window function call may include an aggregate function call, but not the other way around.

Inheritance:

A notion from object-oriented databases is inheritance. It provides intriguing new opportunities for database design.
Example for Inheritance:

CREATE TABLE cities (
24
Advanced Features
 name text,
 population real,
 elevation int -- (in ft)
);

CREATE TABLE capitals (
 state char(2) UNIQUE NOT NULL
) INHERITS (cities);

In this example, a column of capitals inherits its parent cities' name, population, and elevation columns. The column name's type, text, is a built-in PostgreSQL type for character strings of varying length. The state column in the capitals table also displays the state's abbreviation. A table in PostgreSQL can derive from zero or more other tables.

Advanced Features of PostgreSQL (Part 01)

Pawan Kukreja — Mon, 11 Sep 2023 09:52:46 +0000

Introduction:

We will now discuss some more advanced features of SQL that simplify management and prevent loss or corruption of your data. We will discuss some PostgreSQL extensions aswell.
Some examples from this chapter can also be found in advanced.sql in the tutorial directory. This file also contains some sample data to load.

Views:

Suppose the combined listing of weather records and city location
is of particular interest to your application, but you do not want to type the query each time you need
it. You can create a view over the query, which gives a name to the query that you can refer to like an
ordinary table.

Views allow you to encapsulate the details of the structure of your tables, Making liberal use of views is a key aspect of good SQL database design.
Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.

Foreign Keys:

You want to make sure that no one can insert rows in the weather table that do not have a matching entry in
the cities table. This is called maintaining the referential integrity of your data. In simplistic database
systems this would be implemented (if at all) by first looking at the cities table to check if a matching
record exists, and then inserting or rejecting the new weather records.

Transactions:

It is a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps
are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction
from completing, then none of the steps affect the database at all.
When the transaction is done and acknowledged by the database system, it has been permanently recorded and not being lost if ever crash in system. Another important property of transactional databases is closely related to the notion of atomic updates. when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others.
In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands.
If partway through the transaction, we decide we do not want to commit we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled.

The SQL Language (Part-4)

Pawan Kukreja — Mon, 11 Sep 2023 08:30:52 +0000

Aggregate Functions:

PostgreSQL Supports aggregate functions, It computes single result from multiple input rows.
Aggregates to compute:

Count
Sum
avg(average)
max(maximum)
min(minimum) over the set of rows.

It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses.
The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups
and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas
HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not
contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will
be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions.
(Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom
useful. The same condition could be used more efficiently at the WHERE stage.)

Updates

You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows:
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';

Deletions

Rows can be removed from the table using Delete command.
If you no longer want any query to execute you can use Delete command:
DELETE FROM weather WHERE city = 'Hayward';

The SQL Language (Part-3)

Pawan Kukreja — Sun, 13 Aug 2023 22:46:29 +0000

Joins Between Tables

With the JOIN function, we can access the multiple table at once or access the same table in such a way that multiple rows of the table are being processed at the same time.
Queries that access multiple tables at one time are called join queries. They combine rows from one table with rows from one table with rows from a second table, with expression specifying which rows are to be paired.

For Example:

SELECT * FROM weather JOIN cities ON city = name;

San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) (2 rows)

There is no result row for the city of Hayward. This is because there is no matching entry in the cities table for Hayward, so the join ignores the unmatched rows in the weather table. We will see shortly how this can be fixed.

There are two coloumns containing the city name. This is correct because this lists of coloumns from the weather and cities tables are concatenated. In practice this is undesirable, through, so you will probably want to list the outputs columns explicitly rather than using.

SELECT city, temp_lo, temp_hi, prcp, date, location
 FROM weather JOIN cities ON city = name;

Since the column all had different names, the parser automatically found which table they belong to. If there were duplicate columns names in the two tables you would need to qualify the columns names to show which one you meant, as in

SELECT weather.city, weather.temp_lo, weather.temp_hi,
 weather.prcp, weather.date, cities.location
 FROM weather JOIN cities ON weather.city = cities.name;

It is widely considered good style to qualify all column names in a join query, so that the query won't fail
if a duplicate column name is later added to one of the tables.

We will figure out how we can get the Hayward records back in, What we want the query to do is to scan the weather table and for each row to find the matching cities row. If no matching found we will use "empty calues" to substitute for cities table's columns. This kind of query is called an outer join.

SELECT *
 FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

Hayward | 37 | 54 | | 1994-11-29 | | San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) (3 rows)

This is called left outer join because the table mentioned on teh left of the join operator will have each of its rows in the output at least once.

There are also right outer joins and full outer joins.

We can also join a table against itself. This is called self join. As an example, suppose we wish to find all the weather records that are in the temperature range of other weather records.

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
 w2.city, w2.temp_lo AS low, w2.temp_hi AS high
 FROM weather w1 JOIN weather w2
 ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)

We can relable the weather table as w1 and w2 to be able to distinguish the left and right side of join.

SELECT *
 FROM weather w JOIN cities c ON w.city = c.name;

The SQL Language (Part-2)

Pawan Kukreja — Sun, 13 Aug 2023 21:52:52 +0000

Querying a Table

A SQL SELECT statement is used to retrieve the data from the table. Like to retrieve all the rows of table weather:

SELECT * FROM weather;

Here * showed the all columns.

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

The Output should be:

city | temp_lo | temp_hi | prcp | date
San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows)

You can write expression, not just simple coulumn references, in the select list, you can do:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

This should give:

city | temp_avg | date

San Francisco | 48 | 1994-11-27 San Francisco | 50 | 1994-11-29 Hayward | 45 | 1994-11-29 (3 rows)

A query can be “qualified” by adding a WHERE clause that specifies which rows are wanted. The WHERE
clause contains a Boolean expression.

SELECT * FROM weather
 WHERE city = 'San Francisco' AND prcp > 0.0;

It will show:

city | temp_lo | temp_hi | prcp | date

San Francisco | 46 | 50 | 0.25 | 1994-11-27 (1 row)

You can sort the result:

SELECT * FROM weather
 ORDER BY city;

city | temp_lo | temp_hi | prcp | date

Hayward | 37 | 54 | | 1994-11-29 San Francisco | 43 | 57 | 0 | 1994-11-29 San Francisco | 46 | 50 | 0.25 | 1994-11-27

It may not give full results:

Try this command to:

SELECT * FROM weather
 ORDER BY city, temp_lo;

You can request duplicate rows be removed from the result:

SELECT DISTINCT city
 FROM weather;

It will show

city

Hayward San Francisco (2 rows)

You can use DISTINCT and ORDER BY, together for correct order.

SELECT DISTINCT city
 FROM weather
 ORDER BY city;

The SQL Language (Part-1)

Pawan Kukreja — Sun, 13 Aug 2023 12:09:57 +0000

Introduction

This chapter provides an overview of how to use SQL to perform simple operations. This tutorial is only intended to give you an introduction and is in no way a complete tutorial on SQL.
In the following example we assume that you have created a database named mydb as described in the previous chapter and have been able to start psql

Examples can also be found in the PostgreSQL source distribution in the directory src/tutorial/.
To use those files, first change to that directory and run make:

cd .../src/tutorial
make

This creates the scripts and compiles the C files containing user-defined functions and types. Then to start to tutorial do the following:

psql -s mydb

mydb=> \i basics.sql

This \i command reads in commands from the specified file. psql's -s option puts you in single step mode which pauses before sending each statement to the server. This commands used in this section are in the file basics.sql.

Concepts

PostgreSQL is a relational database management system(RDBMS), that means it is a system for managing data stored in relations. Relation is essentially a mathematical term for table. The notion of storing data in tables is so common place today that it might seem inherently obvious but there are a number of other ways of organising database.

Each table is a named collection of rows. Each row of a given table has the same set of named columns and each colum is of a specific datatype. Where as columns have a fixed order in each row, it is important to remember that SQL does not guarntee the order of the rows within the table in any way.

Creating a New Table

you can create a table by specifying the table name along with all column names and their types.

Create table weather (
city varchar(80),
 temp_lo int, -- low temperature
 temp_hi int, -- high temperature
 prcp real, -- precipitation
 date date
);

you can enter this into psql with the line breaks. psql will recognise that the command is not terminated until the semicolon.

Populating a Table With Rows

The INSERT statement is used to populate a table with rows:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
All the data types use rather obvious input formats. Constants that are not simple numeric values usually must be surrounded by single quotes, as in the example. The datatype is actually quite flexible in what it accepts, but for this tutorial we will stick to the unambiguous formate shown here.

The point type requires a coordinate pair as input as shown here

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

This syntax used so far requires you to remember the order of the columns. As alternative syntax allows you to list the columns explicitly:

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
 VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

You can list the columns in a different order if you wish or even omit some columns, e.g., if the precipitation
is unknown:

INSERT INTO weather (date, city, temp_hi, temp_lo)
 VALUES ('1994-11-29', 'Hayward', 54, 37);

Enter all the commands shown above so you have some data to work with in the following sections.

You can use COPY to load large amounts of data from flat-text files. This is usually faster because the COPY command is optimised for this application while allowing less flexibility that INSERT

COPY weather FROM '/home/user/weather.txt';

Where file name for the source file must be available on the machine running the backend process.

Architecture of PostgreSQL.

Pawan Kukreja — Sun, 13 Aug 2023 11:09:02 +0000

Architecture Fundamentals

In database jargon, PostgreSQL uses a client/server model. A PostgreSQL session consists of the following cooperating processes:

A server process, which manages the database files, accepts connections to the database from client applications, and performs database actions on behalf of the clients. The database server program is called postgres.
The user's client application that wants to perform database operations. Client applications can be very diverse in nature: a client could be a text-oriented tool, a graphical application, a web server that accesses the database to display web pages, or a specialized database maintenance tool. Some client applications are supplied with the PostgreSQL distribution; most are developed by users.

As is typical of client/server applications, the client and the server can be on different hosts. In that case
they communicate over a TCP/IP network connection. You should keep this in mind, because the files that can be accessed on a client machine might not be accessible on the database server machine.

The PostgreSQL server can handle multiple concurrent connections from clients. To achieve this it starts
(“forks”) a new process for each connection. From that point on, the client and the new server process
communicate without intervention by the original postgres process. Thus, the supervisor server process
is always running, waiting for client connections, whereas client and associated server processes come and
go.

Creating a Database

The first test to see whether you can access the database server is to try to create a database. A running
PostgreSQL server can manage many databases.

To create a new database, in this example named mydb, you use the following command:

`createdb mydb`

If this produces no response then this step was successful and you can skip over the remainder of this
section.

If you see a message similar to:
createdb: command not found

then PostgreSQL was not installed properly. Either it was not installed at all or your shell's search path
was not set to include it. Try calling the command with an absolute path instead:

/usr/local/pgsql/bin/createdb mydb

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

Another response could be this:
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket?

This means that the server was not started, or it is not listening where createdb expects to contact it.
Again, check the installation instructions or consult the administrator.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "pawan" does not exist

where your own login name mentioned. This will happen if the administrator has not created a PostgreSQL user account for you.

If you have a user account but it does not have the privileges required to create a database, you will see
the following:
createdb: error: database creation failed: ERROR: permission denied to create database

If PostgreSQL refuses to create databases for
you then the site administrator needs to grant you permission to create databases.

You can also create databases with other names. PostgreSQL allows you to create any number of databases
at a given site.

createdb

If you do not want to use your database anymore you can remove it.

dropdb mydb

Brief Introduction to PostgreSQL

Pawan Kukreja — Sun, 13 Aug 2023 10:27:53 +0000

What is PostgreSQL

It is an object-relational database management system based on Postgres Version 4.2 developed by University of California at Barkeley Computer Science Department.

It support large part of the SQL standard and offers many modern features and It is an open source.

Complex Queries
Foreign Keys
Triggers
Updatable Views
Transaction integrity
Multiversion Concurrency Control

PostgreSQL can be extended by the user in many way:

Data types
Functions
Operators
Aggregate Functions
Index Methods
Procedural Languges

PostgreSQL can be used modified and distributed by anyone free of charge for any purpose, be it private commercial and academic.

Brief History of PostgreSQL

PostgreSQL previously called The object-relational database management system derived from Postgres package written in University of California at Berkeley. Now PostgreSQL is now the most advanced open-sources database available anywhere.

Postgres95
In 1994, Andrew Yu and Jolly Chen added an SQL Language interpreter to Postgres. Under a New name Postgres95 and subsequently released to the web to find its own way in the world as an open-source descendant of the original Postgres Berkley code.

PostgreSQL
In 1996 name was changed from Postgres95 to PostgreSQL to reflect the relationship between the original Postgres and the more recent versions with SQL capabilities.

Convention
The following conventions are used in the synopsis of a command.

Brackets "[ and ]" which Indicates optional parts.
Braces "{ and }" and vertical lines "|" indicate that you must choose one alternative.
Dots "..." indicated that the preceding element can be repeated. And all other symbols including parentheses should be taken literally.

SQL commands are preceded by the prompt =>, and shell commands are preceded by the prompt $. Normally, prompts are not shown through.

Bug Reporting Guidlines

Big report plays an important role in making PostgreSQL more reliable because even the utmost care cannot guarantee that every part of PostgreSQL will work on every platform under every circumstances.

Identifying Bugs

A program terminates with a fatal signal or an operating system error message that would point to a problem in the program.
A program produces the wrong output for any given input
A program refuses to accept the valid input.
A program accepts invalid input without a notice or error message
PostgreSQL fails to compile build or install according to the instructions on supported platforms