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,name,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:

Expand|Select|Wrap|Line Numbers

 
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()

\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

Quote:

Originally Posted by 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...

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:

Expand|Select|Wrap|Line Numbers

 
SELECT

    a.attname as "Column",

    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"

FROM

    pg_catalog.pg_attribute a

WHERE

    a.attnum > 0

    AND NOT a.attisdropped

    AND a.attrelid = (

        SELECT c.oid

        FROM pg_catalog.pg_class c

            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

        WHERE c.relname ~ '^(foo)$'

            AND pg_catalog.pg_table_is_visible(c.oid)

    )

;

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

 
   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

Quote:

Originally Posted by michaelb

This works for me:

Expand|Select|Wrap|Line Numbers

 
SELECT

    a.attname as "Column",

    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"

FROM

    pg_catalog.pg_attribute a

WHERE

    a.attnum > 0

    AND NOT a.attisdropped

    AND a.attrelid = (

        SELECT c.oid

        FROM pg_catalog.pg_class c

            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

        WHERE c.relname ~ '^(foo)$'

            AND pg_catalog.pg_table_is_visible(c.oid)

    )

;

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

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

how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

re: how to get column names of a table

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about