Databases/SQL — PostgreSQL — get tables and columns

by

In order to get the list of the available tables of a database in PostgreSQL, connect to that particular database and run:

SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE ‘pg\\_%’
AND tablename NOT LIKE ‘sql\\_%’;

In order to get the list of columns for a particular table in that database, run:

SELECT attname FROM pg_attribute, pg_type
WHERE typname = ‘table_name
AND attrelid = typrelid
AND attname NOT IN (‘cmin’, ‘cmax’, ‘ctid’, ‘oid’, ‘tableoid’, ‘xmin’, ‘xmax’);

Advertisements

4 Responses to “Databases/SQL — PostgreSQL — get tables and columns”

  1. tinkasoft Says:

    In 8.1 and above (and possibly previous releases), the first query should be:

    SELECT tablename FROM pg_tables
    WHERE tablename NOT LIKE ‘pg%’
    AND tablename NOT LIKE ‘sql%’

    – ‘_’ is a wild card character too
    – your query will return a number of system tables like sql_features, sql_packages, etc.

  2. rvernica Says:

    Thanks for the comment!

    I updated the post. To be more accurate, ‘_’ can be escaped with ‘\’.

  3. Get tables and columns in PostgreSQL - Sybase Tutorial Says:

    […] Here is a good tutorial show you how to Get tables and columns in PostgreSQL: In order to get the list of the available tables of a database in PostgreSQL, connect to that particular database and run: […]

  4. PostgreSQL: Lista tablic w bazie | sigvatrblog Says:

    […] Na podstawie: https://notfaq.wordpress.com/2006/07/29/sql-postgresql-get-tables-and-columns/ […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: