By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

syntax to view the structure of a table

priyan
P: 54
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....
Aug 4 '07 #1
Share this Question
Share on Google+
13 Replies


P: 17
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
Aug 4 '07 #2

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

Excerpt from the 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
Aug 4 '07 #3

P: 10
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.........
Aug 6 '07 #4

priyan
P: 54
\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.
Aug 6 '07 #5

Expert 100+
P: 534
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.
Aug 6 '07 #6

priyan
P: 54
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
  1. /d errorlog
  2.  
also the same error is coming
Expand|Select|Wrap|Line Numbers
  1. ERROR: syntax error at or near "/"
  2. SQL state: 42601
  3. Character: 1
  4.  
Aug 6 '07 #7

Expert 100+
P: 534
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
  1. postgres=# \d
  2.                List of relations
  3.  Schema |      Name       |   Type   |  Owner
  4. --------+-----------------+----------+----------
  5.  public | bar             | table    | Michael
  6.  public | bar_id_seq      | sequence | Michael
  7.  public | businesstab     | table    | postgres
  8.  public | components      | table    | Michael
  9.  public | foo             | table    | Michael
  10.  
  11.  
  12. postgres=# \d bar
  13.                                 Table "public.bar"
  14.  Column |         Type          |                    Modifiers
  15. --------+-----------------------+--------------------------------------------------
  16.  id     | integer               | not null default nextval('bar_id_seq'::regclass)
  17.  f1     | character varying(32) |
  18.  f2     | character varying(32) |
  19. Indexes:
  20.     "bar_pkey" PRIMARY KEY, btree (id)
  21. Triggers:
  22.     trig_audit BEFORE INSERT OR UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE ... ...
  23. postgres=#
Aug 6 '07 #8

priyan
P: 54
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
  1. postgres=# \d
  2.                List of relations
  3.  Schema |      Name       |   Type   |  Owner
  4. --------+-----------------+----------+----------
  5.  public | bar             | table    | Michael
  6.  public | bar_id_seq      | sequence | Michael
  7.  public | businesstab     | table    | postgres
  8.  public | components      | table    | Michael
  9.  public | foo             | table    | Michael
  10.  
  11.  
  12. postgres=# \d bar
  13.                                 Table "public.bar"
  14.  Column |         Type          |                    Modifiers
  15. --------+-----------------------+--------------------------------------------------
  16.  id     | integer               | not null default nextval('bar_id_seq'::regclass)
  17.  f1     | character varying(32) |
  18.  f2     | character varying(32) |
  19. Indexes:
  20.     "bar_pkey" PRIMARY KEY, btree (id)
  21. Triggers:
  22.     trig_audit BEFORE INSERT OR UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE ... ...
  23. postgres=#
Expand|Select|Wrap|Line Numbers
  1. \d tablename 
  2.  
is also not working properly
Aug 7 '07 #9

Expert 100+
P: 534
A complete copy-and-paste example of how \d is not working properly would be helpful.
Aug 7 '07 #10

priyan
P: 54
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
  1. postgres=# \d
  2.  
the error is like this
Expand|Select|Wrap|Line Numbers
  1. ERROR: syntax error at or near "postgres"
  2. SQL state: 42601
  3. Character: 1
  4.  
if i use like this
Expand|Select|Wrap|Line Numbers
  1. \d errorlog
  2.  
Expand|Select|Wrap|Line Numbers
  1. ERROR: syntax error at or near "\"
  2. SQL state: 42601
  3. Character: 1
  4.  
please give the full details help me please
Aug 7 '07 #11

Expert 100+
P: 534
if i use like this
Expand|Select|Wrap|Line Numbers
  1. postgres=# \d
  2.  
the error is like this
Expand|Select|Wrap|Line Numbers
  1. ERROR: syntax error at or near "postgres"
  2. SQL state: 42601
  3. Character: 1
  4.  
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.
Aug 20 '07 #12

priyan
P: 54
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...............
Aug 21 '07 #13

Expert 100+
P: 534
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
  1. C:\Temp> psql -l
  2. Password: ******
  3.         List of databases
  4.    Name    |  Owner   | Encoding
  5. -----------+----------+----------
  6.  Michael   | Michael  | UTF8
  7.  postgres  | postgres | UTF8
  8.  template0 | postgres | UTF8
  9.  template1 | postgres | UTF8
  10. (4 rows)
  11.  
  12. C:\Temp>
  13.  
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
  1. C:\Temp> psql -c "select count(1) from  pg_ts_cfg"
  2. Password: ******
  3.  count
  4. -------
  5.      4
  6. (1 row)
  7.  
  8. C:\Temp>
  9.  
You'll find more interesting things in the psql man page
Aug 21 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.