I would note that if you are using insert-dao or update-dao, certain col-types will be enforced and throw errors. For example, smallint and boolean will throw errors if not provided the correct value types. Double-float will throw errors if provided a string. On the other hand, string will quite happily take an integer and and insert it into your database as a string. Providing a col-type of bpchar to some slot, even though postgresql will think that you want a single character, will not throw an error if you provide a string longer than a single character.
Numbers
Remember the test table from the introduction? It has a timestamp with timezone field called "date", a numeric field called "number-test" and a money field called "money".
Lisp has a ratio datatype. In other words, instead of trying to come up with a floating point number for 1/3, common lisp simply views it as 1/3. But how does that translate into storage in postgresql?
(query (:insert-into 'test :set 'id 1 'number-test (/ 1 3) 'text "one third"))(query (:select 'number-test :from 'test :where (:= 'id 1)) :single)3333333333333333333333333333333333333/10000000000000000000000000000000000000We inserted a ratio 1/3 into the numeric field, then got something back that was not quite the same, but is decimal ratio truncated at 37 decimal points.
Now insert that same ratio into the money field.
(query (:insert-into 'test :set 'id 1 'money (/ 1 3) 'text "one third money"))(query (:select 'money :from 'test :where (:= 'id 1)) :single)"$0.33"Here you notice that you got back a string that looks like a number truncated at two decimal points (the pennies). You would have to convert it to something else in order to perform any mathematical calculations.
So, for example, you might reach for the wu-decimal package or the decimals package and call wu-decimal:parse-decimal or decimals:parse-decimal-number like so:
(wu-decimal:parse-decimal "$0.33" :start 1)33/100Notice that we tried to start at 1 in order to get rid of the monetary indicator at the front. However, that doesn't work if the number was negative. Better is just removing the monetary indicator.
(wu-decimal:parse-decimal (remove #\$ "-$0.33"))-33/100or, using the decimals package:
(decimals:parse-decimal-number (remove #\$ "-$0.33"))-33/100Timestamps with the local-time package
Postgresql keeps everything in a single timezone - UTC. Then everything else is set using the offset. See, e.g. http://www.postgresql.org/docs/9.1/static/datatype-datetime.html
"For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.
When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct."
So, looking at a server that is set for PDT, for table test with fields id, date, number_test, money and text
Default with no timezone or offset:
(query (:insert-into 'test :set 'id 3 'text "insert here" 'date (local-time:encode-timestamp 0 0 0 12 01 01 2013)))2013-01-01 12:00:00-08 (looking at the default timezone for the server, postgresql has set the timezone to UTC less 8 hours - UTC time would be 04:00:00)
Using offset to explicitly offset 1 hour from UTC (e.g. Paris)
(query (:insert-into 'test :set 'id 4 'text "offset 1 hour" 'date (local-time:encode-timestamp 0 0 0 12 01 01 2013 :offset 3600)))2013-01-01 03:00:00-08 (looking at the default timezone for the server, postgresql has kept the timezone as PDT - UTC less 8 hours - but set the time as 03:00:00, which is 1 hour ahead of UTC)
Using timezone to explicitly set it for UTC
(query (:insert-into 'test :set 'id 5 'text "insert here using timezone utc" 'date (local-time:encode-timestamp 0 0 0 12 01 01 2013 :timezone local-time::+utc-zone+)))2013-01-01 04:00:00-08 (looking at the default timezone for the server, postgresql has kept the timezone as PDT - UTC less 8 hours - but set the time as 04:00:00, which is the time in UTC relative to the PDT time at the server.
See Time Functions for information on specific functions dealing with time.
Normally, the use of :order-by would order the results in ascending order. You can apply :desc to a column and :asc to another column to re-arrange how the order-by rules will work.
(query (:order-by (:select 'id 'name :from 'regions) (:desc 'id)))((11 "Eastern Europe") (10 "Caribbean") (9 "Pacific") (8 "Central Asia") (7 "South America") (6 "North America") (5 "Middle East") (4 "Western Europe") (3 "Central America") (2 "Asia") (1 "Africa"))The Distinct keyword is used to eliminate duplicative rows. In s-sql the keyword :distinct comes after the select arguments and prior to the keyword :from.
The postmodern s-sql syntax would look like:
(query (:select 'regions.name :distinct :from 'countries 'regions :where (:and (:< 'latitude 0) (:= 'regions.id 'region-id))))(("Pacific") ("Asia") ("Africa") ("South America"))As indicated in the postgresql documentation, the DISTINCT ON clause is not part of the sql standard. A set of rows for which all the expressions are equal are considered duplicates and only the first row of the set is kept. This is a convenience but can had indeterminate results.
The postmodern s-sql syntax would look like:
(query (:select 'id 'name 'region-id :distinct-on 'region-id :from 'countries))((165 "Gabon" 1) (102 "Nepal" 2) (73 "Nicaragua" 3) (20 "UK" 4) (51 "Egypt" 5) (166 "Greenland" 6) (75 "Honduras" 7) (184 "Turkmenistan" 8)(108 "Papua New Guinea" 9) (121 "Antigua" 10) (67 "Belarus" 11))