Thread

1. PG Unpivot ?

   dfgpostgres <dfgpostgres3@gmail.com> — 2025-11-03T17:18:55Z

   psql (13.2, server 15.3) on linux
   
   I think they call this "unpivot" in MSSQL ?
   
   How can I get an sql query to return  one line per column with... an ID,
   column name and value.  the ctid for the id field is fine.
   
   Example:
   dvdb=# create table unpivot (intcol integer, floatcol float, strcol
   varchar);
   CREATE TABLE
   dvdb=# insert into unpivot (intcol,floatcol,strcol) values
   (1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three');
   INSERT 0 3
   dvdb=# select * from unpivot;
    intcol | floatcol | strcol
   --------+----------+--------
         1 |      1.1 | one
         2 |      2.2 | two
         3 |      3.3 | three
   (3 rows)
   
   I want 9 records returned, each row with 3 cols, 1st col is the ctid,
   second is the column name, third is the val.
   
   Thanks in Advance !
   

2. Re: PG Unpivot ?

   hubert depesz lubaczewski <depesz@depesz.com> — 2025-11-03T17:22:11Z

   On Mon, Nov 03, 2025 at 12:18:55PM -0500, dfgpostgres wrote:
   > psql (13.2, server 15.3) on linux
   > 
   > I think they call this "unpivot" in MSSQL ?
   > 
   > How can I get an sql query to return  one line per column with... an ID,
   > column name and value.  the ctid for the id field is fine.
   > 
   > Example:
   > dvdb=# create table unpivot (intcol integer, floatcol float, strcol
   > varchar);
   > CREATE TABLE
   > dvdb=# insert into unpivot (intcol,floatcol,strcol) values
   > (1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three');
   > INSERT 0 3
   > dvdb=# select * from unpivot;
   >  intcol | floatcol | strcol
   > --------+----------+--------
   >       1 |      1.1 | one
   >       2 |      2.2 | two
   >       3 |      3.3 | three
   > (3 rows)
   > I want 9 records returned, each row with 3 cols, 1st col is the ctid,
   > second is the column name, third is the val.
   
   I think it should work:
   
   select u.ctid, e.* from unpivot u, to_jsonb(u) j, jsonb_each_text(j) e;
   
   Best regards,
   
   depesz
   
   
   
   
   

3. Re: PG Unpivot ?

   dfgpostgres <dfgpostgres3@gmail.com> — 2025-11-03T17:32:09Z

   Wow !
   That did it, even with the predicate I stuck on the end when I tried it.
   
   Thanks Depesz !
   
   On Mon, Nov 3, 2025 at 12:22 PM hubert depesz lubaczewski <depesz@depesz.com>
   wrote:
   
   > On Mon, Nov 03, 2025 at 12:18:55PM -0500, dfgpostgres wrote:
   > > psql (13.2, server 15.3) on linux
   > >
   > > I think they call this "unpivot" in MSSQL ?
   > >
   > > How can I get an sql query to return  one line per column with... an ID,
   > > column name and value.  the ctid for the id field is fine.
   > >
   > > Example:
   > > dvdb=# create table unpivot (intcol integer, floatcol float, strcol
   > > varchar);
   > > CREATE TABLE
   > > dvdb=# insert into unpivot (intcol,floatcol,strcol) values
   > > (1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three');
   > > INSERT 0 3
   > > dvdb=# select * from unpivot;
   > >  intcol | floatcol | strcol
   > > --------+----------+--------
   > >       1 |      1.1 | one
   > >       2 |      2.2 | two
   > >       3 |      3.3 | three
   > > (3 rows)
   > > I want 9 records returned, each row with 3 cols, 1st col is the ctid,
   > > second is the column name, third is the val.
   >
   > I think it should work:
   >
   > select u.ctid, e.* from unpivot u, to_jsonb(u) j, jsonb_each_text(j) e;
   >
   > Best regards,
   >
   > depesz
   >
   >
   

4. Re: PG Unpivot ?

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-03T17:33:23Z

   dfgpostgres <dfgpostgres3@gmail.com> writes:
   > I want 9 records returned, each row with 3 cols, 1st col is the ctid,
   > second is the column name, third is the val.
   
   Perhaps psql's "expanded" mode is close enough?
   
   regression=# \x
   Expanded display is on.
   regression=# select * from unpivot;
   -[ RECORD 1 ]---
   intcol   | 1
   floatcol | 1.1
   strcol   | one
   -[ RECORD 2 ]---
   intcol   | 2
   floatcol | 2.2
   strcol   | two
   -[ RECORD 3 ]---
   intcol   | 3
   floatcol | 3.3
   strcol   | three
   
   PS: I do not advise relying on ctid as a row identifier.
   Use a proper primary key.
   
   			regards, tom lane