Skip to content
Youngho Chaa cha cha
TwitterHomepage

Learning PostgreSQL

database, postgresql4 min read

The motto

  • The most advanced open-source database in the world
  • POST-Ingres: Michael Stonebraker developed Ingress and then a post-Ingres project

Installation

postgresql

brew install postgresql
brew services start postgresql@14

psql

First check if you have psql is installed.

psql --version

If it’s not, install it via brew https://www.timescale.com/blog/how-to-install-psql-on-mac-ubuntu-debian-windows/

brew doctor
brew update
brew install libpq
brew link --force libpq

Postico

Autocomplete

Press Tab, and Postico automatically tries to complete the word you are typing

Shortcuts

  • ⌘ ↩︎: Execute the current query (or the current selection, if any)
  • ⌥⌘↩︎: Execute all queries in the Query View
  • ⇧⌘T: Go the SQL Query View (used to be called the Terminal)
  • ⇥: Shift the selected line(s) to the right (increase indentation)
  • ⇧⇥: Shift the selected line(s) to the left (decrease indentation)
  • ⌘]: Alternate shortcut for shift right
  • ⌘[: Alternate shortcut for shift left
  • ^⌘←: Replace the current query with the previous query from your history.
  • ^⌘→: Replace the current query with the next query from your history.
  • ⌘/: Comment or uncomment the selected line(s) by prepending two dashes
  • ⌥⌘/: Comment (or uncomment) the selection with a block comment (/* */)
  • ^H: Look up the current word (or selection) in Dash

Commands

# connect
psql -h localhost -p 15432 -u <user>
# list databases
\l
# connect to database
\c machinelearning
# list tables
\dt

Statements

I came from a SQL Server background, and Postgres has slight variations in syntax. ENUM was interesting that I could create a custom type. Also, I have to put ; to execute the statement in psql. Hmm, I’m not a big fan of typing ; at the end of the statement (23/10/2023)

Managing table

Creating a table

This is more of a comprehensive example to create tables, constraints, and indexes. (23/10/23)

CREATE TABLE conversations (
id BIGINT GENERATED ALWAYS AS IDENTITY,
uid UUID NOT NULL,
title VARCHAR(512),
created_at TIMESTAMP NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (id),
CONSTRAINT uk_uid UNIQUE (uid)
);
CREATE TYPE author AS ENUM (
'MODEL',
'AUTHOR'
);
CREATE TABLE messages (
id BIGINT GENERATED ALWAYS AS IDENTITY,
uid UUID NOT NULL,
conversation_id BIGINT NOT NULL,
author author NOT NULL,
created_at TIMESTAMP NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (id),
CONSTRAINT uk_uid UNIQUE (uid),
CONSTRAINT fk_messages_conversations_id FOREIGN KEY (conversation_id) REFERENCES conversations (id)
)
CREATE INDEX idx_messages_conversation_id ON messages (conversation_id);

Deleting table

DROP TABLE Conversations
DROP TABLE Messages

Altering table

  • Only one altering action per statement.
  • Put ; to mark the end of the statement.
ALTER TABLE conversations DROP COLUMN user_uid;
ALTER TABLE conversations ADD COLUMN user_id bigint NOT NULL:
ALTER TABLE conversations
ADD CONSTRAINT fk_conversations_user_id
FOREIGN KEY (user_id)
REFERENCES users (user_id);

ENUM

An enum (short for "enumerated type") in PostgreSQL is a data type that comprises a static, ordered set of values. They are useful when a column is intended to only contain a limited set of possible values, which can be represented as human-readable strings, making the data self-explanatory and enforcing integrity at the database level.

Creating Enum

Enums are created with the CREATE TYPE command, followed by the AS ENUM keyword and a list of possible values.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Once created, the enum can be used as a column type in a table:

CREATE TABLE person (
name text,
current_mood mood
);

Advantages:

  1. Data Integrity: Enums restrict the column to the predefined list of values, preventing invalid data from being inserted.
  2. Clarity: They make the data more readable and self-documenting, as the allowed values are clear and meaningful.
  3. Performance: Comparisons of enum values are faster than those of text values because they are internally represented as integers.

Considerations:

  • Flexibility: Altering existing enums can be cumbersome. You can easily add new values, but removing or renaming existing ones is not straightforward and typically involves creating a new enum type.
  • Portability: Since enums are a PostgreSQL-specific feature, using them can reduce the portability of your database to other SQL systems which may not support enums or implement them differently.
  • Indexing and Constraints: While you can index enum columns and use them in constraints and joins, their special nature means that you have to be mindful of how they interact with other PostgreSQL features like array types or composite types.

Deleting Enum

DROP TYPE author_type

Alter Enum

Altering a value is not permitted. You have to create a new one, convert the value, and replace it with the old one

CREATE TYPE author_type AS ENUM (
'USER',
'BOT'
)
ALTER TABLE messages
ALTER COLUMN author TYPE author_type
USING author::text::author_type
© 2024 by Youngho Chaa cha cha. All rights reserved.
Theme by LekoArts