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;
(524,1,Jared,Ely,,530,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(1,1,Mary,Smith,,5,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(2,1,Patricia,Johnson,,6,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(3,1,Linda,Williams,,7,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(4,2,Barbara,Jones,,8,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(5 rows)


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