PostgreSQL - Setup
- Download, install, run the PostgreSQL server
- Listens for TCP connections on port 5432 (By default)
- Set your password so you can connect from your server
- Install a library for your language that will connect to the PostgreSQL server
- Create a connection in your code
- When in doubt, follow the documentation
db, err := sql.Open(
"pgx",
"postgres://postgres:" + os.Getenv("POSTGRES_PASSWORD") + "@localhost:5432/cse312",
)
PostgreSQL - Tables
- SQL is based on tables with rows and column
- Similar in structure to CSV except the fields are typed
- A table must be fully defined before data is stored
- If creating tables on server startup, use
IF NOT EXISTS
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS chat (
id SERIAL PRIMARY KEY,
author TEXT,
content TEXT,
updated BOOLEAN
)
`)
PostgreSQL - Insert Data
- Insert a record into a table using
INSERT INTO
- If using inputs from the user, always use prepared statements!
- Use placeholders in your statement and replace the values later
- Prevents values from being interpreted as SQL code
_, err := db.Exec(`
INSERT INTO chat (author, content, updated) VALUES ($1, $2, $3)
`, "Jesse", "hello chat!", false)
PostgreSQL - Retrieve Data
- Retrieve records from a table using
SELECT ... FROM
SELECT * FROM table_name will return all fields from every record in table_name
records, err := db.Query("SELECT * FROM chat")
for records.Next() {
var id int
var author string
var content string
var updated bool
records.Scan(&id, &author, &content, &updated)
}