I once wrote a paper called "Postgres Goofiness" to help professionals familiar with Oracle become familiar with the quirks and idiosyncrasies of the Postgres implementation of SQL. To be fair, and remove all bias, some of the Postgres implementation may be more ANSI-standard at times than Oracle, but not always (ANSI is a governing body that approves valid SQL syntax). Still, Oracle (and Microsoft SQL Server and IBM DB2) are certainly still more prevalent, and most folks are far more familiar with them.
Here is a less-than-comprehensive list. Perhaps you can think of some more and comment. I picked these because they immediately stand out and are often quite different and less than intuitive.
Compared to Oracle, Postgres SQL is quite similar, and is based upon ANSI standards which have been around for 40+ years.
However, the implementations of the standard are often curiously different, especially to an Oracle professional.
For example:
1 - The LIMIT clause.
Select * from huge_table limit 100: 100 rows are returned in no particular order. ROWNUM virtual column concept from Oracle does not exist.
More details on this clause and it's variations in this excellent POST.
2 - Query Parallelism
3 - Create index abx_index on schema name.abx (column_name); - Notice schema_name is not specified for the index?
It is assumed the same as the table. Specifying it generates an error!
4 - SELECT clause without FROM.
Select version();
Postgres versions are (roughly) moved up a notch yearly.
Postgres 9 was a longstanding, stable release, much like Oracle 11g.
Companies are finding it hard to justify upgrades.
Security vulnerabilities are numerous with 9, and no patches available.
5 - SYSDATE becomes NOW?
master=> select now();
now
-------------------------------
2022-06-26 13:16:29.143339+00
(1 row)
5a A more conventional syntax:
master=> select current_date;
current_date
--------------
2022-08-03
(1 row)
5b.
master=> select current_time;
current_time
--------------------
02:07:19.259544+00
(1 row)
5c.
master=> select 6;
6
----------
6
Above is completely off-the-rails illegal in Oracle but will work in SQLServer!
A more detailed post on this issue: SQL Without FROM?
6 - backslash commands replace many queries in other SQL flavors.
Examples:
\q. Quits “psql”
\d Same as describe in sqlplus
\timing same as “set timing on” - not too bad! I can remember that!!!
See the documentation for a comprehensive list. Keep in mind Postgresql originates from the eighties, when every key stroke was expensive, and every programming language strived for brevity. It was also designed to run on much smaller systems.
7 - SQL errors often have “hints” to help resolve the problem. Very similar to oerr without asking!
8 - The equivalent of sql*loader is built-in to the sql command line tool.
psql> \copy owner.table from ‘c:\local_file_name’;
Once again note the ubiquitous backslash!
9 - Dictionary views and catalog tables are similar, but have different names and often derivative functions - but they get the job done:
Select pg_size_pretty(pg_relation_size(‘your_object_name’));
10- Sometimes, these combinations of catalog views and functions must be combined in odd ways to produce the same output that one Oracle dictionary view produces, but they DO WORK!
SELECT
relname AS "relation",
pg_size_pretty (
pg_total_relation_size (C .oid)
) AS "total_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND C .relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY
pg_total_relation_size (C .oid) DESC
LIMIT 5;
11 - Other oddities - a clause to use results of a subquery - sort of like “with”?
execute query into result;
12 - The term “table” and “relation” are often interchangeable in the documentation.
13 - “Tuples” are the equivalent to blocks, more or less.
I will cut it short at a "baker's dozen". I am sure you will encounter many, many more.
Feel free to comment or suggest still more, and I will revise or write another post on this subject!
