Firebird, MySQL, SQL Server, PostgreSQL and Oracle: A Practical Guide with Commands

They all speak SQL. None of them speaks the same SQL.

Anyone working with data in Brazil sooner or later runs into all five: the Firebird behind the legacy system that has been running for twenty years, the MySQL behind the website, the SQL Server behind the ERP, the PostgreSQL behind the new product and the Oracle behind the bank or telecom. This guide shows what changes between them — with the equivalent command side by side.

Contents

• 1 · Overview and comparison
• 2 · PostgreSQL
• 3 · MySQL and MariaDB
• 4 · SQL Server
• 5 · Oracle
• 6 · Firebird
• 7 · The same task in all five
• 8 · Migrating between databases
• 9 · How to choose

1. Overview and comparison

Three observations that save a lot of arguing:

• None of them is "the best". Context, team and cost weigh more than the feature list.
• Standard SQL covers most of the daily work. The differences show up in pagination, auto-increment, date/text functions, upsert and administration — exactly what this guide details.
• Migrating is expensive. Choosing a database is a decision measured in years, not sprints.

2. PostgreSQL

The closest to the SQL standard and the most extensible of the five. It has become the market default for a new product — and it is what this blog uses in its examples.

Connecting:

psql -h localhost -U app -d loja
psql "postgresql://app:senha@localhost:5432/loja"

Creating a table and auto-increment:

CREATE TABLE clientes (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- modern SQL standard
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(200) UNIQUE NOT NULL,
  dados JSONB NOT NULL DEFAULT '{}',
  criado_em TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Typical queries and functions:

SELECT * FROM produtos ORDER BY preco DESC LIMIT 10 OFFSET 20;
SELECT nome || ' — ' || categoria AS descricao FROM produtos;
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) FROM pedidos GROUP BY 1;
SELECT payload->>'tipo' AS tipo FROM eventos WHERE payload @> '{"tipo":"compra"}';

Upsert:

INSERT INTO metricas (dia, acessos) VALUES (CURRENT_DATE, 1)
ON CONFLICT (dia) DO UPDATE SET acessos = metricas.acessos + 1;

Administration:

-- User and minimum permission
CREATE USER app_web WITH PASSWORD 'senha-forte';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_web;

-- Metadata
\dt                      -- tables (psql)
\d clientes              -- the table's structure
SELECT * FROM pg_stat_activity WHERE state <> 'idle';

-- Execution plan
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pedidos WHERE cliente_id = 42;

# Backup and restore
pg_dump -Fc -U app loja > loja.dump
pg_restore -d loja -j 4 loja.dump

Strong points: indexable JSONB, extensions (PostGIS, pgvector, TimescaleDB), complete CTEs and window functions, custom types, GIN/GiST/BRIN indexes, logical replication.

Gotchas: MVCC demands attention to vacuum and bloat; unquoted identifiers become lowercase; a connection is a process, so pooling (PgBouncer) is practically mandatory at scale.

3. MySQL and MariaDB

The database of the web. Simple to stand up, a giant ecosystem, present on any hosting plan. MariaDB is the community fork, compatible for the most part.

Connecting:

mysql -h localhost -u app -p loja

Creating a table and auto-increment:

CREATE TABLE clientes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE,
  criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Always use utf8mb4. MySQL's old utf8 stores at most 3 bytes per character and breaks on emoji.

Typical queries and functions:

SELECT * FROM produtos ORDER BY preco DESC LIMIT 10 OFFSET 20;
SELECT CONCAT(nome, ' — ', categoria) AS descricao FROM produtos;
SELECT DATE_FORMAT(criado_em, '%Y-%m') AS mes, SUM(total) FROM pedidos GROUP BY 1;
SELECT JSON_EXTRACT(dados, '$.tipo') FROM eventos;

Upsert:

INSERT INTO metricas (dia, acessos) VALUES (CURRENT_DATE, 1)
ON DUPLICATE KEY UPDATE acessos = acessos + 1;

Administration:

CREATE USER 'app_web'@'%' IDENTIFIED BY 'senha-forte';
GRANT SELECT, INSERT, UPDATE ON loja.* TO 'app_web'@'%';
FLUSH PRIVILEGES;

SHOW TABLES;
DESCRIBE clientes;
SHOW CREATE TABLE clientes;
SHOW FULL PROCESSLIST;
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;

mysqldump --single-transaction --routines --triggers loja > loja.sql
mysql loja < loja.sql

Strong points: simplicity, replication with GTID, an enormous body of knowledge, great read performance with a well-configured InnoDB.

Gotchas: historically permissive with invalid data (check the sql_mode); DDL is not transactional; CTEs and window functions only from 8.0 onwards; text comparison is case-insensitive by default, which surprises anyone coming from PostgreSQL.

4. SQL Server

The enterprise database of the Microsoft world. First-class tooling (SSMS, Profiler, Query Store) and natural integration with .NET, Power BI and Azure.

Connecting:

sqlcmd -S localhost -U sa -P senha -d loja

