[R] Ordered factors and DBMS

[Douglas Bates]

"F. Tusell" <etptupaf at bs.ehu.es> writes:

> Is there any way of preserving order of ordered factors when storing a
> table in PostgreSQL?
> 
> I have been  using PostgreSQL and the RPgSQL package  to store a large
> table with answers such  as "Strong disagreement", "Strong agreement",
> "Mild disagreement".  "Mild agreement".  When reading the  table, such
> answers are  turned into  factors, apparently in  reverse alphabetical
> order.  If I  reorder them  using ordered(factor,  levels=the  order I
> want), everything  is fine for the  session, but saving  the table and
> re-reading it reverts to the original order.
> 
> I have  a vague  feeling of having  seen this  asked, but I  could not
> locate any  references. I looked  also B.D. Ripley's  "Using Databases
> with R", but this problem is not discussed.

I think it would be difficult to do this in a single table in
PostgreSQL but it could be expressed naturally as two tables and a
join.  The first table would contain the data as factor levels and the
second table would give the correspondence between levels and labels.
The join would provide the data as labels by matching the levels.

Another possibility is to define a PostgreSQL data type for this.

MySQL does have this type of structure.  They call it an enum
data type.
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._