Why not used standart SQL commands?

On Saturday, January 8, 2022, Ali Koca <kinetixcicocuk@gmail.com> wrote:

Hello,

I'm seeing \dt used for "show tables", \l used for "show databases". Why
not standart SQL syntax words? Why specified PostgreSQL commands?
I can't figure out that.

Less characters to type (and remember). Assuming psql is available is
reasonable given the forum. Also, the output is generally better.

David J.

Ali Koca <kinetixcicocuk@gmail.com> writes:

I'm seeing \dt used for "show tables", \l used for "show databases". Why
not standart SQL syntax words? Why specified PostgreSQL commands?
I can't figure out that.

"show tables" isn't standard either.

regards, tom lane

On 1/8/22 10:25, Ali Koca wrote:

Hello,

I'm seeing \dt used for "show tables", \l used for "show databases". Why
not standart SQL syntax words? Why specified PostgreSQL commands?
I can't figure out that.

AFAIK, SHOW TABLES is a MySQL thing.

See here:

https://www.sqltutorial.org/sql-list-all-tables/

If you are looking for a 'standard' method then look at:

https://www.postgresql.org/docs/current/information-schema.html

in particular:

https://www.postgresql.org/docs/current/infoschema-tables.html

Ali

--
Adrian Klaver
adrian.klaver@aklaver.com

[ please keep the mailing list cc'd ]

Ali Koca <kinetixcicocuk@gmail.com> writes:

How? I'm newbie

There is an ISO SQL standard, and "show" isn't in it.

Every SQL implementation has its own extensions of
the standard, as well as quirks and deviations from
the standard. It sounds like you're used to mysql's
extensions ... you'll need to learn about postgres's.

regards, tom lane

Ali Koca schrieb am 08.01.2022 um 19:25:

I'm seeing \dt used for "show tables", \l used for "show databases". Why
not standart SQL syntax words? Why specified PostgreSQL commands?
I can't figure out that.

The only thing the SQL standard defines to gain access to information about
tables, schemas and catalogs (=databases) is the information_schema views.

There is no "show database" or "show tables" statement in the SQL standard.

Btw: \dt is a command specific to the psql client - it's not a general
"SQL command" in Postgres

On Sat, Jan 8, 2022 at 7:25 PM Ali Koca <kinetixcicocuk@gmail.com> wrote:

Hello,

I'm seeing \dt used for "show tables", \l used for "show databases". Why
not standart SQL syntax words? Why specified PostgreSQL commands?
I can't figure out that.

As others pointed out, "show databases" and "show tables" are not from any
standard.

Since you seem to come from MySQL, here is a list terms translated to
PostgreSQL:

https://wiki.postgresql.org/wiki/MySQL_PostgreSQL_terminology

Regards,

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Den 2022-01-08 kl. 19:25, skrev Ali Koca:

Hello,

I'm seeing \dt used for "show tables", \l used for "show databases". Why
not standart SQL syntax words? Why specified PostgreSQL commands?
I can't figure out that.

Ali

The psql backslash commands is just helper commands that wraps the
actual sql. With the -E, --echo-hidden option when starting psql you can
see the real sql.

The \dt command for instance uses this query:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN
'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN
'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

/Nicklas Avén