Creating a table and auto-increment:

CREATE TABLE clientes (
  id BIGINT IDENTITY(1,1) PRIMARY KEY,
  nome NVARCHAR(120) NOT NULL,
  email NVARCHAR(200) NOT NULL UNIQUE,
  criado_em DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

Typical queries and functions:

SELECT TOP 10 * FROM produtos ORDER BY preco DESC;

SELECT * FROM produtos ORDER BY preco DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT nome + ' — ' + categoria AS descricao FROM produtos;
SELECT FORMAT(criado_em, 'yyyy-MM') AS mes, SUM(total) FROM pedidos GROUP BY FORMAT(criado_em, 'yyyy-MM');
SELECT ISNULL(telefone, 'sem contato') FROM clientes;

Upsert:

MERGE INTO metricas AS destino
USING (SELECT CAST(GETDATE() AS DATE) AS dia) AS origem
ON destino.dia = origem.dia
WHEN MATCHED THEN UPDATE SET acessos = destino.acessos + 1
WHEN NOT MATCHED THEN INSERT (dia, acessos) VALUES (origem.dia, 1);

Administration:

CREATE LOGIN app_web WITH PASSWORD = 'senha-forte';
CREATE USER app_web FOR LOGIN app_web;
ALTER ROLE db_datareader ADD MEMBER app_web;

SELECT name FROM sys.tables;
EXEC sp_help 'clientes';

-- The most expensive queries
SELECT TOP 10 total_worker_time/execution_count AS media_cpu, text
FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY media_cpu DESC;

SET STATISTICS IO, TIME ON;   -- query diagnostics

BACKUP DATABASE loja TO DISK = 'D:\bkp\loja.bak' WITH COMPRESSION, INIT;
RESTORE DATABASE loja FROM DISK = 'D:\bkp\loja.bak' WITH RECOVERY;

Strong points: Query Store (plan history), columnstore indexes for BI, Always Encrypted, integration with the Microsoft ecosystem, mature graphical tooling.

Gotchas: per-core licensing weighs on the budget; NOLOCK scattered through the code is a dirty read disguised as an optimisation; the collation set at installation is laborious to change afterwards.

5. Oracle

The database of large-scale critical operations. Availability and scale features that the others took decades to catch up with — at a proportional cost.

Connecting:

sqlplus app/senha@//localhost:1521/XEPDB1

Creating a table and auto-increment:

CREATE TABLE clientes (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- 12c+
  nome VARCHAR2(120) NOT NULL,
  email VARCHAR2(200) NOT NULL UNIQUE,
  criado_em TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

-- Before 12c: sequence + trigger
CREATE SEQUENCE seq_clientes START WITH 1 INCREMENT BY 1;

Typical queries and functions:

SELECT * FROM produtos ORDER BY preco DESC FETCH FIRST 10 ROWS ONLY;
SELECT nome || ' — ' || categoria AS descricao FROM produtos;
SELECT TO_CHAR(criado_em, 'YYYY-MM') AS mes, SUM(total) FROM pedidos GROUP BY TO_CHAR(criado_em, 'YYYY-MM');
SELECT NVL(telefone, 'sem contato') FROM clientes;
SELECT SYSDATE FROM dual;                       -- every query needs a FROM
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual;

Upsert:

MERGE INTO metricas d
USING (SELECT TRUNC(SYSDATE) AS dia FROM dual) o
ON (d.dia = o.dia)
WHEN MATCHED THEN UPDATE SET d.acessos = d.acessos + 1
WHEN NOT MATCHED THEN INSERT (dia, acessos) VALUES (o.dia, 1);

Administration:

CREATE USER app_web IDENTIFIED BY "senha-forte";
GRANT CONNECT, RESOURCE TO app_web;
GRANT SELECT, INSERT, UPDATE ON loja.pedidos TO app_web;

SELECT table_name FROM user_tables;
DESC clientes;
SELECT sql_id, elapsed_time/executions AS media FROM v$sql ORDER BY media DESC FETCH FIRST 10 ROWS ONLY;

EXPLAIN PLAN FOR SELECT * FROM pedidos WHERE cliente_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# RMAN
rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> RESTORE DATABASE;

Strong points: an extremely mature PL/SQL, RAC (active-active cluster), Data Guard, advanced partitioning, AWR for historical diagnostics, flashback (taking a table back to an earlier instant).

Gotchas: an empty string is treated as NULL — unique behaviour among the five; FROM dual is mandatory; complex licensing (a feature enabled by mistake turns into an invoice); identifiers are uppercase by default.

6. Firebird

The least talked about and more widespread than it looks: thousands of Brazilian commercial systems — point of sale, management, retail automation — have been running on Firebird for decades. Heir to Borland's InterBase, opened up in 2000.

Why it survives: the whole server fits into a few megabytes, the database is a single .fdb file, it needs practically no DBA and it can run embedded inside the application. For software distributed to hundreds of customers, that is worth more than any advanced feature.

Connecting:

isql -user SYSDBA -password senha /dados/loja.fdb

Creating a table and auto-increment:

CREATE TABLE clientes (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,   -- Firebird 3+
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE,
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Firebird 2.5: generator + trigger
CREATE GENERATOR gen_clientes_id;
SET TERM ^ ;
CREATE TRIGGER trg_clientes_bi FOR clientes ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
  IF (NEW.id IS NULL) THEN NEW.id = GEN_ID(gen_clientes_id, 1);
END^
SET TERM ; ^

Typical queries and functions:

SELECT FIRST 10 SKIP 20 * FROM produtos ORDER BY preco DESC;   -- its own syntax
SELECT * FROM produtos ORDER BY preco DESC ROWS 21 TO 30;      -- alternative
SELECT nome || ' — ' || categoria AS descricao FROM produtos;
SELECT EXTRACT(YEAR FROM criado_em) AS ano, SUM(total) FROM pedidos GROUP BY 1;
SELECT COALESCE(telefone, 'sem contato') FROM clientes;
SELECT CURRENT_DATE FROM RDB$DATABASE;   -- the equivalent of Oracle's dual

Upsert:

UPDATE OR INSERT INTO metricas (dia, acessos)
VALUES (CURRENT_DATE, 1)
MATCHING (dia);

Procedure and block execution:

SET TERM ^ ;
CREATE PROCEDURE total_do_cliente (p_id BIGINT)
RETURNS (total DECIMAL(12,2)) AS
BEGIN
  SELECT COALESCE(SUM(total), 0) FROM pedidos WHERE cliente_id = :p_id INTO :total;
  SUSPEND;
END^
SET TERM ; ^

SELECT * FROM total_do_cliente(42);

Administration:

CREATE USER app_web PASSWORD 'senha-forte';
GRANT SELECT, INSERT, UPDATE ON clientes TO app_web;

-- Metadata lives in the RDB$ system tables
SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0;
SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CLIENTES';

SET PLAN ON;   -- shows the plan of the following queries in isql

# Logical backup (gbak) and maintenance (gfix)
gbak -b -user SYSDBA -password senha /dados/loja.fdb /backup/loja.fbk
gbak -c -user SYSDBA -password senha /backup/loja.fbk /dados/loja_restaurado.fdb

gfix -sweep -user SYSDBA -password senha /dados/loja.fdb   # clears old versions
gstat -h /dados/loja.fdb                                    # file statistics

Strong points: a minimal footprint, a database in a single file, an embedded edition, simple installation and operation, a free licence, excellent backward compatibility.

Gotchas: a smaller ecosystem and community; old versions (2.5) still in production without modern features; it needs a periodic sweep so record versions do not pile up; monitoring tooling far more limited than that of the other four.

7. The same task in all five

This is the table to keep open next to the editor.

Limiting rows:

Auto-increment:

Current date and time:

Concatenating text:

Handling NULL:

Insert or update (upsert):

Listing tables:

Seeing the execution plan:

Backup:

8. Migrating between databases

What always causes work, in order of pain:

1. Types. Oracle's NUMBER, VARCHAR2, DATETIME2, MySQL's TINYINT(1) as a boolean — each one needs explicit mapping.
2. Auto-increment. Sequence, generator, identity and auto_increment change name, syntax and behaviour with respect to the current value.
3. Procedural code. PL/SQL, T-SQL, PL/pgSQL and PSQL are different languages. Procedures and triggers are a rewrite, not a conversion.
4. NULL and the empty string. Leaving Oracle always reveals logic that relied on '' IS NULL.
5. Collation and accents. Text ordering and comparison change — and reports come out in a different order.
6. Pagination and date functions. Omnipresent in the code, they have to be swept one by one.

A strategy that usually works:

# 1. Convert the schema with a tool and review it by hand
pgloader mysql://user@host/loja postgresql://user@host/loja

# 2. Load the data into staging and validate counts and totals
# 3. Run the application against the new database in parallel (shadow), comparing results
# 4. Cut over with logical replication to shorten the downtime window

And the rule that prevents a two-year project: migrate out of concrete need (end of support, licence cost, a missing feature), never out of technical preference.

9. How to choose

A short path to the decision:

• New product, team with no strong preference → PostgreSQL. The market standard, no licence, and it handles JSON, text search, vectors and time series without bringing in another database.
• Website, CMS, simple web app, shared hosting → MySQL/MariaDB. It runs anywhere and everyone has already touched it.
• Microsoft shop, .NET, Power BI, a team used to SSMS → SQL Server. The integration pays for part of the licence.
• Large-scale mission-critical work, with a requirement for RAC/Data Guard and a support contract → Oracle, with your eyes open about the cost.
• Distributed desktop system, point of sale, embedded database, no DBA at the customer site → Firebird. That is exactly the niche where it cannot be beaten.

And the criterion that usually outweighs all the others: the database your team knows how to operate at three in the morning. An advanced feature is no substitute for someone who understands what is happening when the system stops.