Re: really novice with Postgres !
От | Steve Crawford |
---|---|
Тема | Re: really novice with Postgres ! |
Дата | |
Msg-id | 51FBC99B.4040809@pinpointresearch.com обсуждение исходный текст |
Ответ на | Re: really novice with Postgres ! (Jean MAURICE <mauricejea@numericable.fr>) |
Список | pgsql-novice |
Hi Jean, First, as a novice you will soon find someone telling you that the convention on the PostgreSQL mailing list is to "bottom post", i.e. post your reply below the message you are replying to so people can read the full history top to bottom. On 08/02/2013 06:31 AM, Jean MAURICE wrote: > Hi Tovo, > > I saw there is a view 'tables' that gives the list of the tables. How > do we use a view in Postgres ? > I tried > SELECT * FROM tables; > but the error is something like 'the relation 'tables' doesn't exist' > (my Postgres is in French !) > Initial learning curves are always steep. In PostgreSQL tables are organized into sets called "schemas." In a default installation the tables will typically be in a schema called "public." It's easy to be unaware that this is happening because, also by default, the "public" schema is in your search path. You can check your search path with the "show search_path;" statement. Another schema is the "pg_catalog" schema which is visible by default and contains lots of tables/pseudo-tables/views that underpin the inner workings of PostgreSQL but which can be useful for the types of queries you seek. The information_schema is not in your search path by default but also contains tables/views that can help you (and which are generally easier to use than the pg_catalog tables). To access a table either explicitly (due to the same table-name existing in multiple schemas in the search path) or because the schema is not in the search path, simply prepend the schema to the table, i.e. "select * from information_schema.columns where table_name='foo' and table_schema='public';". I have some more questions : >>> Is there a SQL command to >>> - test if a database exists Look in pg_catalog.pg_database >>> - test if a table exists information_schema.tables >>> - get the list of the tables of a database information_schema.tables again >>> - get the structure (list of fields) of a table information_schema.columns One trick is to run the "psql" terminal with the "-E" option. This will cause it to show the actual commands it sends to the server in response to the various backslash commands. For example if you want to see a list of table (\dt) it would show the command it sent to the server: ********* QUERY ********** 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' WHEN 'f' THEN 'foreign table' 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 WHERE c.relkind IN ('r','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** You can use that query as a jumping off point to learn where the data is located or to modify the query to suit your use. Cheers, Steve
В списке pgsql-novice по дате отправления: