This is a field guide to the PostgreSQL relational database management system, or Postgres.

Like most RDBMSs, Postgres groups defined sets of data into rows. The rows are grouped together into columns. Columns are grouped together into tables. Tables are grouped together into schemas. Schemas are grouped together into databases. Databases are grouped together into clusters managed by a PostgreSQL server instance.

Postgres can be managed with the psql command-line program.

Here is basic Postgres command syntax:

  1. -- This is a comment.
    -- Comment

    All characters after “--” are ignored by Postgres until the end of the line.

  2. CREATE TABLE table_name (
    -- “CREATE TABLE” Command

    This command creates a new, empty table in the current database with the schema provided.

    The name of the table must be unique from any other table, sequence, index, view, foreign table, or specified data type in the table itself.

    Postgres is agnostic on capitalization with key word and identifiers, except within double quotes.

    Tables have several system columns, including oid (object ID), tableoid, xmin, cmin, xmax, cmax, and ctid.

  3. column_name01 int,
    -- Column Name and Type Specification

    This specification specifies a new column with the name “column_name1” and the type “int”.

    Postgres supports the many data types, in additional to user-created types.

    Postgres also supports some data type not typically used in user-created tables, such as object identifier types and pseudo-types.

  4. column_name02 int,
    -- “Numeric” Type Specification

    Postgres supports four numeric data types:

    • Integer:
      • smallint
      • integer
      • bigint
    • Arbitrarily Precise:
      • decimal(precision, (optional)scale)
      • numeric(precision, (optional)scale)

      Decimal and numeric are equivalent.

      Precision includes both digits both and after the decimal point; scale only includes numbers after the decimal point (in the mantissa).

    • Floating-Point:
      • real
      • double precision
      • float(precision)

      Real usually has 24 bits in the mantissa; double precision usually has 53 bits.

      Floating-point types also include “Infinty”, “-Infinity”, and “NaN” as special values.

    • Serial:
      • smallserial
      • serial
      • bigserial

      Serial types are not true types but help create unique identifier columns similar to the auto-incrementation of other databases.

  5. column_name03 money,
    -- “Money” Type Specification

    Postgres supports a money type with fixed fractional precision (two decimal points) that accepts type currency formatting, such as ‘$1,000.00’.

  6. column_name04 char(10),
    -- “Character”/Text Type Specification

    Postgres supports three character types:

    • character varying(n), varchar(n)
    • character(n), char(n)
    • text

    The first type has a variable length with a character limit of (n). The second has a fixed length of (n) characters, padded out with blank characters. The third has a variable, unlimited length.

    Varchar and text are generally more efficient than char, due to the extra, padded length of char entry.

  7. column_name05 bytea,
    -- “Binary” Type Specification

    Postgres supports a binary format of variable length.

    Postgres also supports a BLOB type, or binary large object, which is largely the same.

  8. column_name06 timestamp,
    -- “Date/Time” Type Specification

    Postgres supports six types of date/time types:

    • timestamp, timestamp without time zone
    • timestamp with time zone
    • date
    • time, time without time zone
    • time with time zone
    • interval

  9. column_name07 boolean,
    -- “Boolean” Type Specification

    Postgres supports one boolean type.

  10. column_name08 custom_enum_type,
    -- Custom, Enumerated Type Specification

    Postgres supports custom, enumerated types as sets of static, ordered values.

    See “Create Type”, below.

  11. column_name09 point,
    -- “Geometric” Type Specification

    Postgres supports eight types of geometric data:

    • point: (x,y)
    • line: {A,B,C}
    • lseg: ((x1,y1),(x2,y2))
    • box: ((x1,y1),(x2,y2))
    • path: (closed) ((x1,y1),…)
    • path: (open) [(x1,y1),…)]
    • polygon: ((x1,y1),…)
    • circle: <(x,y),r>

  12. column_name10 cidr,
    -- “Network Address” Type Specification

    Postgres supports three types of network address data:

    • cidr
    • inet
    • macaddr

  13. column_name11 bit(8)
    -- “Bit String” Type Specification

    Postgres supports two kinds of bit string data:

    • bit(n)
    • bit varying(n)

  14. column_name12 tsvector,
    -- “Text Search” Type Specification

    Postgres supports two types of text search data:

    • tsvector
    • tsquery

  15. column_name13 uuid,
    -- “UUID” Type Specification

    Postgres supports one type of UUID data.

    For distributed systems, this provides a better guarantee of uniqueness than incrementation.

  16. column_name14 xml,
    -- “XML” Type Specification

    Postgres supports one type of XML data.

    Postgres supplies functions to convert data to XML and back again.

  17. column_name15 json,
    -- “JSON” Type Specification

    Postgres supports one type of JSON data.

    Postgres supplies functions to convert data to JSON and back again.

  18. column_name16 int[],
    -- Array Type Specification

    Postgres supports one type of array data.

    Multiple-dimension arrays are allowed with the syntax int[][] (or whatever data type).

  19. column_name17 custom_composite_type,
    -- Composite Type Specification

    Postgres supports one type of composite data, separate from enumerations.

  20. column_name18 int4range,
    -- “Range” Type Specification

    Postgres supports six kinds of built-in range types:

    • int4range: integer
    • int8range: bigint
    • numrange: numeric
    • tsrange: timestamp without time one
    • tstzrange: timestamp with time zone
    • daterange: date

    More range types can be created with the “CREATE TYPE” command.

  21. column_name19 int NOT NULL,
    -- “NOT NULL” Constraint

    This constraint requires that the column has no null value.

  22. column_name20 UNIQUE,
    -- “UNIQUE” Constraint

    This constraint requires that the column has a unique value.

  23. column_name21 int UNIQUE NOT NULL,
    -- Multiple Constraints

    Columns can accept multiple constraints. In this case, these combined constraints are equivalent to the “PRIMARY KEY” constraint.

  24. column_name22 int DEFAULT 10,
    -- “DEFAULT” Value Specification

    This specification assigns a default value to a column. If no value if supplied, this one is used.

    An expression, like nextval(), can be used to produced serial values from sequence objects.

    The “serial” data type does something similar.

  25. column_name23 int CHECK (column_name23 > 0),
    -- “CHECK” Constraint

    This constraint specifies that any value satisfy the check.

  26. column_name24 int CONSTRAINT check_name1 CHECK (column_name23 > 0)
    -- Named “CHECK” Constraint

    Naming checks allows better debugging if a constraint is violated.

  27. CHECK(column_name25 > column_name22),
    -- Table Constraint

    Constraints can exist for tables in addition to columns.

  28. CHECK(column_name26 > column_name22 AND column_name23 >= column_name22)
    -- Logical Operator Compound “CHECK” Constraint

    There are three logical operators in Postgres: AND, OR, and NOT.

    There are three values in Postgres’ logic system: TRUE, FALSE, and NULL.

  29. column_name27 int PRIMARY KEY,
    -- “PRIMARY KEY” Constraint

    This constraint ensures the unique, existing value for a column, making it a primary key. It is equivalent to the combined “UNIQUE” and “NOT NULL” keys.

  30. PRIMARY KEY (column_name25, column_name26),
    -- DELETE THIS ROW BEFORE RUNNING Multiple Column “PRIMARY KEY” Constraint

    This constraint ensures the unique, existing value for a combination of columns, making them a primary key.

    Each table must not have multiple primary keys.

  31. column_name 28 int REFERENCES table_name2 (column_name)
    -- “FOREIGN KEY” Constraint

    This constraint references another column in another table, making it a foreign key.

    If the foreign table column is not specified, the name is assumed to be that of the referencing column.

  32. );
    -- End of Command Operator

    “;” terminates a Postgres command.

  33. CREATE TABLE table_name2 (
    -- “ALTER TABLE” Command
  34. column_name 29 int
  35. ) INHERITS (table_name1);
    -- “INHERITS” Inheritance Command

    This command causes the newly created table to inherit the columns and proprties of the parent table.

    Queries run on a table can include their inheritance children.

  36. CREATE VIEW view_name AS SELECT * FROM table_name1;
    -- “CREATE VIEW” Command

    This command creates a new view.

    Views have many uses.

  37. ALTER TABLE table_name;
    -- “ALTER TABLE” Command

    This command alters an existing table.

  38. ALTER TABLE ADD COLUMN column_name29 int;
    -- “ADD COLUMN” Command

    This command adds a column to the specified table.

    This command can be combined with checks and defaults. If no default is specified, the rows in the table don’t have to be physically updated.

  39. ALTER TABLE DROP COLUMN column_name30 int;
    -- “DROP COLUMN” Command

    This column drops a column from the specified table.

  40. ALTER TABLE ALTER COLUMN column_name28 SET NOT NULL;
    -- “ALTER COLUMN” Command

    This column alters a column by adding a constraint.

    Other actions including dropping a constraint, changing a data type (if all values in that column can be converted), changing a default value for future rows, renaming a column, and renaming a table.

  41. GRANT ALL ON table_name TO username;
    -- “GRANT” Grant Privileges Command

    This command grants privileges to a user.

    Privileges include:

    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • TRUNCATE
    • REFERENCES
    • TRIGGER
    • CREATE
    • CONNECT
    • TEMPORARY
    • EXECUTE
    • USAGE
    • ALL

    The special user “PUBLIC” encompasses every user on the system. Groups can also be specified.

  42. REVOKE ALL ON table_name FROM username;
    -- “REVOKE” Revoke Privileges Command

    This command revokes privileges from a user.

  43. DROP TABLE table_name2;
    -- “DROP TABLE” Command

    This command removes and deletes a table.

    It has three optional parameters:

    • IF EXISTS: don’t throw an error if the table doesn’t exist.
    • CASCADE: automatically drop objects that depend on the table (not including other tables with foreign keys)
    • RESTRICT: refuse to drop the table if other objects depend on it (default).

  44. CREATE SCHEMA schema_name;
    -- “CREATE SCHEMA” Schema Creation Command

    This command creates a schema, which acts like a bulked-up namespace for PostgreSQL.

    By default, all action takes place inside the “PUBLIC” schema.

  45. SHOW search_path;
    -- “SHOW” Search Path Command

    This command shows the current search path.

  46. SET search_path TO schema_name;
    -- “SET” Search Path Command

    This command sets the current search path.

  47. DROP SCHEMA schema_name;
    -- “DROP SCHEMA” Schema Removal Command

    This command drops the specified schema.

  48. CREATE TYPE custom_enum_type AS ENUM( ‘value1’, ‘value2’, ‘value3’ );
    -- MOVE THIS LINE ABOVE THE “CREATE TABLE” COMMAND ABOVE “CREATE TYPE” Enumeration Command

    This command creates a new data type with a static, ordered set of values.

    CREATE TYPE can also create a number of other data types, including composites and ranges.

  49. CREATE TYPE custom_composite_type AS ( column_name1 int, column_name2 text );
    -- MOVE THIS LINE ABOVE THE “CREATE TABLE” COMMAND ABOVE “CREATE TYPE” Composite Command

    This command creates a new data type with a static, ordered set of values.

  50. INSERT INTO table_name VALUES (1, ‘value’);
    -- “INSERT” Row Insertion Command

    This command inserts a row into a table.

    If fewer insertion values are given than columns in the table, Postgres fills in columns left-to-right and fills in the remaining with their default values.

    It has several variations.

  51. INSERT INTO table_name (column_name2, column_name1) VALUES (‘value’, 1);
    -- Row Insertion with Column Names

    Row insertion with named columns allows arbitrary order.

  52. INSERT INTO table_name (column_name1, column_name2) VALUES (1, DEFAULT);
    -- Row Insertion with Explicit Default Values

    “DEFAULT” will explicitly insert a column’s default value.

  53. INSERT INTO table_name (column_name1) VALUES (1), (2), (3);
    -- Multiple Row Insertion

    Multiple values can be inserted into multiple rows.

  54. UPDATE table_name SET column_name1 = 1, column_name2 = column_name2 * 10 WHERE column_name1 = 1;
    -- “UPDATE” Update Command

    This command update the rows selected in the “WHERE” clause with the values in the “SET” clause.

    If the “WHERE” clause is not provided, all rows are updated.

  55. DELETE FROM table_name WHERE column_name1 = 1;
    -- “DELETE” Delete Command

    This command deletes the rows in a table selected in the “WHERE” clause.

    If no “WHERE” clause is provided, all rows are deleted.

  56. SELECT random();
    -- “SELECT” Query Command

    This command queries the database for data.

  57. SELECT DISTINCT * FROM table_name;
    -- “DISTINCT” Key Word

    This key word filters a query return for distinct results and only displays the first one.

    Its antithesis, enabled by default, is the “ALL” keyword.

  58. SELECT * FROM table_name LIMIT 1;
    -- “LIMIT” Key Word

    This key word limits the number of results that are returned.

    When using “LIMIT” and “OFFSET”, it’s important to use “ORDER BY”, otherwise the order will be unpredictable.

  59. SELECT * FROM table_name OFFSET 1;
    -- “OFFSET” Key Word

    This number removes the first results from a query.

    When using “LIMIT” and “OFFSET”, it’s important to use “ORDER BY”, otherwise the order will be unpredictable.

  60. SELECT * FROM table_name;
    -- “FROM” Query Clause

    This command queries the database for data and returns it as a view.

    In this case, the command queries all data from table_name.

  61. SELECT column_name1, column_name2 FROM table_name;
    -- Multiple Column Query

    This command queries two columns from table_name.

    If both columns are numeric or convertible to it, operations like addition can be performed on them.

  62. SELECT table_name1.column_name1 FROM table_name1, table_name2;
    -- Multiple Table Query

    This command queries one column from table_name1.

    Selecting from multiple tables using commas is roughly equivalent to “CROSS JOIN”.

  63. SELECT ROW(1, ‘value’);
    -- Row Constructor

    This command constructs a row.

  64. SELECT ARRAY[1, ‘value’];
    -- Array Constructor

    This command constructs an array.

  65. SELECT column_name1 + column_name2 FROM table_name;
    -- Mathematical Operator

    These are Postgres’ mathematical operators:

    • +: addition
    • -: subtraction
    • *: multiplication
    • /: division
    • %: modulo (remainder)
    • ^: exponentiation
    • |/: square root
    • ||/: cube root
    • !: factorial
    • !!: factorial (prefix operator)
    • @: absolute value
    • &: bitwise AND
    • |: bitwise OR
    • #: bitwise XOR
    • ~: bitwise NOT
    • <<: bitwise shift left
    • >>: bitwise shift right

  66. SELECT abs(column_name1) FROM table_name;
    -- Mathematical Function

    Postgres has many mathematical functions.

  67. SELECT column_name1 || column_name2 FROM table_name;
    -- String, Byte String, and Bit String Function

    Postgres has many string functions and many byte string functions and bit string functions.

  68. SELECT tochar(timestamp, column_name1) FROM table_name;
    -- Data Type Conversion/Formatting Function

    Postgres has many data type conversion and formatting functions.

  69. SELECT tochar(timestamp, column_name1)+1, age(column_name06) FROM table_name;
    -- Date/Time Conversion/Formatting Functions and Operators

    Postgres has many date/time conversion and formatting functions.

  70. SELECT enum_first(column_name08) FROM table_name;
    -- Enum Function

    Postgres has many enum functions.

  71. SELECT box(column_name09, column_name09) FROM table_name;
    -- Geometric Function

    Postgres has many geometric functions.

  72. SELECT abbrev(column_name10) FROM table_name;
    -- Network Address Function

    Postgres has many network address functions.

  73. SELECT numnode(column_name12) FROM table_name;
    -- Text Search Function

    Postgres has many text search functions.

  74. SELECT xmlconcat(column_name14) FROM table_name;
    -- XML Function

    Postgres has many XML functions.

  75. SELECT json_array_length(column_name15) FROM table_name;
    -- JSON Function

    Postgres has many JSON functions.

  76. SELECT lastval() FROM table_name;
    -- Sequence Function

    Postgres has many sequence functions.

  77. SELECT cardinality(column_name16) FROM table_name;
    -- Array Function

    Postgres has many array functions.

  78. SELECT lower(column_name18) FROM table_name;
    -- Range Function

    Postgres has many array functions.

  79. SELECT max(SELECT * FROM table_name) FROM table_name;
    -- Aggregate Function

    Aggregate functions return a unitary value for an aggregate value.

    Postgres has many aggregate functions.

  80. SELECT * FROM generate_series(1,10);
    -- Generating Function

    Postgres has many generating functions, which generate sets (or more than one row of results).

  81. SELECT current_database();
    -- System Information Function

    Postgres has many system information functions.

  82. SELECT current_setting('datestyle');
    -- System Administration Function

    Postgres has many system information functions.

    They are usually restricted to superusers.

  83. SELECT * FROM table_name1 CROSS JOIN table_name2;
    -- “CROSS JOIN” Join Command

    This command joins two tables.

    In the absence of parantheses, JOIN clauses nest left-to-right.

    “CROSS JOIN” creates a joined table with all the columns of table_name1 and table_name2 combined and all the rows of table_name1 multiplied by table_name2.

    Each row in table_name1 is joined to all rows in table_name2.

  84. SELECT * FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- “INNER JOIN” Command

    This command joins two tables for whatever rows the “ON” clause evaluates to “true”.

  85. SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- Alternate “INNER JOIN” Command
  86. SELECT * FROM table_name1 LEFT OUTER JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- “LEFT JOIN” Command

    This command performs an inner join. Then, for each row in table_name1 that does not satisfy the join condition for any row in table_name2, a joined row is added with null values in the table_name2 columns.

    The joined table always has at least one row for each row in table_name1.

  87. SELECT * FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- Alternate “LEFT JOIN” Command
  88. SELECT * FROM table_name1 RIGHT OUTER JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- “RIGHT JOIN” Command

    This command performs an inner join. Then, for each row in table_name2 that does not satisfy the join condition for any row in table_name1, a joined row is added with null values in the table_name1 columns.

    The joined table always has at least one row for each row in table_name2.

  89. SELECT * FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- Alternate “RIGHT JOIN” Command
  90. SELECT * FROM table_name1 FULL OUTER JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- “FULL JOIN” Command

    This command combines the “LEFT JOIN” and “RIGHT JOIN” commands.

    The joined table always has at least one row for each row in table_name1 and table_name2.

  91. SELECT * FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name1;
    -- Alternate “FULL JOIN” Command
  92. SELECT * FROM table_name1 JOIN table_name2 USING (column_name1, column_name2);
    -- “USING” Shorthand Clause

    This clause is shorthand for “ON table_name1.column_name1 = table_name2.column_name1 AND table_name1.column_name2 = table_name2.column_name2”.

  93. SELECT * FROM table_name1 NATURAL JOIN table_name2;
    -- “NATURAL” Shorthand Clause

    This clause is shorthand for the “USING” clause using all shared column names.

  94. SELECT * FROM table_name1 as T1 JOIN table_name2 as T2 ON T1.column_name1 = T2.column_name2;
    -- “AS” Alias Clause

    This clause creates a table or column alias for a particular object for its particular query. It can save some typing.

    It’s useful for joining a table to itself: you can give a table separate aliases.

  95. SELECT * FROM (SELECT * table_name1);
    -- Nested Query or Subquery

    Queries can be nested using parantheses.

  96. SELECT * FROM LATERAL (SELECT * table_name1) lateral_name;
    -- “LATERAL” Preceding Reference Key Word

    This clause allows subqueries to reference columns provided by preceding “FROM” items. Without it, each subquery is evaluated independently and cannot reference another “FROM” item.

  97. SELECT * FROM table_name WHERE column_name1 = ‘value’
    -- “WHERE” Comparison Operator Query Clause

    This clause limits the results of a query according to the boolean value of the clause for a given row.

    The most common comparison operators are:

    • =: equality
    • <>: non-equality
    • !=: non-equality
    • >: greater than
    • <: less than
    • >=: greater than or equal to
    • <=: less than or equal to

  98. AND column_name2 BETWEEN 0 and 1
    -- “BETWEEN” “WHERE” Condition

    This comparison detects if the value is between the provided values. This works for numeric, date, and character data types.

  99. AND column_name3 IN (1, 2, 3)
    -- “IN” “WHERE” Condition

    This comparison detects if the value is in a provided list.

  100. AND column_name3 IN ANY [array_name]
    -- “ANY” “WHERE” Condition

    This operato yields true if the operator is true for any value in the right-hand array.

  101. AND column_name3 IN SOME [array_name]
    -- Alternate “ANY” Condition
  102. AND column_name3 IN ALL [array_name]
    -- Alternate “ANY” Condition

    This operato yields true if the operator is true for all values in the right-hand array.

  103. AND ROW(column_name3) IS DISTINCT FROM ROW(column_name4)
    -- Alternate “ANY” Condition

    This operato yields true if the operator is true for all values in the rows.

  104. AND column_name4 IS NULL;
    -- “IS NULL” “WHERE” Condition

    This comparison detects if the value is NULL (or NOT NULL, for the “IS NOT NULL” operator).

  105. AND column_name4 LIKE '_alu_';
    -- “LIKE” Missing Character “WHERE” Condition

    This comparison detects if a value is like a missing character.

  106. AND column_name4 LIKE '%l%';
    -- “LIKE” Multiple Missing Characters “WHERE” Condition

    This comparison detects if the value is like multiple missing characters.

  107. AND column_name4 LIKE '%[k-m]%';
    -- Bracket Expansion “WHERE” Condition

    This comparison detects if the value is like a range of characters.

  108. AND column_name4 = #01/01/1970#;
    -- Date “WHERE” Condition

    This comparison detects if a value is similar to a date.

  109. AND column_name4 SIMILAR TO ‘abc’;
    -- Regular Expression “WHERE” Condition

    This comparison detects if the value is similar to a string using regular expressions.

  110. AND column_name4 ~ ‘abc’;
    -- Posix Regular Expression “WHERE” Condition

    This comparison detects if the value is similar to a string using POSIX regular expressions.

  111. SELECT * FROM table_name1 GROUP BY column_name1;
    -- “GROUP BY” Clause

    This clause aggregates distinct values for the provided column and provides only a single instance of each.

    For other columns, aggregate functions are required to produce meaningful output, since multiple lines have been reduced to only distinct lines for the chosen column.

  112. SELECT * FROM table_name1 GROUP BY column_name1 HAVING column_name1 = 1;
    -- “HAVING” Clause

    This clause acts as a “WHERE” clause for “GROUP BY” clauses, returning only distinct values whose aggregate passes the “HAVING” condition.

  113. SELECT * FROM table_name1 ORDER BY column_name1, column_name2;
    -- “ORDER BY” Clause

    This clause orders the results according to the column, in order of precedence left-to-right, with column_name2 breaking column_name1’s ties.

  114. SELECT * FROM table_name1 UNION SELECT * FROM table_name2
    -- “UNION” Set Operator

    This operator adds the rows of the second query to the first, eliminating duplicate rows unless “UNION ALL” is used.

  115. SELECT * FROM table_name1 INTERSECT SELECT * FROM table_name2
    -- “INTERSECT” Set Operator

    This operator returns all rows that are in both the first and second queries.

  116. SELECT * FROM table_name1 EXCEPT SELECT * FROM table_name2
    -- “EXCEPT” Set Operator

    This operator returns all rows that are in the first query but not the second.

  117. WITH statement_name1 AS ( SELECT * FROM table_name1 ), statement_name2 AS ( SELECT * FROM table_name1 ) SELECT * FROM statement_name;
    -- “WITH” Query Clause

    This clause allows a query to be assigned an alias for another query.

    It can be used to create recursive queries.

  118. SELECT *, CASE WHEN column_name1=1 THEN ‘one’ ELSE ‘?’ END FROM table_name1;
    -- “CASE” if/then/else Expression

    This expression performs an actual if a condition is met.

  119. SELECT COALESCE( column_name1, “reserve value”) FROM table_name1;
    -- “COALESCE” Expression

    This expression returns the first of its arguments that is not null.

  120. SELECT * FROM table_name1 WHERE EXISTS (SELECT * FROM table_name1)
    -- “EXISTS” Clause

    This clause returns a boolean value of true if the subquery returns at least one result.

    The subquery is immediately terminated afterward.

  121. EXPLAIN SELECT * FROM table_name1;
    -- “EXPLAIN” Query Plan Statistics Command

    This command reveals the plan nodes for the given query.

    It is useful in debugging and optimizing a query. Other useful tips are here.

  122. ANALYZE VERBOSE;
    -- “ANALYZE” Analytics Command

    This command collects statistics about the database, storing them in the pg_statistic system catalog.

    It’s useful for the query planner when optimizing queries.

  123. --

Optimization tips:

  • Table Partitioning
  • Indexing
  • Normalization tips:

    • First Normal Form:
      • no repeating groups in individual tables
      • separate table for each set of related data
      • identify each set of related data with a primary key
      • Codd’s definition: values in the domain on which each relation is defined are required to be atomic (that is, not decomposed into smaller pieces by the DBMS)
    • Second Normal Form:
      • first normal form
      • no non-prime attribute(that is, no attribute that is not part of any candidate key)is dependent on any proper subset of any candidate key (minimal superkey, or minimal set of columns that form a distinct row) of thte table
    • Third Normal Form:
      • second normal form
      • all attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes