-- Connect to a databasepsql-hhost-Uuser-ddbname-- List all databases\l-- Connect to a database within psql\cdbname-- List all tables\dt-- Describe a table\dtable_name-- Exit psql\q
Data Types
-- PostgreSQL supports the following data typesINTEGER-- Integer valueSERIAL-- Auto-incrementing integerBIGINT-- Large integer valueREAL-- Floating-point numberDOUBLEPRECISION-- Double-precision floating-point numberNUMERIC-- Exact number with selectable precisionCHAR(n)-- Fixed-length character stringVARCHAR(n)-- Variable-length character stringTEXT-- Variable-length character string (no limit)DATE-- Calendar date (year, month, day)TIME-- Time of day (hours, minutes, seconds)TIMESTAMP-- Date and timeBOOLEAN-- Logical Boolean (true/false)BYTEA-- Binary data ("byte array")
Creating Tables
-- Create a tableCREATETABLEtable_name(column1SERIALPRIMARYKEY,column2VARCHAR(100)NOTNULL,column3INTEGERDEFAULT0);-- Create a table with a foreign keyCREATETABLEchild_table(idSERIALPRIMARYKEY,parent_idINTEGER,FOREIGNKEY(parent_id)REFERENCESparent_table(id));
Inserting Data
-- Insert a single rowINSERTINTOtable_name(column2,column3)VALUES('Hello',123);-- Insert multiple rowsINSERTINTOtable_name(column2,column3)VALUES('World',456),('PostgreSQL',789);
Querying Data
-- Select all rowsSELECT*FROMtable_name;-- Select specific columnsSELECTcolumn1,column2FROMtable_name;-- Select with a conditionSELECT*FROMtable_nameWHEREcolumn1=1;-- Select with sortingSELECT*FROMtable_nameORDERBYcolumn1DESC;-- Select with a limitSELECT*FROMtable_nameLIMIT10;
Updating Data
-- Update specific rowsUPDATEtable_nameSETcolumn2='Updated'WHEREcolumn1=1;-- Update all rowsUPDATEtable_nameSETcolumn2='Updated';
Deleting Data
-- Delete specific rowsDELETEFROMtable_nameWHEREcolumn1=1;-- Delete all rowsDELETEFROMtable_name;
Joins
-- Inner joinSELECT*FROMtable1INNERJOINtable2ONtable1.column=table2.column;-- Left joinSELECT*FROMtable1LEFTJOINtable2ONtable1.column=table2.column;-- Right joinSELECT*FROMtable1RIGHTJOINtable2ONtable1.column=table2.column;-- Full joinSELECT*FROMtable1FULLJOINtable2ONtable1.column=table2.column;
Aggregate Functions
-- Count rowsSELECTCOUNT(*)FROMtable_name;-- Sum valuesSELECTSUM(column1)FROMtable_name;-- Average valuesSELECTAVG(column1)FROMtable_name;-- Minimum valueSELECTMIN(column1)FROMtable_name;-- Maximum valueSELECTMAX(column1)FROMtable_name;-- Group bySELECTcolumn2,COUNT(*)FROMtable_nameGROUPBYcolumn2;
Transactions
-- Begin a transactionBEGIN;-- Commit a transactionCOMMIT;-- Rollback a transactionROLLBACK;
Indexes
-- Create an indexCREATEINDEXindex_nameONtable_name(column1);-- Create a unique indexCREATEUNIQUEINDEXunique_index_nameONtable_name(column1);-- Drop an indexDROPINDEXindex_name;
Views
-- Create a viewCREATEVIEWview_nameASSELECTcolumn1,column2FROMtable_nameWHEREcolumn1=1;-- Select from a viewSELECT*FROMview_name;-- Drop a viewDROPVIEWview_name;
Importing and Exporting Data
-- Import data from a CSV fileCOPYtable_nameFROM'/path/to/file.csv'DELIMITER','CSVHEADER;-- Export data to a CSV fileCOPYtable_nameTO'/path/to/file.csv'DELIMITER','CSVHEADER;
User Management
-- Create a new userCREATEUSERusernameWITHPASSWORD'password';-- Grant privileges to a userGRANTALLPRIVILEGESONDATABASEdbnameTOusername;-- Revoke privileges from a userREVOKEALLPRIVILEGESONDATABASEdbnameFROMusername;-- Drop a userDROPUSERusername;