PostgreSQL Syntax
Understanding the basic structure of PostgreSQL commands
🗄️ What is PostgreSQL Syntax?
PostgreSQL syntax refers to the rules and structure for writing database commands. It defines how to create, read, update, and delete data using SQL statements that PostgreSQL understands and executes.
-- This is a simple PostgreSQL query
SELECT * FROM users;
Output:
id | name | email ---+-----------+------------------ 1 | John Doe | [email protected] 2 | Jane Smith| [email protected]
Key Syntax Elements
Statements
Commands that perform actions
SELECT, INSERT, UPDATE, DELETEKeywords
Reserved words with special meaning
FROM, WHERE, ORDER BY, JOINIdentifiers
Names for tables and columns
users, products, order_idSemicolon
Ends each SQL statement
SELECT * FROM users;🔹 Basic Query Structure
PostgreSQL queries follow a specific order. The SELECT statement retrieves data from tables. You specify columns after SELECT and the table name after FROM. The semicolon marks the end of the statement.
-- Basic SELECT syntax
SELECT column1, column2
FROM table_name;
-- Example
SELECT name, email
FROM customers;Output:
name | email -----------+------------------ Alice | [email protected] Bob | [email protected]
🔹 Case Sensitivity Rules
PostgreSQL keywords are case-insensitive, but it's convention to write them in uppercase. Table and column names are case-sensitive when quoted. Unquoted identifiers are automatically converted to lowercase by PostgreSQL for consistency.
-- These are equivalent
SELECT name FROM users;
select name from users;
SeLeCt NaMe FrOm UsErS;
-- Case-sensitive with quotes
SELECT "Name" FROM "Users";🔹 Comments in PostgreSQL
Comments help document your SQL code. Single-line comments start with two dashes and continue to the end of the line. Multi-line comments begin with /* and end with */, allowing explanations across multiple lines.
-- This is a single-line comment
SELECT * FROM products; -- Get all products
/*
This is a multi-line comment
It can span multiple lines
*/
SELECT name, price FROM products;🔹 String Literals
Text values in PostgreSQL must be enclosed in single quotes. Double quotes are reserved for identifiers like table and column names. To include a single quote within a string, use two single quotes together as an escape sequence.
-- Correct string syntax
SELECT * FROM users WHERE name = 'John';
-- Escaping single quotes
SELECT * FROM books WHERE title = 'It''s a Great Day';
-- Using double quotes for identifiers
SELECT "First Name" FROM "User Table";🔹 Common Syntax Patterns
PostgreSQL uses consistent patterns across different operations. INSERT adds new records, UPDATE modifies existing data, and DELETE removes records. Each statement follows a logical structure with keywords that clearly indicate the operation being performed on the database.
-- INSERT pattern
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
-- UPDATE pattern
UPDATE table_name
SET column1 = value1
WHERE condition;
-- DELETE pattern
DELETE FROM table_name
WHERE condition;