SELECT ALL column_names in postgresql

Question

I'm using PostgreSQL and I want to create a query that will display all column_names in a specific table.

Schema: codes

Table Name: watch_list

Here are the column_names in my table:

watch_list_id, watch_name, watch_description

I tried what I found in the web:

SELECT *
FROM information_schema.columns
WHERE table_schema = 'codes'
  AND table_name   = 'watch_list'

It output is not what I wanted. It should be:

watch_list_id, watch_name, watch_description

How to do this?

table_catalog, table_schema, table_name, column_name, ordinal_position. . . — ZSMJ
– ZSMJ, Commented Jun 1, 2015 at 6:32
in the result the column_name have the name of the columns in the table.. — Nandakumar V
– Nandakumar V, Commented Jun 1, 2015 at 6:34

score 3 · Accepted Answer · 2015-06-01 06:52:47Z

3

If you want all column names in a single row, you need to aggregate those names:

SELECT table_name, string_agg(column_name, ', ' order by ordinal_position) as columns
FROM information_schema.columns
WHERE table_schema = 'codes'
  AND table_name   = 'watch_list'
GROUP BY table_name;

If you remove the condition on the table name, you get this for all tables in that schema.

edited Jun 1, 2015 at 6:52

answered Jun 1, 2015 at 6:46

user330315

Sign up to request clarification or add additional context in comments.

Comments

Suraj · Accepted Answer · 2015-06-01 06:35:57Z

-1

SELECT table_name FROM information_schema.tables WHERE table_schema='public'

answered Jun 1, 2015 at 6:35

Suraj

3733 silver badges17 bronze badges

3 Comments

user330315 Over a year ago

That will display all tables in a schema. The OP wants all columns for one table.

Suraj Over a year ago

This will help you: SELECT column_name FROM information_schema.columns WHERE table_name = 'watch_list'

user330315 Over a year ago

Which is essentially the same query as already shown in the question and it will not show the columns in a single row, but will show one row in the result for each column

Collectives™ on Stack Overflow

SELECT ALL column_names in postgresql

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related