Preamble
PostgreSQL is a powerful Open Source Object-Relational Database System. It has over 15 years of the active development phase and a proven architecture that has earned a good reputation for reliability, data integrity, and accuracy.
This tutorial will give you a quick start with PostgreSQL and allows you to get used to PostgreSQL programming.
PostgreSQL data types
Literals
Announcement of variables
Comments
Aliases (pseudonyms)
PostgreSQL functions
PostgreSQL function date/time
|
Function & Description
|
|---|
|
AGE The age PostgreSQL function returns the number of years, months, and days between two dates.
|
|
CURRENT_DATE The current_date function in PostgreSQL returns the current date.
|
|
CURRENT_TIME The current_time function in PostgreSQL returns the current time with time zone.
|
|
CURRENT_TIMESTAMP The current_timestamp PostgreSQL function returns the current date and time with the time zone.
|
|
DATE_PART The date_part PostgreSQL function extracts parts from the date.
|
|
EXTRACT The extract PostgreSQL function extracts parts from the date.
|
|
LOCALTIME The local time PostgreSQL function returns the current time.
|
|
LOCALTIMESTAMP The localtimestamp PostgreSQL function returns the current date and time.
|
|
NOW The now PostgreSQL function returns the current time and date with the time zone.
|
PostgreSQL string functions
|
Function & Description
|
|---|
|
BTRIM The PostgreSQL btrim function removes all specified characters both at the beginning and at the end of a line.
|
|
CHAR_LENGTH The char_length function in PostgreSQL returns the length of the specified string.
|
|
CHARACTER_LENGTH The character_length function in PostgreSQL returns the length of the specified string.
|
|
INITCAP The function initcap PostgreSQL converts the first letter of each word to upper case, and all other letters are converted to lower case.
|
|
LENGTH The length PostgreSQL function returns the length of the specified string, expressed in the number of characters.
|
|
LOWER The lower PostgreSQL function converts all characters of a specified string into lower case.
|
|
LPAD The PostgreSQL lpad function returns a string added to the left side of the specified string of a certain length.
|
|
LTRIM The ltrim PostgreSQL function removes all specified characters on the left side of the line.
|
|
POSTGRESQL operator || PostgreSQL operator || allows to combine 2 or more lines together.
|
|
POSITION The PostgreSQL position function returns the substring location in a string.
|
|
REPEAT The repeat PostgreSQL function repeats the string as many times as specified.
|
|
REPLACE The replace PostgreSQL function replaces all occurrences of the specified string.
|
|
RPAD The PostgreSQL rpad function returns a string added to the right side of the specified string of a certain length.
|
|
RTRIM The PostgreSQL rtrim function removes all specified characters on the right side of the line.
|
|
STRPOS The PostgreSQL strpos function returns the substring arrangement in a string.
|
|
SUBSTRING The substring PostgreSQL function allows extracting substring from a string.
|
|
TRANSLATE The translate PostgreSQL function replaces a sequence of characters in a string with another set of characters. However, it replaces one character at a time.
For example, it replaces the first character in string_to_replace with the first character in replace_string. Then it will replace the second character in string_to_replace with the second character in replace_string, etc. |
|
TRIM The trim PostgreSQL function removes all specified characters from the beginning or end of a line.
|
|
UPPER PostgreSQL function upper converts all characters in a specified string into upper case.
|
PostgreSQL conversion functions
|
Function & Description
|
|---|
|
TO_CHAR The PostgreSQL to_char function converts a number or date to a string.
|
|
TO_DATE The to_date function in PostgreSQL converts a string into a date.
|
|
TO_NUMBER The PostgreSQL to_number function converts a string into a number.
|
|
TO_TIMESTAMP The PostgreSQL to_timestamp function converts a string into a timestamp.
|
Numerical/mathematical PostgreSQL functions
|
Function & Description
|
|---|
|
ABS The abs function in PostgreSQL returns the absolute value of the number.
|
|
AVG The avg PostgreSQL function returns the average value of the expression.
|
|
CEIL The ceil PostgreSQL function returns the smallest integer value, which is greater than or equal to a number.
|
|
CEILING The ceiling PostgreSQL function returns the smallest integer value, which is greater than or equal to a number.
|
|
COUNT The count PostgreSQL function returns the number of expressions.
|
|
DIV The div PostgreSQL function is used for integer division, where n is divided by m and integer value is returned.
|
|
EXP The exp function in PostgreSQL returns e, raised to number (or enumber).
|
|
FLOOR The floor PostgreSQL function returns the largest integer value that is less than or equal to a number.
|
|
MAX The function max PostgreSQL returns the maximum value of the expression.
|
|
MIN The min function in PostgreSQL returns the minimum value of the expression.
|
|
MOD The mod PostgreSQL function returns the residue from n divided by m.
|
|
POWER The power PostgreSQL function returns the value of m, which is elevated to n-th degree.
|
|
RANDOM The random PostgreSQL function can be used to return a random number or a random number within a range.
|
|
ROUND The round PostgreSQL function returns a number rounded to a certain number of decimal places.
|
|
SETSEED The setseed PostgreSQL function can be used to set the initial number the next time the random function is called. If you do not call setseed, PostgreSQL will use its own seed value. This may or may not be really random.
If you set the initial value by calling setseed, then random will return a repeatable sequence of random numbers obtained from the initial number. Tip: The setseed function can be very useful in situations where you want to make sure that PostgreSQL uses a really random starting number. |
|
SIGN The sign PostgreSQL function returns a value that denotes a number sign.
|
|
SQRT The function sqrt PostgreSQL returns the square root of the number.
|
|
SUM The sum PostgreSQL function returns the cumulative value of the expression.
|
|
TRUNC The trunc PostgreSQL function returns a number truncated to a certain number of decimal places.
|
PostgreSQL query types
|
Get records from the table
|
|
|
Get records from the table and limit the number of records
|
|
|
Used to remove duplicates from the result set
|
|
|
Used for displaying tables and any associations
|
|
|
Used to filter the results
|
|
|
Used to sort the results
|
|
|
Used to group the results
|
|
|
Used in combination with the GROUP BY operator to limit groups of returned strings only to those whose TRUE condition
|
|
|
Operator for inserting a row into a table
|
|
|
The operator of data update in the table
|
|
|
Operator to delete data from the table
|
|
|
Operator to combine sets of results with the removal of repeating rows
|
|
|
Operator to combine sets of results without removing repeating lines
|
|
|
The operator returns the general records of the result sets
|
|
|
Set of results from one minus set of results from another
|
|
|
Attached request (subquery)
|
|
|
Used to extract data from multiple tables
|
PostgreSQL database administration
|
Create a database account
|
|
|
Granting and cancellation of privileges in the database
|
|
|
Change user password
|
|
|
User search
|
|
|
Find users who are logged in to PostgreSQL
|
|
|
Rename user
|
|
|
Delete user
|
|
|
Cleaning up unused space in the database
|
|
|
The process that automatically cleans unused space in the database
|
PostgreSQL tables and views
|
Create a table
|
|
|
Create a table from the data of another table
|
|
|
Add, edit, delete columns in the table; rename the table
|
|
|
Delete the table
|
|
|
Delete all records from the table (with the optimizer)
|
|
|
Virtual table (representation of other tables)
|
PostgreSQL keys, indexes, and restrictions
|
Create, add and delete primary keys
|
|
|
Create, delete and rename indices (performance tuning)
|
|
|
Create, add and remove unique constraints
|
PostgreSQL conditions
|
Logical operator AND
|
|
|
Logical operator OR
|
|
|
Logical operator AND and OR
|
|
|
Determines whether the specified character string coincides with the specified template
|
|
|
Determines whether a specified value matches a value in a nested query or list
|
|
|
Negation of conditions
|
|
|
Used to check the NULL value
|
|
|
Used to check the NOT NULL value
|
|
|
Defines the range to check the condition
|
|
|
In the WHERE proposal of an external query, the presence of strings returned by an attached query is checked.
|
PostgreSQL comparison statements
|
Comparison operators such as =, <>, !=, >, < and others
|
Learn PostgreSQL Tutorial – Full Course for Beginners
About Enteros
IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Healthcare Enterprise Architecture with Enteros: Leveraging Forecasting Models for Enhanced Performance and Cost Efficiency
- 15 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Transforming Banking Operations with Enteros: Leveraging Database Solutions and Logical Models for Enhanced Performance
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Database Performance on AWS EC2 with Enteros: A Cloud FinOps Solution for the Financial Sector
- 14 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing IT Sector Budgeting with Enteros: Enhancing Database Performance for Cost-Effective Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…