Postgresql – read about the behavior of the postgress casting operators

castpostgresql

I want to do something like this:

alter table foos
alter column bar type bool using bar::boolean;

bar is currently a text column. Some experimentation shows that postgres will do things like convert a string "t" to boolean true. In my case this is great, but I'd like to read the full behavior before using this. I can't seem to find where this documentation is.

Best Answer

The behavior for boolean

typically you have to go look up every type and to see how it can be cast or look for a function to do the casting

here is a link

Postgresql also supports Create Cast

Information on the pg_cast

Related Solutions

Postgresql – How to set weights on a Postgres TSVECTOR column

Okay so this was dumb of me. The weights work correctly. The issue I was having was because of the forced UTF8 encoding (I simply set "utf8" on the db, didn't rebuild. I set it back to latin1 and it works now).

PostgreSQL – How to Resolve Mysterious Inequality Behavior When Comparing Date Strings

What makes you think you are comparing dates?

Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text:

SELECT '20150526' > '2015-05-26' AS text2text
     , '20150526'::date > '2015-05-26'::date AS date2date;

text2text | date2date
----------+----------
 t        | f

Try:

SELECT pg_typeof(col) FROM (VALUES ('20150527')) t(col);  -- no cast

pg_typeof
---------
text

The result of your comparison of text values depends on your locale, on COLLATION rules to be precise. To see the default COLLATION of your session:

SHOW lc_collate;

COLLATION can be also be set per column or even per expression. To see the actual COLLATION for any expression use the collation for (any) construct:

SELECT collation for (my_column) FROM tbl LIMIT 1;

To test how strings are sorted in your locale:

SELECT *
FROM  (
   VALUES
     ('20150526')
   , ('2015-05-25')
   , ('20150525')
   , ('2015-05-26')
   , ('20150527')
   , ('2015-05-27')
   ) t(col)
ORDER BY col;    -- COLLATE "C"

Compare this to the sort order by pure byte-value by appending COLLATE "C" to ORDER BY. To see the result of your comparison with a different COLLATION:

SELECT '20150526' > '2015-05-26' COLLATE "C" AS text2text_c;

Best Answer

Related Solutions

Postgresql – How to set weights on a Postgres TSVECTOR column

PostgreSQL – How to Resolve Mysterious Inequality Behavior When Comparing Date Strings

Related Question