I am new to PostgreSQL, but so far have only come across tutorials on how to export entire tables with headers to .csv. I would like to only export field names.
How would one write a query to only export the column names of table x into a .csv ?
Add a comment
|
3 Answers
COPY (select * from table_name where false) -- To return result without rows
TO 'D:\File.csv' DELIMITER ',' CSV HEADER;
or
create temp table tmp (like table_name); -- To copy structure into a new temp table
COPY tmp TO 'D:/File.csv' DELIMITER ',' CSV HEADER;
drop table tmp;
Comments
You should know that from the psql console you can ask some useful information about table structure with \dcommand.
Of course, I understood that what you need is to retrieve that information with a SQL query executed from your application.
But, if you call psql with -E (or --echo-hidden), then, after typing \d some table (or whatever other console command) it will also output the query (or queries) used to obtain that information. Which is very useful in cases like that.
For example:
test=> \d foo
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
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)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relp
ersistence, c.relreplident
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '41967';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
NULL AS indexdef,
NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '41967' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
[...more...]
Now, combining the third query with the first one is easy to build just what you want:
SELECT a.attname
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
WHERE c.relname ~ '^(foo)$'
)
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
In fact, you can also change
WHERE c.relname ~ '^(foo)$'withWHERE c.relname = 'foo'too. Except if you want to be able to match table names by regular expression. Which I think is not your case.
1 Comment
iskandarblue
Thanks for that info!
another method, using psql from the command line, so that superuser privileges are not required
psql -U user -d database -c "COPY (SELECT * FROM table WHERE FALSE) To STDOUT With CSV HEADER DELIMITER ',';" > tmp/foo.csv
This returns column names only, in a comma-separated list:
$ more tmp/foo.csv
id,name,date,status...
Comments
