how to get column names of a table

If it does not have to be a query the simplest approach is to use the metacommand \d in psql session:
\d without a table name will give you a list of all user tables in the database.
See psql manual for details

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

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

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..

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.

This works for me:

Replace foo on line 13 with the name of your table.
The code should produce the output similar to this:
If you really need this functionality consider making a function that would take the table name as an argument and spit out the result.

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...

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...

I did not find any such query. Your query works for me.