Connect to a PostgreSQL server using the PostgreSQL command-line client (psql) and a username. It will prompt you for the password

To connect to a specific database on a PostgreSQL server with a username

To exit the client

For a full list of commands

For a list of psql commands

To export data using the pg_dump tool

To create a database

To delete a specific database

To list all the databases on a server

To connect to a specific database

To list all tables in a database

To get information about a specific table

To create a table

To increment the ID automatically with each new record, use the SERIAL data type

To create a table with a foreign key

To change a table name

To add a column to the table

To change a column name

To change a column data type

To delete a column

To delete a table

An example of a single-table query

An example of a multiple-table query

To insert data into a table, use INSERT

You may specify the columns in which the data is added. The remaining columns are filled with default values or NULLs

To update the data in a table, use UPDATE

To delete data from a table, use DELETE

To delete all data from a table, use TRUNCATE TABLE

average value of expr for the group.

count of expr values within the group.

maximum value of expr values within the group.

minimum value of expr values within the group.

sum of expr values within the group.

To count the rows in the table

To count the non-NULL values in a column

To count unique values in a column

To count the animals by species

To get the average, minimum, and maximum ages by habitat

To import data from a CSV file into a table

To export data from a query to a CSV file

To change the type of a value, use the :: operator

You may also use CAST(). This is useful when the name of the type contains spaces, e.g., double precision

To fetch the city names that are not Berlin

To fetch the city names that start with a 'P'

To fetch the city names that start with any letter followed by 'ublin' (like Dublin in Ireland or Lublin in Poland)

To concatenate two strings, use the || operator or the CONCAT() function

Note that with ||, the result is NULL if any of the strings is NULL

In contrast, CONCAT() ignores NULL

To get the count of characters in a string

To convert all letters to lowercase

To convert all letters to uppercase

To capitalize the first letter of each word in a string, use INITCAP()

To get a part of a string

To replace a part of a string

To get the number of seconds in a week

In PostgreSQL, the division operator / performs an integer division on integer arguments

Avoid integer division by including at least one non-integer argument

To get the remainder of a division

To round a number to its nearest integer

To round a number to three decimal places (NUMERIC arguments only)

To get the absolute value of a number

To get the square root of a number:

To fetch the names of the cities whose rating values are not missing

To replace NULL in a query with something meaningful

To save yourself from division by 0 errors

a date with a resolution of one day; stores the year, month, and day in the YYYY-MM-DD format.

a time of day with a resolution of one microsecond; stores the hours, minutes, seconds, and fractional seconds in the HH:MM:SS.SSSSSS format.

a timestamp with the time zone; stores the date and the time along with the corresponding time zone information. The range is from '4713-11-24 00:00:00' BC to '294276-12-31 23:59:59' AD.

a timestamp without the time zone; stores the date and the time. PostgreSQL handles TIMESTAMP values automatically with time zone conversion.

a duration of time, such as 3 days, 4 hours, and 30 minutes.

to get the current time.

to get the current date.

to get the current timestamp with both of the above.

To create a date, time, or datetime value, write it as a string and cast it to the desired type.

You may skip casting in simple conditions. The database knows what you mean.

An interval is the duration between two points in time.

To define an interval: INTERVAL '3 days'

This syntax consists of the INTERVAL keyword, a value, and a time part keyword (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND).

You may combine different INTERVALs using the + or - operator

To extract a part of a date, use EXTRACT()

You may also use DATE_PART(). It extracts specific components from a date or timestamp.

To add or subtract an INTERVAL from a date, time, or timestamp

To find the difference between two dates in days

DATE_TRUNC() in PostgreSQL truncates date or timestamp values to the specified time units.