Postgres 15

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 aws_s3_bucket(example text);
insert into aws_s3_bucket values 
  ('1'),
  ('2'),
  ('not a number'),--this will cause an error if I try casting to numeric
  ('3'),
  ('4');

CREATE TABLE

INSERT 0 5

select example::numeric from aws_s3_bucket;

ERROR:  invalid input syntax for type numeric: "not a number"

--the first one works fine
select example::numeric from aws_s3_bucket limit 1 offset 0;

example

1

SELECT 1

--the second one works fine
select example::numeric from aws_s3_bucket limit 1 offset 1;

example

2

SELECT 1

--the third one throws an exception, as expected
select example::numeric from aws_s3_bucket limit 1 offset 2;

ERROR:  invalid input syntax for type numeric: "not a number"

--the fourth one look like it would work but it doesn't, third one still breaks things
select example::numeric from aws_s3_bucket limit 1 offset 3;

ERROR:  invalid input syntax for type numeric: "not a number"

--the fifth one look like it would work but it doesn't, third one still breaks things
select example::numeric from aws_s3_bucket limit 1 offset 4;

ERROR:  invalid input syntax for type numeric: "not a number"

CREATE OR REPLACE FUNCTION skip_exceptions() RETURNS setof numeric AS $BODY$
DECLARE current_value  text;
BEGIN
  FOR current_value IN SELECT example
                       FROM aws_s3_bucket LOOP
     BEGIN RETURN NEXT current_value::numeric;
     EXCEPTION WHEN OTHERS THEN NULL;--do nothing, keep looping
     END;
  END LOOP;
END $BODY$ LANGUAGE plpgsql;

SELECT skip_exceptions();

CREATE FUNCTION

skip_exceptions

1

2

3

4

SELECT 4