"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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
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. ft. -- Fernando TUSELL e-mail: Departamento de Econometr?a y Estad?stica etptupaf at bs.ehu.es Facultad de CC.EE. y Empresariales Tel: (+34)94.601.3733 Avenida Lendakari Aguirre, 83 Fax: (+34)94.601.3754 E-48015 BILBAO (Spain) Secr: (+34)94.601.3740 PGP: finger etptupaf at bsdx01.bs.ehu.es http://etdx01.bs.ehu.es ---------------------------------------------------------------------- -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
Doug's suggestions of joining two tables or defining a new type are a good ones, although you would have to do some manual manipulations in raw SQL. There is a general need to be able to split compound R types across more than one SQL column. It may be possible with RPgSQL if you redefind rpgsql.format.values.factor and friends, but I haven't tried this myself. It is something to think about as we develop a more general db interface. Tim F. Tusell wrote:>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. > > >ft. >-- Timothy H. Keitt Department of Ecology and Evolution State University of New York at Stony Brook Stony Brook, New York 11794 USA Phone: 631-632-1101, FAX: 631-632-7626 http://life.bio.sunysb.edu/ee/keitt/ -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
Additions to Doug Bates' suggestions> ... 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.I think this is probably the most clear and elegant way to do it.> Another possibility is to define a PostgreSQL data type for this.See the PosgreSQL documentation for "CREATE TYPE". Cheers Jason -- Indigo Industrial Controls Ltd. 64-21-343-545 jasont at indigoindustrial.co.nz -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._