在Postgres中同一列中存储不同数据类型的合理方法？ [英] Sane way to store different data types within same column in postgres?
问题描述
我正在尝试修改与Postgres数据库交互的现有API。长篇小说，它本质上是存储描述符/元数据，以确定实际的资产（通常是某种文件）存储在服务器硬盘上的位置。
I'm currently attempting to modify an existing API that interacts with a postgres database. Long story short, it's essentially stores descriptors/metadata to determine where an actual 'asset' (typically this is a file of some sort) is storing on the server's hard disk.
目前，可以使用任何数量的未定义的键值对（即，uploadBy，addedOn，assetType等）将这些资产标记为标记这些标签存储在单独的表中结构类似于以下内容：
Currently, its possible to 'tag' these 'assets' with any number of undefined key-value pairs (i.e. uploadedBy, addedOn, assetType, etc.) These tags are stored in a separate table with a structure similar to the following:
+---------------+----------------+-------------+
|assetid (text) | tagid(integer) | value(text) |
|---------------+----------------+-------------|
|someStringValue| 1234 | someValue |
|---------------+----------------+-------------|
|aDiffStringKey | 1235 | a username |
|---------------+----------------+-------------|
|aDiffStrKey | 1236 | Nov 5, 1605 |
+---------------+----------------+-------------+
assetid和tagid是来自其他表的外键。想想一下代表一个文件的assetid，tagid / value对是描述符的映射。
assetid and tagid are foreign keys from other tables. Think of the assetid representing a file and the tagid/value pair is a map of descriptors.
现在，API（在Java中）创建了所有这些key-值对作为Map对象。这包括时间戳/日期等。我们想要做的是以某种方式能够存储键值对中的值的不同类型的数据。或者至少在数据库中存储不同，所以如果我们需要，我们可以在这些标签上运行查询日期范围等的查询。但是，如果它们作为文本项目存储在数据库中，那么我们必须一个。）知道这实际上是一个日期/时间/时间戳项目，b。）转换成我们实际可以运行的东西查询。
Right now, the API (which is in Java) creates all these key-value pairs as a Map object. This includes things like timestamps/dates. What we'd like to do is to somehow be able to store different types of data for the value in the key-value pair. Or at least, storing it differently within the database, so that if we needed to, we could run queries checking date-ranges and the like on these tags. However, if they're stored as text items in the db, then we'd have to a.) Know that this is actually a date/time/timestamp item, and b.) convert into something that we could actually run such a query on.
到目前为止，我只能想到一个想法，没有完全改变更改db的布局太多。
There is only 1 idea I could think of thus far, without complete changing changing the layout of the db too much.
它是扩展assettag表（如上所示），为各种类型（数字，文本，时间戳）添加其他列，允许它们为空，然后再打开插入，检查相应的键来确定它真正的数据类型。但是，我可以看到这种实现的很多问题。
It is to expand the assettag table (shown above) to have additional columns for various types (numeric, text, timestamp), allow them to be null, and then on insert, checking the corresponding 'key' to figure out what type of data it really is. However, I can see a lot of problems with that sort of implementation.
任何PostgreSQL-Ninjas可以提供一个关于如何解决这个问题的建议？我最近才回到数据库互动的深层次，所以我承认我有点生锈了。
Can any PostgreSQL-Ninjas out there offer a suggestion on how to approach this problem? I'm only recently getting thrown back into the deep-end of database interactions, so I admit I'm a bit rusty.
推荐答案
p>您基本上有两个选择：
You've basically got two choices:
有一列每个数据类型，但仅使用与要存储的数据类型匹配的列。当然，这导致大多数列是空的 - 浪费空间，但纯粹主义者喜欢它，因为强打字。有点笨重，必须检查每个列为null，以确定哪个数据类型适用。另外，如果你真的想要存储一个null，那么太糟糕了 - 那么你必须选择一个特定的值，意思是null - 更多的笨蛋。
Have one column for each data type, but only use the column that matches that data type you want to store. Of course this leads to most columns being null - a waste of space, but the purists like it because of the strong typing. It's a bit clunky having to check each column for null to figure out which datatype applies. Also, too bad if you actually want to store a null - then you must chose a specific value that "means null" - more clunkiness.
所有内容都可以表示为文本，所以有一个文本列的值，另一列（int或text）的类型，所以你的应用程序代码可以在正确的类型对象中恢复正确的值。好的，你可以轻松地将类型扩展到超出SQL数据类型的东西，而且你没有很多空值。
Everything can be expressed as text, so have a text column for the value, and another column (int or text) for the type, so your app code can restore the correct value in the correct type object. Good things are you can easily extend the types to something beyond SQL data types, and you don't have lots of nulls.
这篇关于在Postgres中同一列中存储不同数据类型的合理方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
