syntax to view the structure of a table

priyan

hi

In postgre I want to view the structure of a table . pls tell me is there any query for retrieving the structure of a table. pls help me.

thanks in advance
priyan....

ramesh1210

Quote:

Originally Posted by priyan

hi

In postgre I want to view the structure of a table . pls tell me is there any query for retrieving the structure of a table. pls help me.

thanks in advance
priyan....

If you are working in linux then u just give \d followed with the table name

michaelb

This will work on any supported OS, Linux, Windows, etc
Open the psql session and type \d or \d+

Excerpt from the manual:

Quote:

Originally Posted by the Postgres manual

\d [ pattern ]
\d+ [ pattern ]

For each relation (table, view, index, or sequence) matching the pattern, show all columns, their types, the tablespace (if not the default) and any special attributes such as NOT NULL or defaults, if any. Associated indexes, constraints, rules, and triggers are also shown, as is the view definition if the relation is a view. ("Matching the pattern" is defined below.)

The command form \d+ is identical, except that more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table.

Note: If \d is used without a pattern argument, it is equivalent to \dtvs which will show a list of all tables, views, and sequences. This is purely a convenience measure.

See the psql man page for more information

kirthikasubramanian

Quote:

Originally Posted by michaelb

This will work on any supported OS, Linux, Windows, etc
Open the psql session and type \d or \d+

Excerpt from the manual:

See the psql man page for more information

\d [pattern] is not working.
\d[memberphoto] is my command is correct or not.
memberphoto is my tablename please help me.........

priyan

Quote:

Originally Posted by kirthikasubramanian

\d [pattern] is not working.
\d[memberphoto] is my command is correct or not.
memberphoto is my tablename please help me.........

/d [pattern] and /d+ [pattern] is not working properly. i am getting following error
code:(sql)
select \d errorlog
error:
1.ERROR: syntax error at or near "/"
2.SQL state: 42601
3.Character: 8

this my command.

michaelb

Quote:

Originally Posted by priyan

code:(sql)
select \d errorlog
error:
1.ERROR: syntax error at or near "/"
2.SQL state: 42601
3.Character: 8

What you did is incorrect, of course is raises the error.
You cannot embed a metacommand in your SELECT query. Look at psql man page for reference.

You may also want to read the Posting Guidelines at the top of the forum, check out how to use the CODE tags, it'll be helpful for you future postings.

priyan

Quote:

Originally Posted by michaelb

What you did is incorrect, of course is raises the error.
You cannot embed a metacommand in your SELECT query. Look at psql man page for reference.

You may also want to read the Posting Guidelines at the top of the forum, check out how to use the CODE tags, it'll be helpful for you future postings.

if i write like

Expand|Select|Wrap|Line Numbers

/d errorlog

also the same error is coming

Expand|Select|Wrap|Line Numbers

 
ERROR: syntax error at or near "/"

SQL state: 42601

Character: 1

michaelb

Have you had a chance to read the psql manual I pointed to you could've noticed the correct syntax - it's \d, not /d

Expand|Select|Wrap|Line Numbers

 
postgres=# \d

               List of relations

 Schema |      Name       |   Type   |  Owner

--------+-----------------+----------+----------

 public | bar             | table    | Michael

 public | bar_id_seq      | sequence | Michael

 public | businesstab     | table    | postgres

 public | components      | table    | Michael

 public | foo             | table    | Michael
 
postgres=# \d bar

                                Table "public.bar"

 Column |         Type          |                    Modifiers

--------+-----------------------+--------------------------------------------------

 id     | integer               | not null default nextval('bar_id_seq'::regclass)

 f1     | character varying(32) |

 f2     | character varying(32) |

Indexes:

    "bar_pkey" PRIMARY KEY, btree (id)

Triggers:

    trig_audit BEFORE INSERT OR UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE ... ...

postgres=#

priyan

Quote:

Originally Posted by michaelb

