Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE "MyTableName"( id int, first varchar(32));
INSERT INTO "MyTableName" VALUES (1, 'foo');
1 rows affected

SELECT format('SELECT %s FROM "MyTableName"'
             , string_agg(CASE WHEN udt_name in ('varchar', 'text')
                               THEN format('left(%1$I, 65535) AS %1$I', column_name)
                               ELSE quote_ident(column_name) END
                        , ', ' ORDER BY ordinal_position))
FROM  information_schema.columns c
JOIN  parse_ident('"MyTableName"') t ON t[1] = c.table_name;
-- can't use full form with secrect schema name
-- JOIN  parse_ident('"public"."MyTableName"') t ON t[1] = c.table_schema
--                                              AND t[2] = c.table_name


format


SELECT id, left(first, 65535) AS first FROM "MyTableName"


-- what I would do:
SELECT format('SELECT %s FROM %s'
            , string_agg(CASE WHEN atttypid = ANY ('{text, bpchar, varchar}'::regtype[])
                              THEN concat('left(', col, ', 65535) AS ', col)
                              ELSE col END, ', ')
            , attrelid)
FROM  (
   SELECT attrelid::regclass, atttypid, quote_ident(attname) AS col
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = 'public."MyTableName"'::regclass  -- provide once, optionally schema-qualified
   AND    attnum > 0
   AND    NOT attisdropped
   ORDER  BY attnum
   ) sub
GROUP  BY attrelid;


format


SELECT id, left(first, 65535) AS first FROM "MyTableName"


SELECT id, left(first, 65535) AS first FROM "MyTableName";


id
first


1
foo