[GENERAL] Composite type evaluates to Null if any element is null
I have just re-read chapter 8.15 of the 8.3 manual and I am understanding why a composite type would evaluate to NUll if any of it elements are null. Can anyone explain this behavior? To me if I have a composite type column and there are some values in it, its incomplete, yes, but not null. Thanks for the feedback. Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Composite type evaluates to Null if any element is null
On Wed, Dec 17, 2008 at 12:23 PM, Andrew Gould andrewlylego...@gmail.com wrote: What are composite types used for? Do they allow you to search multiple fields for a value more easily? A number of things really. Starting with 8.4, they can be used with indexes and comparisons. so the list is growing. The main utility for composite types though is passing arguments in and (especially) returning arguments from functions. All tables have a automatically generated composite type backing them, so: select foo from foo; -- is legal in 8.4, we can do: create index foo_idx on foo((foo)); which optimizes things like: select * from foo order by foo limit 5; select foo from foo where foo = (1,2,3)::foo if foo is defined as (int, int, int); We can use composite types to get around subquery restrictions sometimes. -- illegal, field list subquery must return one row, one column select bar.* (select * from foo where bar_Id=bar.bar_id) from bar; -- but this works: select bar.*, (select foo from foo where bar_Id=bar.bar_id) from bar; -- as above, with foo expanded: select (bar).*, (foo).* from (select bar, (select foo from foo where bar_Id=bar.bar_id) from bar) q; -- starting with 8.3, we can make arrays of foo: select array_accum(foo) from foo; -- arrays can be nested: create table barfoo(bar, foo[]); select array(select (bar, (select array_accum(f) from f where f.bar_id = bar.bar_id))::barfoo); of course, if you were doing any of this in libpq, you absolutely would want to be using libpqtypes ;-) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Composite type evaluates to Null if any element is null
On Wed, Dec 17, 2008 at 11:09 AM, Ketema Harris ket...@ketema.net wrote: I have just re-read chapter 8.15 of the 8.3 manual and I am understanding why a composite type would evaluate to NUll if any of it elements are null. Can anyone explain this behavior? To me if I have a composite type column and there are some values in it, its incomplete, yes, but not null. can you be a little more specific? postgres=# create table ct(a int, b int, c int); CREATE TABLE postgres=# postgres=# select (1, null, 2)::ct; row (1,,2) (1 row) postgres=# select (1, null, 2)::ct is null; ?column? -- f (1 row) note, there is significant behavior change in the way composite type works in comparison purposes for 8.4, which I am using. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Composite type evaluates to Null if any element is null
What are composite types used for? Do they allow you to search multiple fields for a value more easily? Thanks, Andrew
