Guide to Basic PostgreSQL Commands and Differences from MariaDB (MySQL)

As an advanced MariaDB user transitioning to PostgreSQL, it's important to understand the key differences in commands, utilities, and features between the two systems. While both are powerful relational database management systems (RDBMS) and share similarities in SQL syntax, PostgreSQL introduces unique concepts and commands. This guide focuses on those differences to help you get up to speed quickly.

1. Command-Line Tools

Accessing the Database

MariaDB/MySQL:

mysql -u username -p

PostgreSQL:

psql -U username -d database_name

Common `psql` Meta-Commands

In PostgreSQL's interactive terminal (psql), meta-commands start with a backslash (\):

\l — List all databases.
\c database_name — Connect to a database.
\dt — List tables in the current schema.
\d table_name — Describe a table's structure.
\du — List all roles (users).
\q — Quit psql.

2. User and Role Management

Creating Users/Roles

MariaDB/MySQL:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

PostgreSQL:

CREATE ROLE username WITH LOGIN PASSWORD 'password';

Note: In PostgreSQL, a "role" can act as both a user and a group. Adding WITH LOGIN allows the role to log in.

Granting Privileges

MariaDB/MySQL:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

PostgreSQL:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

3. Database Management

Creating and Dropping Databases

Creating a Database:
MariaDB/MySQL:
```
CREATE DATABASE database_name;
```

PostgreSQL:

CREATE DATABASE database_name OWNER username;

Dropping a Database:
Same in both:
```
DROP DATABASE database_name;
```

Listing Databases

MariaDB/MySQL:

SHOW DATABASES;

PostgreSQL:
Using psql meta-command:
```
\l
```
Or SQL query:
```
SELECT datname FROM pg_database;
```

4. Data Types Differences

Auto-Increment Columns

MariaDB/MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:

id SERIAL PRIMARY KEY

SERIAL is a pseudo-type that creates an integer column with an associated sequence.

Boolean Type

MariaDB/MySQL:
No native BOOLEAN; uses TINYINT(1).
PostgreSQL:
Native BOOLEAN type with values TRUE or FALSE.

String Data Types

MariaDB/MySQL:
VARCHAR, TEXT, BLOB, etc.
PostgreSQL:
Similar types, but TEXT is often used in place of large VARCHAR.

5. String Concatenation

MariaDB/MySQL:

SELECT CONCAT(first_name, ' ', last_name) FROM users;

PostgreSQL:

SELECT first_name || ' ' || last_name FROM users;

6. Conditional Expressions

IF vs CASE

MariaDB/MySQL:

SELECT IF(condition, true_result, false_result);

PostgreSQL:

SELECT CASE WHEN condition THEN true_result ELSE false_result END;

7. Upsert Operations

Insert or Update

MariaDB/MySQL:

INSERT INTO table_name (...) VALUES (...) ON DUPLICATE KEY UPDATE column=VALUE;

PostgreSQL:

INSERT INTO table_name (...) VALUES (...) ON CONFLICT (unique_column) DO UPDATE SET column=EXCLUDED.column;

EXCLUDED refers to the values proposed for insertion.

8. Limit and Offset

MariaDB/MySQL:

SELECT * FROM table_name LIMIT 10 OFFSET 20;

PostgreSQL:

Same syntax, but also supports FETCH:

SELECT * FROM table_name LIMIT 10 OFFSET 20;
SELECT * FROM table_name OFFSET 20 FETCH NEXT 10 ROWS ONLY;

9. Date and Time Functions

Current Date and Time

MariaDB/MySQL:

SELECT NOW();

PostgreSQL:

SELECT CURRENT_TIMESTAMP;

Date Arithmetic

MariaDB/MySQL:

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

PostgreSQL:

SELECT CURRENT_TIMESTAMP + INTERVAL '7 days';

10. Regular Expressions

MariaDB/MySQL:

SELECT * FROM table_name WHERE column REGEXP 'pattern';

PostgreSQL:

Case-sensitive:

SELECT * FROM table_name WHERE column ~ 'pattern';

Case-insensitive:

SELECT * FROM table_name WHERE column ~* 'pattern';

11. Configuration Files

Configuration Locations

MariaDB/MySQL:
Main configuration in my.cnf or my.ini.
PostgreSQL:
Main configuration in postgresql.conf.
Client authentication in pg_hba.conf.

Note: pg_hba.conf controls host-based authentication and is critical for security setup.

12. Data Import/Export

Importing Data

MariaDB/MySQL:

LOAD DATA INFILE 'file_path' INTO TABLE table_name;

PostgreSQL:

COPY table_name FROM 'file_path' DELIMITER ',' CSV HEADER;

Exporting Data

MariaDB/MySQL:

SELECT * FROM table_name INTO OUTFILE 'file_path';

PostgreSQL:

COPY table_name TO 'file_path' DELIMITER ',' CSV HEADER;

13. Sequences

MariaDB/MySQL:
Uses AUTO_INCREMENT for auto-incrementing columns.
PostgreSQL:
Uses sequences, which can be created independently.
```
CREATE SEQUENCE sequence_name;
```
Can be associated with a table column.

14. Case Sensitivity in Identifiers

MariaDB/MySQL:
Generally case-insensitive for identifiers.
PostgreSQL:
Converts unquoted identifiers to lowercase.

Quoted identifiers are case-sensitive.

SELECT * FROM "TableName";  -- References exactly "TableName"

15. Stored Procedures and Functions

Defining Functions

MariaDB/MySQL:

DELIMITER $$
CREATE FUNCTION function_name(parameters) RETURNS return_type
BEGIN
  -- function body
END$$
DELIMITER ;

PostgreSQL:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
  -- function body
END;
$$ LANGUAGE plpgsql;

Stored Procedures

MariaDB/MySQL:
Supports stored procedures.
PostgreSQL:

Introduced CREATE PROCEDURE in version 11.

CREATE PROCEDURE procedure_name(parameters)
LANGUAGE plpgsql
AS $$
BEGIN
  -- procedure body
END;
$$;

16. Views and Materialized Views

Views

Same in both:

CREATE VIEW view_name AS SELECT ...;

Materialized Views

MariaDB/MySQL:
Does not support materialized views natively.
PostgreSQL:

CREATE MATERIALIZED VIEW view_name AS SELECT ...;

Refreshing Materialized Views:

REFRESH MATERIALIZED VIEW view_name;

17. JSON Support

MariaDB/MySQL:
Supports JSON data type and functions.
PostgreSQL:
Offers advanced JSON support with json and jsonb data types.

JSON functions and operators:

SELECT data->'key' FROM table_name;    -- Returns JSON object
SELECT data->>'key' FROM table_name;   -- Returns text

18. Extension System

MariaDB/MySQL:
Uses plugins.
PostgreSQL:
Supports extensions to add functionalities.
```
CREATE EXTENSION extension_name;
```
Common extensions include hstore, uuid-ossp, pgcrypto, and PostGIS.

19. Foreign Data Wrappers (FDW)

MariaDB/MySQL:
Limited external table capabilities.
PostgreSQL:
Supports FDWs to query external data sources (e.g., other databases, files).

Example:

CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'remote_db');
CREATE USER MAPPING FOR current_user SERVER foreign_server OPTIONS (user 'remote_user', password 'password');
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO local_schema;

20. Transaction Control

Explicit Transactions:

MariaDB/MySQL:

START TRANSACTION;
-- SQL statements
COMMIT;  -- or ROLLBACK;