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

how to get column names of a table

P: 55
hi all,

How to get fieldnames of a table using postgresql?

Can someone help me to overcome this issue?

Like if i have table called "employee" i need to fetch the fieldnames(id,name,workstatus..etc) of employees alone not its records...Is any method to get this?


Thanks in advance...
Aug 14 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 534
If it does not have to be a query the simplest approach is to use the metacommand \d in psql session:
Expand|Select|Wrap|Line Numbers
  1. postgres=# \d bar
  2.                                 Table "public.bar"
  3.  Column |         Type          |                    Modifiers
  4. --------+-----------------------+--------------------------------------------------
  5.  id     | integer               | not null default nextval('bar_id_seq'::regclass)
  6.  f1     | character varying(32) |
  7.  f2     | character varying(32) |
  8. Indexes:
  9.     "bar_pkey" PRIMARY KEY, btree (id)
  10. Triggers:
  11.     trig_audit BEFORE INSERT OR UPDATE ON bar FOR EACH ROW EXECUTE PROCEDURE audit_proc()
  12.  
\d without a table name will give you a list of all user tables in the database.
See psql manual for details
Aug 14 '07 #2

P: 55
Thanks michael for your help..

I need a query to fetch the fieldnames is any method available for that?

If so guide me the solution or its related pages...

Regards..

Barani
Aug 14 '07 #3

Expert 100+
P: 534
I am pretty sure this question came up before, I remember I posted the sql query that gives this info few months ago.
Try to search the Postgresql forum, if you found the answer post a link to it - it may help others.
Meanwhile take a look at the Postgresql Information Schema
Aug 14 '07 #4

P: 55
Hi michael,

I have gone through your guidance and searched perl forum...

You have mentioned the query as "select count(*) from tablename"

Its not giving me the fieldnames of a table...

Kindly suggest me some other way...

Thanks&Regards,

Barani..
Aug 14 '07 #5

Expert 100+
P: 534
Hi michael,
I have gone through your guidance and searched perl forum...

You have mentioned the query as "select count(*) from tablename"
Its not giving me the fieldnames of a table...
No, no I said:
>> Try to search the Postgresql forum ...

"select count(*) from tablename" is not what you need, this query only returns the number of rows in the table.

I looked at the old postings, but could not find anything, which is strange, I remember replying to the same question few months ago.
I'll try to dig it out for you.
Aug 14 '07 #6

Expert 100+
P: 534
This works for me:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     a.attname as "Column",
  3.     pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
  4. FROM
  5.     pg_catalog.pg_attribute a
  6. WHERE
  7.     a.attnum > 0
  8.     AND NOT a.attisdropped
  9.     AND a.attrelid = (
  10.         SELECT c.oid
  11.         FROM pg_catalog.pg_class c
  12.             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  13.         WHERE c.relname ~ '^(foo)$'
  14.             AND pg_catalog.pg_table_is_visible(c.oid)
  15.     )
  16. ;
  17.  
Replace foo on line 13 with the name of your table.
The code should produce the output similar to this:
Expand|Select|Wrap|Line Numbers
  1.    Column  |       Datatype
  2. --------+-----------------------
  3.  id     | integer
  4.  fs1    | character varying(32)
  5.  fi2    | integer
  6.  fn3    | numeric(5,0)
  7. (4 rows)
  8.  
If you really need this functionality consider making a function that would take the table name as an argument and spit out the result.
Aug 15 '07 #7

P: 55
This works for me:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     a.attname as "Column",
  3.     pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
  4. FROM
  5.     pg_catalog.pg_attribute a
  6. WHERE
  7.     a.attnum > 0
  8.     AND NOT a.attisdropped
  9.     AND a.attrelid = (
  10.         SELECT c.oid
  11.         FROM pg_catalog.pg_class c
  12.             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  13.         WHERE c.relname ~ '^(foo)$'
  14.             AND pg_catalog.pg_table_is_visible(c.oid)
  15.     )
  16. ;
  17.  
Replace foo on line 13 with the name of your table.
The code should produce the output similar to this:
Expand|Select|Wrap|Line Numbers
  1.    Column  |       Datatype
  2. --------+-----------------------
  3.  id     | integer
  4.  fs1    | character varying(32)
  5.  fi2    | integer
  6.  fn3    | numeric(5,0)
  7. (4 rows)
  8.  
If you really need this functionality consider making a function that would take the table name as an argument and spit out the result.
Hi michael,

Thanks for your help and cooperation towards my query...

Its working for me now...i have searched in the postgresql forum but i have not found this type of query over there ...

Any way i like to thank you once again for your timely help...

Hope we will have furhermore discussions..

Regards,
Barani...
Aug 16 '07 #8

P: 55
Hi michael,

The query you have mentioned me is working and giving me the result what i needed...

But i need to understand its structure, in the previous reply u have mentioned about information schema...

Is that is related with the query you have posted...


Kindly post me the details...

With regards,

Barani...
Aug 16 '07 #9

P: n/a
I did not find any such query. Your query works for me.
Oct 19 '10 #10

Post your reply

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