364,111 Members | 2029 Browsing Online
Community for Developers & IT Professionals
Bytes IT Community
Post New Question »
Latest Articles Questions FAQ
home > postgresql database > postgresql database questions

how to get column names of a table

Lastknight
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
michaelb
Expert 100+
P: 499
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
Lastknight
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
michaelb
Expert 100+
P: 499
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
Lastknight
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
michaelb
Expert 100+
P: 499
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
michaelb
Expert 100+
P: 499
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
Lastknight
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
Lastknight
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
Mahendra Varman
P: n/a
Mahendra Varman
I did not find any such query. Your query works for me.
Oct 19 '10 #10

Post your reply

Help answer this question



Didn't find the answer to your PostgreSQL Database question?

You can also browse similar questions: PostgreSQL Database

Question stats

  • viewed: 28972
  • replies: 9
  • date asked: Aug 14 '07

Most Active

  1. rski427
  2. michaelb374
  3. priyan32
  4. r035198x25
  5. amitpatel6624

Most Questions Answered

  1. rski9
  2. r035198x1
  3. indikamaligaspe1
  4. nampet1
  5. scottiebo1

Top Questions

  1. Installing PostgreSQL 8.1 on VISTA39536
  2. Fatal error: Call to undefined function pg_connect()37612
  3. how to get column names of a table28401
  4. Server doesn't listen22420
  5. Combining two fields data into one field in the same table21391

Latest Questions

  1. invalid byte sequence for encoding "UTF8"155
  2. Questions of information_schema189
  3. Delete cascade with FK136
  4. how to make sql server 2005 to support other languages124
  5. When I try to connect to a database, I get the following error : psql teleflowdb8153

Similar topics

More

BYTES.COM © 2012
Formerly "TheScripts.com" from 2005-2008
About Bytes | Advertise on Bytes
Sitemap | PostgreSQL Database Answers Sitemap | PostgreSQL Database Insights Sitemap

Follow us to get the Latest Bytes Updates