Postgres : Convert entire row to text with type conversion

by bitznbitez

One of the neat ways you can use postgres type conversion is to display an entire row in a format suitable for using in a subsequent insert etc.   That is comma delimited, quoted as needed between two parenthesis.

First lets document the customer table in this sample dvdrental database.

CREATE TABLE customer (

customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
store_id smallint NOT NULL,
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
email character varying(50),
address_id smallint NOT NULL,
activebool boolean DEFAULT true NOT NULL,
create_date date DEFAULT ('now'::text)::date NOT NULL,
last_update timestamp without time zone DEFAULT now(),
active integer

);

Now lets produce the rows converting the whole row to text as follows….

dvdrental=# select t::text from customer t limit 5;
t
-------------------------------------------------------------------------------------------------------
(524,1,Jared,Ely,jared.ely@sakilacustomer.org,530,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(1,1,Mary,Smith,mary.smith@sakilacustomer.org,5,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(2,1,Patricia,Johnson,patricia.johnson@sakilacustomer.org,6,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(3,1,Linda,Williams,linda.williams@sakilacustomer.org,7,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(4,2,Barbara,Jones,barbara.jones@sakilacustomer.org,8,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(5 rows)

dvdrental=#

While I don’t have an immediate need for this conversion, its still a fascinating little postgres trick.

Advertisements