Creating a custom PostgreSQL type that is a set of other types?

Question

The kind of information I want to store in this type is, for example: "330.30 eur/ton" or "25.15 usd/kg".

So, basically:

Price as decimal.
Currency as a FK to another table.
Weight unit as a FK to another table.

When making queries I would like to access the members directly e.g. "selling_price.price" or "selling_price.currency".

And I would like to be able to sort by this field as well... Which creates a whole host of problems, I realize, as we need to convert currencies and weight units.

One solution would be to add a fourth element, which stores the same information in a standardized format (e.g. usd/kg as a float), sort by that, and then update this whenever we updated currency exchange rates.

So, basically, this custom type (let's call it PRICE_PER_UNIT) would store:

Price as decimal.
Currency as FK.
Weight unit as a FK.
Value in standardized format.

And sorting on this field would have to be done by the standardized field, as a float.

Since I have never done anything like it in SQL before, I have no idea how to even get started with any of this... Anyone?

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-07-28 16:35:50Z

0

PostgreSQL supports CREATE TYPE for creating composite types. I think that's the best place for you to start.

answered Jul 28, 2011 at 16:35

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

unpythonic · Accepted Answer · 2011-07-28 17:17:19Z

0

Unless you're using the beta 9.1 version, avoid PostgreSQL types like the plague. They're a huge pain to update, and it's not until 9.1 that you can add or remove values from enums or alter attributes. For other versions, you're limited to renaming, changing the owner, and changing the schema.

You may want to be wary of storing the "standardized" price for sorting. Here I'm assuming you're talking about storing everything as (say) US dollars per kilogram. Anytime the currency exchange rates change, you're going to have to update every row in your table. Instead, you may want to store the conversion factor for the currency into USD, and store the conversion factor for the weight into kg, then sort based on an calculated value (conversion factors from the currency and weight type tables are joined to your data table).

answered Jul 28, 2011 at 17:17

unpythonic

4,08822 silver badges20 bronze badges

1 Comment

Elver Loho Over a year ago

Since I'd like to use the Django ORM on top of it all, would it be possible to join the conversion tables without the ORM having to know about it? And any code examples would be useful :)

Collectives™ on Stack Overflow

Creating a custom PostgreSQL type that is a set of other types?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related