Thread: row => text => row
Hello,
It is possible to cast a row to text, but is there a way to revert that?
e.g.:
create temp table test like pg_class;
WHITH dummy as (SELECT (c.*)::text t from pg_class c limit 10)
INSERT INTO test
SELECT ???
FROM dummy;
an other option that would sometimes help me would be to cast rows as text,but in their (default) csv representation:
select to_csv((c.*)) from foo c;
regards,
Marc Mamin
Marc Mamin <M.Mamin@intershop.de> writes: > It is possible to cast a row to text, but is there a way to revert that? Well, you can surely cast it back to the rowtype, but I think that answer doesn't really help you. What you seem to need is not casting to a rowtype, but "bursting" the rowtype variable into individual columns. > create temp table test like pg_class; > WHITH dummy as (SELECT (c.*)::text t from pg_class c limit 10) > INSERT INTO test > SELECT ??? > FROM dummy; The trick here is to use the rowtype result as a single variable, and burst it later: regression=# create temp table test (like pg_class); CREATE TABLE regression=# with dummy as (select c from pg_class c limit 10) regression-# insert into test select (c).* from dummy; INSERT 0 10 BTW, I assume there's a reason for not simply doing insert into test select * from pg_class c limit 10; or even with dummy as (select * from pg_class c limit 10) insert into test select * from dummy; regards, tom lane
On 11/11/2016 12:55 AM, Marc Mamin wrote: > Hello, > > It is possible to cast a row to text, but is there a way to revert that? > > e.g.: > > create temp table test like pg_class; > > WHITH dummy as (SELECT (c.*)::text t from pg_class c limit 10) > INSERT INTO test > SELECT ??? > FROM dummy; > > > an other option that would sometimes help me would be to cast rows as > text,but in their (default) csv representation: > > select to_csv((c.*)) from foo c; In psql(https://www.postgresql.org/docs/9.5/static/app-psql.html): Long way \pset fieldsep ',' \pset format unaligned \pset footer \o test.csv SELECT (c.*) t from pg_class c limit 10; \o Shorter way: \copy { table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( option [, ...] ) ] Outside psql: https://www.postgresql.org/docs/9.5/static/sql-copy.html > > regards, > > Marc Mamin > -- Adrian Klaver adrian.klaver@aklaver.com