![]() ![]() This document may be printed freely as long as this notice stays intact. () / Peter Freitag (), All Rights Reserved. Here is a handy cheat sheet of all Postgres 10 configuration options. The nodes in the lower level of the tree are scan nodes. PostgreSQL comes with a ton of configuration settings, but with documentation. The structure of a query plan is a tree of plan nodes. PostgreSQL devises a query plan for each query it receives. Questions, comments, criticism, or requests can be directed HereĬopyright © 2005 Foundeo Inc. One of the first steps we can take to understand how to improve the performance of our database is to analyze the queries that are made. ![]() VALUES (1, 'one') Update a row UPDATE table SET foo = 'bar' WHERE id = 1 Delete a row DELETE FROM table WHERE id = 1 UPDATE accounts SET balance += 50 WHERE id = 1 īasic SQL Get all columns and rows from a table SELECT * FROM table Add a new row INSERT INTO table (column1,column2) Table Maintenance VACUUM ANALYZE table Reindex a database, table or index REINDEX DATABASE dbName Show query plan EXPLAIN SELECT * FROM table Import from a file COPY destTable FROM '/tmp/somefile' Show all runtime parameters SHOW ALL Grant all permissions to a user GRANT ALL PRIVILEGES ON table TO username Perform a transaction INSERT INTO tableName (intColumn, charColumn) VALUES ($1, $2) Ĭreate a Function CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integerĪS 'SELECT date_part(''month'', $1)::integer ' The PostgreSQL cheat sheet page provides you with the common PostgreSQL commands and statements that enable you to work with PostgreSQL quickly and. Throughout this cheat sheet, we'll be using the sample data airbnblistingsdenoting rental apartments on Airbnb. PostgreSQL is a good place to start since it’s close to standard SQL syntax and is easily adapted to other dialects. PostgreSQL Cheat Sheet PostgreSQL Cheat Sheet CREATE DATABASE CREATE DATABASE dbName CREATE TABLE (with auto numbering integer id) CREATE TABLE tableName (ĭateCreated timestamp DEFAULT current_timestampĪdd a primary key ALTER TABLE tableName ADD PRIMARY KEY ( id) Create an INDEX CREATE UNIQUE INDEX indexName ON tableName (columnNames) Backup a database (command line) pg_dump dbName > dbName.sql Backup all databases (command line) pg_dumpall > pgbackup.sql Run a SQL script (command line) psql -f script.sql databaseName Search using a regular expression SELECT column FROM table WHERE column ~ 'foo.*' The first N records SELECT columns FROM table LIMIT 10 Pagination SELECT cols FROM table LIMIT 10 OFFSET 30 Prepared Statements Popular dialects include MySQL, SQLite, SQL Server, Oracle SQL, and more. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |