Thread: [GENERAL] column names query
Hi, is there a simple way to retrieve column names from a query - basically the way psql adds column headings when I do a select? Best regards Wolfgang Hamann
On Thu, Sep 7, 2017 at 9:18 AM, <hamann.w@t-online.de> wrote: > is there a simple way to retrieve column names from a query - basically the way psql adds > column headings when I do a select? How do you do the query? I mean, JDBC, PERL? After all psql is just a C program doing a query using libpq and can do it, we may provide some useful info if you show yours first. Francisco Olarte.
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 'your_name';
----- Original Message -----
From: hamann w <hamann.w@t-online.de>
To: pgsql-general@postgresql.org
Sent: Thu, 07 Sep 2017 07:18:12 -0000 (UTC)
Subject: [GENERAL] column names query
From: hamann w <hamann.w@t-online.de>
To: pgsql-general@postgresql.org
Sent: Thu, 07 Sep 2017 07:18:12 -0000 (UTC)
Subject: [GENERAL] column names query
Hi,
is there a simple way to retrieve column names from a query - basically the way psql adds
column headings when I do a select?
column headings when I do a select?
Best regards
Wolfgang Hamann
Wolfgang Hamann
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
>> On Thu, Sep 7, 2017 at 9:18 AM, <hamann.w@t-online.de> wrote: >> > is there a simple way to retrieve column names from a query - basically the way psql adds >> > column headings when I do a select? >> >> How do you do the query? I mean, JDBC, PERL? After all psql is just a >> C program doing a query using libpq and can do it, we may provide some >> useful info if you show yours first. >> >> Francisco Olarte. Hi, I imagine this situation: I start working on a query... when I am happy with the result, I see a table (with headers) in psql. Now I can do \copy (select .... my query here) to /tmp/testfile1 I would like to do something like \copy (select heading(select .... my query here)) to /tmp/heading_testfile1 Best regards Wolfgang Hamann
>> SELECT table_name, column_name >> FROM information_schema.columns >> WHERE table_name = 'your_name'; >> >> ----- Original Message -----From: hamann w <hamann.w@t-online.de>To: pgsql-general@postgresql.orgSent: Thu, 07 Sep 201707:18:12 -0000 (UTC)Subject: [GENERAL] column names query >> >> >> >> Hi, >> >> is there a simple way to retrieve column names from a query - basically the way psql addscolumn headings when I do a select? >> >> Best regardsWolfgang Hamann >> >> >> >> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general >> Hi, I am already using something like this (but using pg_attribute table) to retrieve current table layout for static tables. At the moment I am looking for a solution for ad hoc queries Example query select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3 from a left join b on .... Expected response col1 mycol3 Obviously, I could save the result into a temp table and retrieve the table's heading. Best regards Wolfgang Hamann
On Thu, Sep 7, 2017 at 3:28 PM <hamann.w@t-online.de> wrote:
Example query
select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3 from a left join b on ....
Expected response
col1 mycol3
This may be overkill, but works:
postgres=# \copy (select 1 as foo, 2 as bar limit 0) to stdout with (format csv, header, delimiter ' ')
foo bar
Hi Hamman: On Thu, Sep 7, 2017 at 3:17 PM, <hamann.w@t-online.de> wrote: > I would like to do something like > \copy (select heading(select .... my query here)) to /tmp/heading_testfile1 It's already been pointed out, you can do something using CSV copy with headers ( Why headers are not allowed in other formats remains a mistery to me), and LIMIT 0 ( I would try adding AND FALSE to the where clause better, it may lead to faster response, although I doubt it) . Francisco Olarte.
On 9/7/2017 12:18 AM, hamann.w@t-online.de wrote: > is there a simple way to retrieve column names from a query - basically the way psql adds > column headings when I do a select? if you're using libpq to do your queries, PQfname(*result, column_number) returns the name of that column number. there are equivalent functions in most other APIs. -- john r pierce, recycling bits in santa cruz
>> On Thu, Sep 7, 2017 at 3:28 PM <hamann.w@t-online.de> wrote: >> >> > Example query >> > select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3 >> > from a left join b on .... >> > Expected response >> > col1 mycol3 >> > >> >> This may be overkill, but works: >> >> postgres=# \copy (select 1 as foo, 2 as bar limit 0) to stdout with (format >> csv, header, delimiter ' ') >> foo bar >> Hi Manuel, this works perfectly, many thanks Wolfgang -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general