public inbox for [email protected]
help / color / mirror / Atom feedPostgresql 8.3X supports Arrays of Composite Types?
8+ messages / 5 participants
[nested] [flat]
* Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-19 21:03 Robson Fidalgo <[email protected]>
0 siblings, 4 replies; 8+ messages in thread
From: Robson Fidalgo @ 2009-04-19 21:03 UTC (permalink / raw)
To: pgsql-docs; [email protected]
Hello,
I am using postgresql 8.3X and I created a table (see example below) that
has an attribute that is an Array of a Composite Type (ROW). However, I do
not know how can I insert a record in this table.
Well, I've searched the mailing lists and have found little said about the
OR features. In postgres 8.3X on-line documentation (
http://www.postgresql.org/docs/8.3/static/arrays.html) I found "Arrays of
any built-in or user-defined base type, enum type, or composite type can be
created", however there is no example that explains how can I do an insert
into an attribute that is an Array of Composite Type. Considering the
example below, "persons have N phones" I create (with success) the Person
table with an array of phones, but I have not success with Insert values.
There is some one that can help me?
Thanks in advances,
Robson
Example:
CREATE table phone (
cod varchar,
num varchar);
CREATE TABLE person (
name varchar,
telephone phone[]);
Until here is everything ok, but I have not success with insert values, then
I tried:
1)insert into person values ('Joe', '{("1","1111"),("2","2222") }');
2)insert into person values ('Joe', array[('1','1111'),('2','2222')]);
3)insert into person values ('Joe', array[row('1','1111'),row('2','2222')]);
4)insert into person values ('Joe',
_phone[phone('1','1111'),phone('2','2222')]);**
** considering _phone = name of array type (automatically created by
postgres) and phone = name of composite type (also automatically created by
postgres)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-19 21:08 Robson Fidalgo <[email protected]>
parent: Robson Fidalgo <[email protected]>
3 siblings, 0 replies; 8+ messages in thread
From: Robson Fidalgo @ 2009-04-19 21:08 UTC (permalink / raw)
To: pgsql-docs; [email protected]
>
> Hello,
> I am using postgresql 8.3X and I created a table (see example below) that
> has an attribute that is an Array of a Composite Type (ROW). However, I do
> not know how can I insert a record in this table.
>
> Well, I've searched the mailing lists and have found little said about the
> OR features. In postgres 8.3X on-line documentation (
> http://www.postgresql.org/docs/8.3/static/arrays.html) I found "Arrays of
> any built-in or user-defined base type, enum type, or composite type can be
> created", however there is no example that explains how can I do an insert
> into an attribute that is an Array of Composite Type. Considering the
> example below, "persons have N phones" I create (with success) the Person
> table with an array of phones, but I have not success with Insert values.
> There is some one that can help me?
>
> Thanks in advances,
>
> Robson
>
> Example:
>
> CREATE table phone (
> cod varchar,
> num varchar);
>
> CREATE TABLE person (
> name varchar,
> telephone phone[]);
>
> Until here is everything ok, but I have not success with insert values,
> then I tried:
>
> 1)insert into person values ('Joe', '{("1","1111"),("2","2222") }');
> 2)insert into person values ('Joe', array[('1','1111'),('2','2222')]);
> 3)insert into person values ('Joe',
> array[row('1','1111'),row('2','2222')]);
> 4)insert into person values ('Joe',
> _phone[phone('1','1111'),phone('2','2222')]);**
> ** considering _phone = name of array type (automatically created by
> postgres) and phone = name of composite type (also automatically created by
> postgres)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-19 23:25 Tom Lane <[email protected]>
parent: Robson Fidalgo <[email protected]>
3 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2009-04-19 23:25 UTC (permalink / raw)
To: Robson Fidalgo <[email protected]>; +Cc: pgsql-docs; [email protected]
Robson Fidalgo <[email protected]> writes:
> I am using postgresql 8.3X and I created a table (see example below) that
> has an attribute that is an Array of a Composite Type (ROW). However, I do
> not know how can I insert a record in this table.
You need some explicit casts so that the array[] construct is seen to
have the right type, eg
insert into person values ('Joe', array[row('1','1111')::phone, row('2','2222')::phone]);
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-19 23:56 David Fetter <[email protected]>
parent: Robson Fidalgo <[email protected]>
3 siblings, 1 reply; 8+ messages in thread
From: David Fetter @ 2009-04-19 23:56 UTC (permalink / raw)
To: Robson Fidalgo <[email protected]>; +Cc: pgsql-docs; [email protected]
On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote:
> Hello,
> I am using postgresql 8.3X and I created a table (see example below)
> that has an attribute that is an Array of a Composite Type (ROW).
> However, I do not know how can I insert a record in this table.
You want a normalized table anyhow. If you want something
denormalized, use a view.
> Example:
>
> CREATE table phone (
> cod varchar,
> num varchar);
>
> CREATE TABLE person (
> name varchar,
> telephone phone[]);
This is better as:
CREATE TABLE phone (
cod VARCHAR,
num VARCHAR,
PRIMARY KEY(cod, num)
);
CREATE TABLE person (
name varchar,
PRIMARY KEY(name)
)
CREATE TABLE person_phone (
name VARCHAR NOT NULL REFERENCES person(name),
cod VARCHAR,
num VARCHAR,
FOREIGN KEY(cod, num) REFERENCES phone(cod, num),
PRIMARY KEY(name, cod, num)
);
Cheers,
David.
--
David Fetter <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [DOCS] Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-20 11:13 Robson Fidalgo <[email protected]>
parent: David Fetter <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Robson Fidalgo @ 2009-04-20 11:13 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs; [email protected]
Hi David,
Thanks for your help, but I want a relational-object solution. The solution
presented by Tom Lane (Thanks Tom!!!!) runs very well and it is a
relational-object implementation (I suggest put a similar example in
postgresql 8.3X documentation).
Cheers,
Robson.
On Sun, Apr 19, 2009 at 8:56 PM, David Fetter <[email protected]> wrote:
> On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote:
> > Hello,
> > I am using postgresql 8.3X and I created a table (see example below)
> > that has an attribute that is an Array of a Composite Type (ROW).
> > However, I do not know how can I insert a record in this table.
>
> You want a normalized table anyhow. If you want something
> denormalized, use a view.
>
> > Example:
> >
> > CREATE table phone (
> > cod varchar,
> > num varchar);
> >
> > CREATE TABLE person (
> > name varchar,
> > telephone phone[]);
>
> This is better as:
>
> CREATE TABLE phone (
> cod VARCHAR,
> num VARCHAR,
> PRIMARY KEY(cod, num)
> );
>
> CREATE TABLE person (
> name varchar,
> PRIMARY KEY(name)
> )
>
> CREATE TABLE person_phone (
> name VARCHAR NOT NULL REFERENCES person(name),
> cod VARCHAR,
> num VARCHAR,
> FOREIGN KEY(cod, num) REFERENCES phone(cod, num),
> PRIMARY KEY(name, cod, num)
> );
>
> Cheers,
> David.
> --
> David Fetter <[email protected]> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: [email protected]
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-docs mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-20 11:13 Robson Fidalgo <[email protected]>
parent: Robson Fidalgo <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Robson Fidalgo @ 2009-04-20 11:13 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs; [email protected]
On Mon, Apr 20, 2009 at 8:13 AM, Robson Fidalgo <[email protected]> wrote:
> Hi David,
>
>
>
> Thanks for your help, but I want a relational-object solution. The solution
> presented by Tom Lane (Thanks Tom!!!!) runs very well and it is a
> relational-object implementation (I suggest put a similar example in
> postgresql 8.3X documentation).
>
>
>
> Cheers,
>
> Robson.
>
> On Sun, Apr 19, 2009 at 8:56 PM, David Fetter <[email protected]> wrote:
>
>> On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote:
>> > Hello,
>> > I am using postgresql 8.3X and I created a table (see example below)
>> > that has an attribute that is an Array of a Composite Type (ROW).
>> > However, I do not know how can I insert a record in this table.
>>
>> You want a normalized table anyhow. If you want something
>> denormalized, use a view.
>>
>> > Example:
>> >
>> > CREATE table phone (
>> > cod varchar,
>> > num varchar);
>> >
>> > CREATE TABLE person (
>> > name varchar,
>> > telephone phone[]);
>>
>> This is better as:
>>
>> CREATE TABLE phone (
>> cod VARCHAR,
>> num VARCHAR,
>> PRIMARY KEY(cod, num)
>> );
>>
>> CREATE TABLE person (
>> name varchar,
>> PRIMARY KEY(name)
>> )
>>
>> CREATE TABLE person_phone (
>> name VARCHAR NOT NULL REFERENCES person(name),
>> cod VARCHAR,
>> num VARCHAR,
>> FOREIGN KEY(cod, num) REFERENCES phone(cod, num),
>> PRIMARY KEY(name, cod, num)
>> );
>>
>> Cheers,
>> David.
>> --
>> David Fetter <[email protected]> http://fetter.org/
>> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
>> Skype: davidfetter XMPP: [email protected]
>>
>> Remember to vote!
>> Consider donating to Postgres: http://www.postgresql.org/about/donate
>>
>> --
>> Sent via pgsql-docs mailing list ([email protected])
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-docs
>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-20 14:27 David Fetter <[email protected]>
parent: Robson Fidalgo <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: David Fetter @ 2009-04-20 14:27 UTC (permalink / raw)
To: Robson Fidalgo <[email protected]>; +Cc: pgsql-docs; [email protected]
On Mon, Apr 20, 2009 at 08:13:15AM -0300, Robson Fidalgo wrote:
> Hi David,
>
> Thanks for your help, but I want a relational-object solution.
You can have one without denormalizing. Just use VIEWs and rewrite
RULEs for INSERTs, UPDATEs and DELETEs on them.
> The solution presented by Tom Lane (Thanks Tom!!!!) runs very well
> and it is a relational-object implementation (I suggest put a
> similar example in postgresql 8.3X documentation).
The docs already contain an example:
http://www.postgresql.org/docs/current/static/rules-update.html
There are excellent reasons not to encourage people to do only half
the job. One part, the smaller part, is presenting an interface which
one part of your OO code can talk to. The other part, and the much
larger one, is having a well-indexed, normalized data store
underneath.
Example: Under the store-the-compound system you're proposing, how do
you find all the people who have a common prefix? Answer: Normalize.
If you need that answer quickly, you're looking at down time and DDL
changes. The questions you ask about the data are impossible to know
in advance, so normalized data helps you deal with that.
Cheers,
David.
--
David Fetter <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql 8.3X supports Arrays of Composite Types?
@ 2009-04-20 21:51 Merlin Moncure <[email protected]>
parent: Robson Fidalgo <[email protected]>
3 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2009-04-20 21:51 UTC (permalink / raw)
To: Robson Fidalgo <[email protected]>; +Cc: pgsql-docs; [email protected]
On Sun, Apr 19, 2009 at 5:03 PM, Robson Fidalgo <[email protected]> wrote:
> Until here is everything ok, but I have not success with insert values, then
> I tried:
>
> 1)insert into person values ('Joe', '{("1","1111"),("2","2222") }');
> 2)insert into person values ('Joe', array[('1','1111'),('2','2222')]);
> 3)insert into person values ('Joe', array[row('1','1111'),row('2','2222')]);
> 4)insert into person values ('Joe',
> _phone[phone('1','1111'),phone('2','2222')]);**
> ** considering _phone = name of array type (automatically created by
> postgres) and phone = name of composite type (also automatically created by
> postgres)
I agree with David -- arrays of composites should not be used in table
definitions. There are exceptions, but you have to be very cautious.
The phone number composite is basically ok, but I'd advise dropping
the array minimum. Here are the basic problems:
*) constraint checking vs. array of composites is problematic
*) updating a specific field of a specific composite is not really
possible...you have to build a complete new composite array and update
the table with it.
*) searching (who has a phone number x?) is a problem
Imagine a client changes one of his/her phone numbers and compare the
sql you would have to write doing it the classic way vs. your way.
As David noted, if you like the composite format in the presentation
of data, you can trivially do this in view. There may be reasons to
do this -- the advantages of composite are convenience in passing data
to/from functions and nesting data returned to the client. There is
no disadvantage of nesting data 'in query' -- that's how I do it and
it works very well.
There are a couple of exceptions to the 'no arrays in table'. You may
have a lot of static data (think float[]) that you are doing numerical
analysis on the client for example. It only ever gets
inserted/selected/deleted in bulk and never updated. There are other
exceptions, but they are rare. Usually it's better doing it the 'sql
way'
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-04-20 21:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-19 21:03 Postgresql 8.3X supports Arrays of Composite Types? Robson Fidalgo <[email protected]>
2009-04-19 21:08 ` Robson Fidalgo <[email protected]>
2009-04-19 23:25 ` Tom Lane <[email protected]>
2009-04-19 23:56 ` David Fetter <[email protected]>
2009-04-20 11:13 ` Robson Fidalgo <[email protected]>
2009-04-20 11:13 ` Robson Fidalgo <[email protected]>
2009-04-20 14:27 ` David Fetter <[email protected]>
2009-04-20 21:51 ` Merlin Moncure <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox