how to get column names of a table

[Lastknight]

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,n ame,workstatus. .etc) of employees alone not its records...Is any method to get this?


Thanks in advance...

[michaelb]

If it does not have to be a query the simplest approach is to use the metacommand \d in psql session:
[CODE=sql]
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 audit_proc()
[/CODE]

\d without a table name will give you a list of all user tables in the database.
See psql manual for details

[Lastknight]

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

[michaelb]

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

[Lastknight]

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

[michaelb]

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.

[michaelb]

This works for me:

[CODE=sql]
SELECT
a.attname as "Column",
pg_catalog.form at_type(a.attty pid, a.atttypmod) as "Datatype"
FROM
pg_catalog.pg_a ttribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_c lass c
LEFT JOIN pg_catalog.pg_n amespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(foo)$'
AND pg_catalog.pg_t able_is_visible (c.oid)
)
;
[/CODE]
Replace foo on line 13 with the name of your table.
The code should produce the output similar to this:

   Column  |       Datatype
--------+-----------------------
 id     | integer
 fs1    | character varying(32)
 fi2    | integer
 fn3    | numeric(5,0)
(4 rows)

If you really need this functionality consider making a function that would take the table name as an argument and spit out the result.

[Lastknight]

This works for me:

[CODE=sql]
SELECT
a.attname as "Column",
pg_catalog.form at_type(a.attty pid, a.atttypmod) as "Datatype"
FROM
pg_catalog.pg_a ttribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_c lass c
LEFT JOIN pg_catalog.pg_n amespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(foo)$'
AND pg_catalog.pg_t able_is_visible (c.oid)
)
;
[/CODE]
Replace foo on line 13 with the name of your table.
The code should produce the output similar to this:

   Column  |       Datatype
--------+-----------------------
 id     | integer
 fs1    | character varying(32)
 fi2    | integer
 fn3    | numeric(5,0)
(4 rows)

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

[Lastknight]

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

[Mahendra Varman]

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