back to first page [..][[BR]]
back to Recipes for EDA [wiki:"CookBook Eda"]


= Listing all tables within a dabase =
{{{
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d CCM_EDA -p 5433 -U postgres 
à l'invite de commande
\d
}}}
{{{
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
}}}
{{{
select c.relname as tablename FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);
}}}

note : aucune de ces commandes ne fonctionne si la base n'est pas en UTF8
l'utilisation de pg_catalog ne semble pas compatible avec ANSI par exemple [[BR]]
Par contre

{{{
select * from information_schema.tables 
}}}
et
{{{
select table_name from information_schema.tables 
where table_schema not in ('pg_catalog','information_schema')
and table_type ='BASE TABLE'
}}}
fonctionnent dans la version 8.4 du logiciel [[BR]]
Ci dessous fonctionne
{{{
select relname from pg_stat_user_tables WHERE schemaname='public';
}}}
== PARTITION BY ==

http://www.postgresql.org/docs/current/static/tutorial-window.html