PostgreSQL integer fields getting auto-detected as 'Category' about metabase

[paoliniluis]

Version? DDL of the table?

[sasignell]

Hello, Thanks for the prompt reply. The PG version is PostgreSQL 10.23 (Ubuntu 10.23-0ubuntu0.18.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit Metabase version is v1.49.13 DDL of table (I believe) is

…

-- FOREIGN TABLE: reports.li_daily_tech_report

-- DROP FOREIGN TABLE IF EXISTS reports.li_daily_tech_report; CREATE FOREIGN TABLE IF NOT EXISTS reports.li_daily_tech_report( date date, tech text COLLATE pg_catalog."default", inspections_completed integer, appts_completed integer, meters_total integer, outside_meters integer, inside_meters integer, door_hangers integer, wait_time integer, dispatches integer, poe_seals integer ) SERVER li_daily_tech_report OPTIONS (program E'curl -d ''{ "q": "WITH a AS (SELECT technician_parent AS tech,COALESCE(is_meter_outside,$$no$$) AS is_meter_outside,CASE WHEN is_meter_count_correct=$$no$$ THEN updated_meter_count::integer ELSE meter_count::integer END AS meter_count_corrected,door_hanger_date,inspection_completed_date, appointment_date, CASE WHEN appointment_date=inspection_completed_date THEN 1 ELSE 0 END AS appt_completed FROM \\"LI Service Line Inspection 2021\\" WHERE inspection_completed_date=current_date-1) , b AS (SELECT tech, COUNT(*) AS inspections_completed, SUM(appt_completed) AS appts_completed, SUM(meter_count_corrected) AS meters_total, SUM(CASE WHEN is_meter_outside=$$yes$$ THEN meter_count_corrected ELSE 0 END) AS outside_meters, SUM(CASE WHEN is_meter_outside=$$no$$ THEN meter_count_corrected ELSE 0 END) AS inside_meters FROM a WHERE tech IS NOT NULL GROUP BY tech ), door AS ( SELECT technician_parent AS tech,COUNT(*) AS door_hangers FROM \\"LI Service Line Inspection 2021\\" WHERE door_hanger_date=current_date-1 GROUP BY technician_parent),gro AS (SELECT technician_parent AS tech, SUM(COALESCE(technician_wait_time,$$0$$)::integer) AS wait_time,SUM(CASE WHEN dispatched = $$yes$$ then 1 else 0 END) as dispatched from \\"LI Service Line Inspection 2021/gas_repair_order_info\\" a JOIN \\"LI Service Line Inspection 2021\\" b ON a._record_id=b._record_id WHERE gro_date=current_date-1 GROUP BY technician_parent),poe AS ( SELECT technician_svs AS tech,COUNT(*) as poe_seals from \\"LI Service Line Inspection 2021/poe_inspection_parent\\" WHERE svs_inspection_date::date=current_date-1 AND poe_sealing_required =$$yes$$ GROUP BY technician_svs) SELECT current_date-1,b.*,door.door_hangers,gro.wait_time,gro.dispatched,COALESCE(poe.poe_seals,0) FROM b LEFT JOIN door ON b.tech=door.tech LEFT JOIN gro ON b.tech=gro.tech LEFT JOIN poe ON b.tech=poe.tech", "f": "csv" }'' -H "Content-Type: application/json" -H "X-ApiToken: " -X POST https://api.fulcrumapp.com/api/v2/query', format 'csv', header 'true');

On Thu, Jun 6, 2024 at 7:03 PM Luis Paolini ***@***.***> wrote: Version? DDL of the table? — Reply to this email directly, view it on GitHub <#43776 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA77J542G6HLFHGTRIPGRE3ZGDTF5AVCNFSM6AAAAABI5IKFROVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJTGUZTGNRVGU> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- Steve Signell Frontier Spatial, L.L.C. Colchester, VT

[FilmonK]

Can reproduce with something as simple as:
Postgres 15.1
Metabase 1.49.14

CREATE TABLE li_daily_tech_report (
    id SERIAL,
    date date,
    tech text,
    inspections_completed integer,
    appts_completed integer,
    meters_total integer,
    outside_meters integer,
    inside_meters integer,
    door_hangers integer,
    wait_time integer,
    dispatches integer,
    poe_seals integer
)

INSERT INTO li_daily_tech_report (date, tech, inspections_completed, appts_completed, meters_total, outside_meters, inside_meters, door_hangers, wait_time, dispatches, poe_seals)
VALUES 
    (CURRENT_DATE - 1, 'Tech A', 5, 2, 20, 10, 10, 1, 30, 5, 0),
    (CURRENT_DATE - 2, 'Tech B', 3, 1, 15, 5, 10, 0, 45, 3, 1),
    (CURRENT_DATE - 3, 'Tech C', 7, 3, 25, 15, 10, 2, 60, 6, 2),
    (CURRENT_DATE - 4, 'Tech D', 2, 0, 10, 4, 6, 0, 20, 2, 0),
    (CURRENT_DATE - 5, 'Tech E', 8, 4, 30, 20, 10, 3, 90, 7, 3),
    (CURRENT_DATE - 6, 'Tech F', 4, 1, 18, 9, 9, 1, 40, 4, 1),
    (CURRENT_DATE - 7, 'Tech G', 6, 2, 22, 11, 11, 2, 70, 5, 2),
    (CURRENT_DATE - 8, 'Tech H', 1, 0, 8, 3, 5, 0, 10, 1, 0),
    (CURRENT_DATE - 9, 'Tech I', 9, 5, 35, 17, 18, 4, 100, 8, 4),
    (CURRENT_DATE - 10, 'Tech J', 5, 2, 20, 10, 10, 1, 30, 5, 0),
    (CURRENT_DATE - 11, 'Tech K', 3, 1, 15, 5, 10, 0, 45, 3, 1),
    (CURRENT_DATE - 12, 'Tech L', 7, 3, 25, 15, 10, 2, 60, 6, 2),
    (CURRENT_DATE - 13, 'Tech M', 2, 0, 10, 4, 6, 0, 20, 2, 0),
    (CURRENT_DATE - 14, 'Tech N', 8, 4, 30, 20, 10, 3, 90, 7, 3),
    (CURRENT_DATE - 15, 'Tech O', 4, 1, 18, 9, 9, 1, 40, 4, 1),
    (CURRENT_DATE - 16, 'Tech P', 6, 2, 22, 11, 11, 2, 70, 5, 2),
    (CURRENT_DATE - 17, 'Tech Q', 1, 0, 8, 3, 5, 0, 10, 1, 0),
    (CURRENT_DATE - 18, 'Tech R', 9, 5, 35, 17, 18, 4, 100, 8, 4),
    (CURRENT_DATE - 19, 'Tech S', 5, 2, 20, 10, 10, 1, 30, 5, 0),
    (CURRENT_DATE - 20, 'Tech T', 3, 1, 15, 5, 10, 0, 45, 3, 1);

You will see something similar to the following in the logs:

analyze.classify :: Based on classification, updating these values of Field 3887 ''inspections_completed'': {:has_field_values :auto-list, :semantic_type :type/Category}

[luizarakaki]

dupe of #33888