Column names for view being capitalized, breaking questions

Get Help
1

I've created some views in postgresql and some of the column names are showing up as capitalized in Metabase - when I create a question I get the following error:

org.postgresql.util.PSQLException: ERROR: column rfq_views.Dimensions does not exist Hint: Perhaps you meant to reference the column "rfq_views.dimensions". Position: 124

I can bypass the error by hiding the column but there are multiple columns with the same issue and I would love to know what might be causing it.

I have several views with the same columns and it is only this view that has the problem. Any suggestions for how to get Metabase to use the correct column name?

(View sql)

SELECT
rlip.id as line_item_id,
COALESCE(r.reference, 'RFQ-' || r.id + 10000) as rfq_number,
r.created_at::timestamp(0) as rfq_created_at,
r.priced_at::timestamp(0) as rfq_priced_at,
r.status,
s.name as supplier_name,
li.shape,
li.material,
li.grade,
li.finish,
li.dimensions_archive as dimensions,
li.length,
li.width,
li.height,
li.diameter,
li.weight,
li.thickness,
li.standard_dimensions,
li.quantity,
ROUND(rlip.unit_price_pennies::numeric(11, 2) / 100, 2) as unit_price,
ROUND(rlip.total_pennies::numeric(11, 2) / 100, 2) as total_price,
ROUND(r.total_gross_pennies::numeric(11, 2) / 100, 2) as rfq_total_gross_price,
li.unit_weight,
li.total_weight,
r.total_weight as rfq_total_weight,
ROUND((rlip.total_pennies::numeric(11, 2) / 100) / NULLIF((li.total_weight / 1000), 0), 2) as price_per_tonne,
COALESCE(e.reference, 'EST-' || e.id + 10000) as enquiry_number,
e.enquiry_channel,
e.pricing_test,
b.name as buyer_name,
da.post_code as delivery_postcode,
('QO-' || lpad(q.id::text, 5, '0')) as quote_number,
(qlip.rfq_line_item_price_id = rlip.id) as chosen_for_quote,
(q.status = 'accepted') as accepted_by_buyer,
q.accepted_at::timestamp(0) as quote_accepted_at,
ROUND(qlip.total_pennies::numeric(20, 2) / 100, 2) as quoted_item_total,
r.regret_reasons
FROM
rfqs r
JOIN suppliers s ON s.id = r.supplier_id
JOIN enquiries e ON e.id = r.enquiry_id
JOIN buyers b ON b.id = e.buyer_id
JOIN addresses da ON da.id = e.delivery_address_id
JOIN line_item_prices rlip ON rlip.priced_type = 'Rfq'
AND rlip.priced_id = r.id
JOIN line_items li ON li.id = rlip.enquiry_line_item_id
LEFT JOIN line_item_prices qlip ON qlip.priced_type = 'Quote'
AND qlip.enquiry_line_item_id = li.id
LEFT JOIN quotes q ON q.id = e.quote_id

2

I think itā€™s Postgress being weird:

3

Yes, that is what is causing the error. But I created the view with lowercase column names - Metabase seems to be capitalizing the column name and I canā€™t understand why.

4

Throwing a few ideas in the hope you can use some of it:

So the database was always with lowercase so Metabase never got a chance to cache some uppercase names?

You can look into the Metabase application data - and possibly patch in correct lowercase data and see if it fixes it. Alternatively try spinning up a fresh Metabase testing instance and point it to your Postgres db - see how it behaves. Iā€™m sure if you got an easy to reproduce bug report from scratch with steps, version info etc. the Metabase core devs will look into it fairly quickly as Postgres is pretty common.

Iā€™m presuming youā€™re on latest official Metabase version. Iā€™m currently somewhat behind in versions myself - but can confirm it working with correct lowercase on v 0.27-ish with some of my Postgres production data - though Iā€™m not using a lot of views.

5

The views have changed and there was a version that had ā€œDimensionsā€ as the column name, so cache may be the problem. Iā€™ve done what looks obvious (Discard saved field values, Re-scan field values) and if I ā€˜show original schemaā€™ I can see the incorrect column names. There does not seem to be a way to change the values.

Iā€™m happy to help reproduce if a core dev would like - it looks like the steps are to create a view with column names using capitals and then replace that view with one using lower case column names. I would think Metabase should use the new column names, but perhaps it doesnā€™t see the difference?

I am on the most current version (0.29.3).

6

I see. Yes, then stale cached values might be what youā€™re experiencing.

If memory serves me right, I think Iā€™ve experienced rescan under a particular table didnā€™t always fix some things. But resorting to doing a full rescan for the whole database under the database setup did. Itā€™s an area that as far as I know recently has seen some changes so YMMV ...

edit: additional comment ...

Yes, entirely possible because of differences in databases case sensitivety and Metabase has to work across them that it might be too forgiving regarding case in some places and the other way around.

edit 2: Ah, looks like you already opened an issue:

7

That wasnā€™t me, but yes, same issue. My difference is using views instead of tables.

1 Like