Have you had a chance to read the psql manual I pointed to you could've noticed the correct syntax - it's \d, not /d

Expand|Select|Wrap|Line Numbers

 
postgres=# \d

               List of relations

 Schema |      Name       |   Type   |  Owner

--------+-----------------+----------+----------

 public | bar             | table    | Michael

 public | bar_id_seq      | sequence | Michael

 public | businesstab     | table    | postgres

 public | components      | table    | Michael

 public | foo             | table    | Michael
 
postgres=# \d bar

                                Table "public.bar"

 Column |         Type          |                    Modifiers

--------+-----------------------+--------------------------------------------------

 id     | integer               | not null default nextval('bar_id_seq'::regclass)

 f1     | character varying(32) |

 f2     | character varying(32) |

Indexes:

    "bar_pkey" PRIMARY KEY, btree (id)

Triggers:

    trig_audit BEFORE INSERT OR UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE ... ...

postgres=#

Expand|Select|Wrap|Line Numbers

\d tablename

is also not working properly

michaelb

A complete copy-and-paste example of how \d is not working properly would be helpful.

priyan

Quote:

Originally Posted by michaelb

A complete copy-and-paste example of how \d is not working properly would be helpful.

if i use like this

Expand|Select|Wrap|Line Numbers

postgres=# \d

the error is like this

Expand|Select|Wrap|Line Numbers

 
ERROR: syntax error at or near "postgres"

SQL state: 42601

Character: 1

if i use like this

Expand|Select|Wrap|Line Numbers

\d errorlog

Expand|Select|Wrap|Line Numbers

 
ERROR: syntax error at or near "\"

SQL state: 42601

Character: 1

please give the full details help me please

michaelb

Quote:

Originally Posted by priyan

if i use like this

Expand|Select|Wrap|Line Numbers

postgres=# \d

the error is like this

Expand|Select|Wrap|Line Numbers

 
ERROR: syntax error at or near "postgres"

SQL state: 42601

Character: 1

postgres=# is not a part of command, it is the psql prompt...
I still have a feeling that you were trying to pass the "\d" metacommand in your SQL query.

priyan

Quote:

Originally Posted by michaelb

postgres=# is not a part of command, it is the psql prompt...
I still have a feeling that you were trying to pass the "\d" metacommand in your SQL query.

plese explain in detail what i have to give as a command and where i have to give it. please help me...............

michaelb

psql is an interactive terminal to the PostgreSQL database.
When you type psql at the prompt you get a window, where you "talk" to the database. In addition to standard SQL queries (SELECT FROM ... , UPDATE ... , CREATE TABLE ..., etc, etc) you have many special commands you can use. They called meta-commands and they all begin with back-slash.
For example
\d - display all tables and views in my database
\d tab-name - describe table tab-name
\d+ tab-name - same as above, but more details.

To exit the psql session type \q
To avoid passing extra arguments when invoking psql you can set your envoronmental variables PGUSER, PGDATA and PGDATABASE.

Sometimes you don't need to stay in the psql session, you can call psql with one command, it'll tell you what you need and exit back to the command prompt.

For example, I want to see all databases on my PostgreSQL server:

Expand|Select|Wrap|Line Numbers

 
C:\Temp> psql -l

Password: ******

        List of databases

   Name    |  Owner   | Encoding

-----------+----------+----------

 Michael   | Michael  | UTF8

 postgres  | postgres | UTF8

 template0 | postgres | UTF8

 template1 | postgres | UTF8

(4 rows)
 
C:\Temp>

In a similar fashion you can call psql with a query. It will execute it and spit back the result:

Expand|Select|Wrap|Line Numbers

 
C:\Temp> psql -c "select count(1) from  pg_ts_cfg"

Password: ******

 count

-------

     4

(1 row)
 
C:\Temp>

You'll find more interesting things in the psql man page

syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

re: syntax to view the structure of a table

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about