Introduction to the PostgreSQL cheat sheet
PostgreSQL PostGIS Geometry/Geography/Box Types box2d A box composed of x min, ymin, xmax, ymax. Often used to return the 2d enclosing box of a geometry. Box3d A box composed of x min, ymin, zmin, xmax, ymax, zmax. Often used to return the 3d extent of a geometry or collection of geometries.
The PostgreSQL offers scaling, storage flexibility, and ease of database management for developers, DBAs, and other technical professionals. Because of PostgreSQL’s ability to offer architecture stability along with an extensive coding capability, the plethora of commands and statements are endless. That’s a good thing. What’s even better is having a handy list of the SQL commands you’re likely to use regularly. Well, some of the most popular ones are featured here in Part 2 of The PostgreSQL Cheat Sheet, so take a few moments to review it now.
Importing Data from CSV in PostgreSQL Insert multiple rows List the tables in SQLite opened with ATTACH Meta commands in PSQL Outputting Query Results to Files with o Random Sequences Show Tables in Postgres SQL Cheat Sheet. PostgreSQL 8.3 Cheat Sheet: Summary of new and old PostgreSQL functions and SQL constructs complete xml query and export, and other new 8.3 features, with examples. SQLite: If you are looking for a free and lite fairly SQL-92 compliant relational database, look no further. How to query with PostgreSQL wildcards like a Pro. Querying in SQL often requires wildcard queries. Below you can find a cheat sheet with all common operations. And even more below an explanation on how it works. A cheat sheet for all PostgreSQL wildcard/like operations. PostgreSQL Configuration Cheat Sheet PostgreSQL comes with a ton of configuration settings, but with documentation scattered all over The Manual! Here is a handy cheat sheet of all Postgres 10 configuration options, split into categories, with clickable links that go to the official documentation.
Prerequisites to using PostgreSQL SQL commands in psql
- Be sure that the object-relational database management systemPostgreSQL installed on your OS.
- At the command line psql, check the PostgreSQL version with the command
psql -V
. - You’ll also need PostgreSQL database accessibility to try out the samples shown in this PostgreSQL cheat sheet.
NOTE: Here are some useful tips regarding commands. When writing code in psql, always end your SQL statement with a semicolon (
;
). If you don’t and push Return, your code will extend to the next line of code without breaking at the place where you wanted it to end.Another tip for writing SQL statements is to remember to enclose your strings in PostgreSQL with singular quotes, not doubles. This one is correct:
'string here'
for example. A syntax error will happen if you use doubles.Finally, to quickly get away from a long results list or a command you started or completed, push CTRL+C.
Accessing PostgreSQL using the ‘psql’ command line interface
- From your server on your localhost, connect to your database in PostgreSQL with the command
psql
.
psql postgres |
- Alternatively to the above command, input your username, host, and then database name to make a Postgres database connection.
psql -U some_username -h 127.0.0.1 -d some_database |
NOTE: See the flags in the above code.
- Your username in Postgres comes after the flag
-U
- The IP address or host domain goes after the flag
-h
- The database PostgreSQL name is inputted after the flag
-d
PostgreSQL cheat sheet of useful SQL queries and commands
This PostgreSQL cheat sheet contains some of the most frequently-used commands to perform basic computing software programming functions so that you can code with efficiency.
Use ‘SELECT’ to get a Postgres table’s column names
- Obtain the names of a table’s columns with the
information_schema
command:
SELECT*FROM information_schema.columns WHERE some_table ='some_table'; |
- You can also access the names of a public table’s column with the
table_schema
command:
SELECT*FROM information_schema.columns WHERE table_schema ='public' AND some_table ='some_table'; |
PostgreSQL cheat sheet commands for modifying tables
- Use the
INSERT INTO
statement to add a value to a table:
INSERTINTO some_table(col1, col2)VALUES(value1,value2); |
NOTE: The above command adds two columns and two values. See (col1, col2) and (value1, value2) in the SQL statement. Place a comma after each indicated column or value within the parenthesis when you have more than one to add to a table.
- Use the
INSERT INTO
andSELECT
statement to add a table’s column data to a different table:
INSERTINTO table1(column_list)SELECT column_list FROM table2; |
- Save table updates with the
UPDATE
statement:
- Use the
UPDATE
statement for condition matching in a table:
UPDATE some_table SET col1 = new_val, col2 = new_val WHERE condition; |
- Use the
DELETE FROM
statement to remove all records from a table in Postgres:
NOTE: An option to remove all records from a table in Postgres is to use the command
TRUNCATE
followed by naming the table you want to delete.- Use the
DELETE FROM
statement to remove data pertaining to a condition:
DELETEFROM some_table WHERE condition; |
PostgreSQL cheat sheet for managing databases
- Make a database with the
CREATE DATABASE
statement:
NOTE: A database may already exist, so to avoid raising an exception, use the
IF NOT EXISTS
clause after the CREATE DATABASE
statement.- Use
DROP DATABASE
to delete a database forever:
DROPDATABASE[IFNOTEXISTS] db_name; |
NOTE: You won’t get an error message prompt if you add the
IF NOT EXISTS
clause.Use the a PostgreSQL ‘SELECT’ statement to query data
Postgresql Commands Cheat Sheet
Query data in a table with these various
SELECT
statement command.- Add the wildcard asterisk
*
symbol to have every record in a PostgreSQL table return in the results page.
- Indicate which columns to query:
SELECT col1, col2 FROM some_table; |
- Query a filtered table:
Postgresql Tutorial Pdf
- Include the clause
WHERE
to specify the columns you want to query:
SELECT some_col, another_col FROM some_table WHERE some_int >50; |
- Perform a column query and give the column an alias with this statement:
Here are some commands to query in PostgreSQL using operators.
- Use the operator
LIKE
to query a character string pattern match:
To query a set of operations in PostgreSQL:
Using the
LIKE
operator:SELECT*FROM some_table WHERECOLUMNLIKE'%value'; |
![Sql injection cheat sheet postgresql Sql injection cheat sheet postgresql](/uploads/1/1/2/1/112187645/623324821.png)
- Use the operator
BETWEEN
to query a table range:
SELECT*FROM some_table WHERECOLUMNBETWEEN low AND high; |
- Use the operator
IN
to add more than one condition or value to yourWHERE
clause in your statement:
SELECT*FROM some_table WHERECOLUMNIN(value1, value2); |
- Use the operator
UNION
to merge at least twoSELECT
statement results sets.
- Use the operator
EXCEPT
to put together twoSELECT
statements that will only return rows that are not in the second statement.
SELECT*FROM table1 EXCEPTSELECT*FROM table2; |
- Use the operator
INTERSECT
to have the results set to reflect every record picked by at least two statements. If a record fails to match each query, it won’t appear in the results set:
- Use the clause
LIMIT
to return a limited amount of rows. In the statement below,OFFSET
rows are skipped:
SELECT*FROM some_table LIMITLIMIT OFFSET offset ORDERBY column_name; |
Here are a few SQL statements for querying multiple tables.
- Make a multiple Postgres table query with the
INNER JOIN
statement:
SELECT*FROM table1 INNERJOIN table2 ON conditions |
- Make a multiple Postgres table query with the
LEFT JOIN
statement:
- Make a multiple Postgres table query with the
FULL OUTER JOIN
statement:
SELECT*FROM table1 FULLOUTERJOIN table2 ON conditions |
- Make a multiple Postgres table query with the
CROSSJOIN
statement:
![Cheat sheet postgresql pdf Cheat sheet postgresql pdf](/uploads/1/1/2/1/112187645/418312401.jpg)
SELECT*FROM table1 CROSSJOIN table2 ON conditions |
- Make a multiple Postgres table query with the
NATURAL JOIN
statement:
SELECT*FROM table1 NATURALJOIN table2 ON conditions |
Here are a few common
SELECT
statements for displaying table rows.- Use the wildcard
(*)
to show all table rows:
- Use the
ORDER BY
clause to sort the order of table rows in the results:
SELECT column_name FROM some_table ORDERBY column_name [ASC|DESC]; |
- To group table data, use the clause
GROUP BY
:
Postgresql Sql Injection Cheat Sheet
- Use both clauses
HAVING
andGROUP BY
to specify the criteria for grouping the data results:
SELECT*FROM some_table GROUPBY col1 HAVING condition; |
Conclusion on the PostgreSQL cheat sheet
Postgresql Sql Cheat Sheet
A helpful PostgreSQL cheat sheet is meant to help you reduce the time you spend on your daily coding projects. Make it an accessible reference of common SQL statements and other commands are at your fingertips. This way, you won’t have to wonder if the syntax is off the mark. The most beneficial result is that you’ll likely cut down on unnecessarily raised exceptions every day.