Thread: Why not used standart SQL commands?

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

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 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

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