join of array

[Andrew L. Gould]

Re: Arrays and "goodness& quot; in RDBMSs (was Re: join of

On Friday 15 August 2003 02:56 pm, elein wrote:[color=blue]
> In response to both Andrew Gould and Ron Johnson...
>
> If arrays are not natural in the organization of
> your data, don't use them. That is the guideline.
>
> If the array defines something specific they are
> very natural. The confusion could be that arrays
> are abstract types.
>
> Specific implementations which use arrays might
> be clearer. For example, a definition of a polygon
> is an array of Points. Points, themselves are an
> array.
>
> (The actual postgreSQL implementation of polygons and points
> doesn't use the newer cleaner array abstraction, I think.
> But if I were reimplementing them, I would build on
> top of the new array capabilities. The point is to show
> an array structured object which makes sense in context.)
>
> Of course you can denomalize via arrays, but it tends
> to make things harder for you. And I believe the
> same thing is true for denormalized integer columns.
>
> elein
> =============== =============== =============== =============== =
> elein@varlena.c om www.varlena.com[/color]

Thanks, Elein. The polygon example makes it clearer. In the books I have
here, the examples show how to use arrays but they use data that I would move
to another table.

Best regards,

Andrew

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Ron Johnson]

Re: Arrays and "goodness& quot; in RDBMSs (was Re: join of

On Fri, 2003-08-15 at 15:36, Andrew L. Gould wrote:[color=blue]
> On Friday 15 August 2003 02:56 pm, elein wrote:[color=green]
> > In response to both Andrew Gould and Ron Johnson...
> >
> > If arrays are not natural in the organization of
> > your data, don't use them. That is the guideline.
> >
> > If the array defines something specific they are
> > very natural. The confusion could be that arrays
> > are abstract types.
> >
> > Specific implementations which use arrays might
> > be clearer. For example, a definition of a polygon
> > is an array of Points. Points, themselves are an
> > array.
> >
> > (The actual postgreSQL implementation of polygons and points
> > doesn't use the newer cleaner array abstraction, I think.
> > But if I were reimplementing them, I would build on
> > top of the new array capabilities. The point is to show
> > an array structured object which makes sense in context.)
> >
> > Of course you can denomalize via arrays, but it tends
> > to make things harder for you. And I believe the
> > same thing is true for denormalized integer columns.
> >
> > elein
> > =============== =============== =============== =============== =
> > elein@varlena.c om www.varlena.com[/color]
>
> Thanks, Elein. The polygon example makes it clearer. In the books I have
> here, the examples show how to use arrays but they use data that I would move
> to another table.[/color]

This is what makes me nervous about db arrays: the tendency for
denormalization .

--
+---------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@c ox.net |
| Jefferson, LA USA |
| |
| "Man, I'm pretty. Hoo Hah!" |
| Johnny Bravo |
+---------------------------------------------------------------+



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jason Godden]

Re: summary and request

create table import_contact (
id character(7) not null primary key,
fm character(30),
ls character(30),
addr character(30),
city character(25),
st character(2),
c character(1),
start decimal(6),
end decimal(6),
) WITHOUT OIDS;

cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
delimiter ',' null ''";

echo "insert into contact select
id,
case fm when null then 'xzxzxzxz' else fm end,
case ls when null then 'xzxzxzxz' else ls end,
case addr when null then '123 xzxzxzxz' else addr end,
case city when null then 'xzxzxzxz' else city end,
case st when null then 'xz' else st end,
case c when null then 'x' else c end,
case start when null then 122038 else start,
case end when null then 122038 else end
from import_contact; " | psql -dthedatabase

Could be one way although it's not atomic. Can rewrite the copy command to be
a copy from file command to do that and use the \i command (or redirect to
psql from file/stdin). Simple but there are many other methods to get this
thing to work. If you don't want to recreate the defaults everytime then you
could have subselects that reference the pg system tables extract the default
value for the columns you are looking for.

Also could create the insert statements with a script on the outside or
replace any blank (null in reality) fields with the default value and copy
that straight to the table.

On Sat, 16 Aug 2003 03:18 am, expect wrote:[color=blue]
> I'd like to summarize what I know (or don't know) since this topic has been
> hit around a little and I'm new to this. I'm hoping it will clear things
> up, at least for me. You are all the experts, I want to make sure I am
> singing from the same page.
>
> data sample:
> id | fm | ls | addr | city | st | z |c|
> start|end
> ---------------------------------------------------------------------------
>-------
>
> 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> Street,Yountvil le,CA,94599,5,0 62001,082009 339111C,Elma Thelma,Velma,98 Oak
> Lane,St. Louis,MO,63119-2065,,,
>
>
> What I wanted to do was to import lots of these from a text file. In the
> case where there is an empty string (i.e. no value after a comma) I wanted
> to define the column in the table in a way that would accept the empty
> string but replace it with the default value for that column. I didn't
> know that the copy command is just some C code that stuffs the data into
> the db ala fois grois.
>
> What I would really benefit from (and I hope some other new soul would too)
> is if someone would outline exactly how they would approach this problem.
>
> Maybe provide the correct table definition and the copy command. Or if
> that just won't work an alternate approach. I realize that some of you
> have done this partially but there have been too many replies to get into a
> single cohesive instruction.
>
>
> Anyway I suppose my initial frustration in trying to do this may have
> blinded me from reason.
>
>
> create table contact (
> id character(7) NOT NULL,
> fm character(30) DEFAULT 'xzxzxzxz',
> ls character(30) DEFAULT 'xzxzxzxz',
> addr character(30) DEFAULT '123 xzxzxzxz',
> city character(25) DEFAULT 'xzxzxzxz',
> st character(2) DEFAULT 'xz',
> c character(1) DEFAULT 'x',
> start decimal(6) DEFAULT 122038,
> end decimal(6) DEFAULT 122038,
> CONSTRAINT handle PRIMARY KEY (id)
> ) WITHOUT OIDS;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)[/color]


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[expect]

Re: summary and request

On Sat, 16 Aug 2003 09:33:51 +1000
Jason Godden <jasongodden@op tushome.com.au> wrote:


Ahh, thanks for this. And thanks to all the others that helped me on my way.
Hopefully I'll be able to give something back to the group. Although that
might be hard with all the experts here.

Perhaps I can document this and provide it for public consumption.

[color=blue]
> create table import_contact (
> id character(7) not null primary key,
> fm character(30),
> ls character(30),
> addr character(30),
> city character(25),
> st character(2),
> c character(1),
> start decimal(6),
> end decimal(6),
> ) WITHOUT OIDS;
>
> cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
> delimiter ',' null ''";
>
> echo "insert into contact select
> id,
> case fm when null then 'xzxzxzxz' else fm end,
> case ls when null then 'xzxzxzxz' else ls end,
> case addr when null then '123 xzxzxzxz' else addr end,
> case city when null then 'xzxzxzxz' else city end,
> case st when null then 'xz' else st end,
> case c when null then 'x' else c end,
> case start when null then 122038 else start,
> case end when null then 122038 else end
> from import_contact; " | psql -dthedatabase
>
> Could be one way although it's not atomic. Can rewrite the copy command to be
> a copy from file command to do that and use the \i command (or redirect to
> psql from file/stdin). Simple but there are many other methods to get this
> thing to work. If you don't want to recreate the defaults everytime then you
> could have subselects that reference the pg system tables extract the default
> value for the columns you are looking for.
>
> Also could create the insert statements with a script on the outside or
> replace any blank (null in reality) fields with the default value and copy
> that straight to the table.
>
> On Sat, 16 Aug 2003 03:18 am, expect wrote:[color=green]
> > I'd like to summarize what I know (or don't know) since this topic has been
> > hit around a little and I'm new to this. I'm hoping it will clear things
> > up, at least for me. You are all the experts, I want to make sure I am
> > singing from the same page.
> >
> > data sample:
> > id | fm | ls | addr | city | st | z |c|
> > start|end
> > ---------------------------------------------------------------------------
> >-------
> >
> > 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> > Street,Yountvil le,CA,94599,5,0 62001,082009 339111C,Elma Thelma,Velma,98 Oak
> > Lane,St. Louis,MO,63119-2065,,,
> >
> >
> > What I wanted to do was to import lots of these from a text file. In the
> > case where there is an empty string (i.e. no value after a comma) I wanted
> > to define the column in the table in a way that would accept the empty
> > string but replace it with the default value for that column. I didn't
> > know that the copy command is just some C code that stuffs the data into
> > the db ala fois grois.
> >
> > What I would really benefit from (and I hope some other new soul would too)
> > is if someone would outline exactly how they would approach this problem.
> >
> > Maybe provide the correct table definition and the copy command. Or if
> > that just won't work an alternate approach. I realize that some of you
> > have done this partially but there have been too many replies to get into a
> > single cohesive instruction.
> >
> >
> > Anyway I suppose my initial frustration in trying to do this may have
> > blinded me from reason.
> >
> >
> > create table contact (
> > id character(7) NOT NULL,
> > fm character(30) DEFAULT 'xzxzxzxz',
> > ls character(30) DEFAULT 'xzxzxzxz',
> > addr character(30) DEFAULT '123 xzxzxzxz',
> > city character(25) DEFAULT 'xzxzxzxz',
> > st character(2) DEFAULT 'xz',
> > c character(1) DEFAULT 'x',
> > start decimal(6) DEFAULT 122038,
> > end decimal(6) DEFAULT 122038,
> > CONSTRAINT handle PRIMARY KEY (id)
> > ) WITHOUT OIDS;
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Jason Godden]

Re: summary and request - correction

Just a note on that example:

I didn't properly end the case commands on the last two fields in the insert
and end should probably be quoted. No I haven't tested it. Should be:

echo "insert into contact select
id,
case fm when null then 'xzxzxzxz' else fm end,
case ls when null then 'xzxzxzxz' else ls end,
case addr when null then '123 xzxzxzxz' else addr end,
case city when null then 'xzxzxzxz' else city end,
case st when null then 'xz' else st end,
case c when null then 'x' else c end,
case start when null then 122038 else start end,
case "end" when null then 122038 else "end" end
from import_contact; " | psql -dthedatabase

Rgds,

Jason

On Sat, 16 Aug 2003 01:10 pm, expect wrote:[color=blue]
> On Sat, 16 Aug 2003 09:33:51 +1000
> Jason Godden <jasongodden@op tushome.com.au> wrote:
>
>
> Ahh, thanks for this. And thanks to all the others that helped me on my
> way. Hopefully I'll be able to give something back to the group. Although
> that might be hard with all the experts here.
>
> Perhaps I can document this and provide it for public consumption.
>[color=green]
> > create table import_contact (
> > id character(7) not null primary key,
> > fm character(30),
> > ls character(30),
> > addr character(30),
> > city character(25),
> > st character(2),
> > c character(1),
> > start decimal(6),
> > end decimal(6),
> > ) WITHOUT OIDS;
> >
> > cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
> > delimiter ',' null ''";
> >
> > echo "insert into contact select
> > id,
> > case fm when null then 'xzxzxzxz' else fm end,
> > case ls when null then 'xzxzxzxz' else ls end,
> > case addr when null then '123 xzxzxzxz' else addr end,
> > case city when null then 'xzxzxzxz' else city end,
> > case st when null then 'xz' else st end,
> > case c when null then 'x' else c end,
> > case start when null then 122038 else start,
> > case end when null then 122038 else end
> > from import_contact; " | psql -dthedatabase
> >
> > Could be one way although it's not atomic. Can rewrite the copy command
> > to be a copy from file command to do that and use the \i command (or
> > redirect to psql from file/stdin). Simple but there are many other
> > methods to get this thing to work. If you don't want to recreate the
> > defaults everytime then you could have subselects that reference the pg
> > system tables extract the default value for the columns you are looking
> > for.
> >
> > Also could create the insert statements with a script on the outside or
> > replace any blank (null in reality) fields with the default value and
> > copy that straight to the table.
> >
> > On Sat, 16 Aug 2003 03:18 am, expect wrote:[color=darkred]
> > > I'd like to summarize what I know (or don't know) since this topic has
> > > been hit around a little and I'm new to this. I'm hoping it will clear
> > > things up, at least for me. You are all the experts, I want to make
> > > sure I am singing from the same page.
> > >
> > > data sample:
> > > id | fm | ls | addr | city | st | z |c|
> > > start|end
> > > -----------------------------------------------------------------------
> > >---- -------
> > >
> > > 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> > > Street,Yountvil le,CA,94599,5,0 62001,082009 339111C,Elma Thelma,Velma,98
> > > Oak Lane,St. Louis,MO,63119-2065,,,
> > >
> > >
> > > What I wanted to do was to import lots of these from a text file. In
> > > the case where there is an empty string (i.e. no value after a comma)
> > > I wanted to define the column in the table in a way that would accept
> > > the empty string but replace it with the default value for that column.
> > > I didn't know that the copy command is just some C code that stuffs
> > > the data into the db ala fois grois.
> > >
> > > What I would really benefit from (and I hope some other new soul would
> > > too) is if someone would outline exactly how they would approach this
> > > problem.
> > >
> > > Maybe provide the correct table definition and the copy command. Or if
> > > that just won't work an alternate approach. I realize that some of you
> > > have done this partially but there have been too many replies to get
> > > into a single cohesive instruction.
> > >
> > >
> > > Anyway I suppose my initial frustration in trying to do this may have
> > > blinded me from reason.
> > >
> > >
> > > create table contact (
> > > id character(7) NOT NULL,
> > > fm character(30) DEFAULT 'xzxzxzxz',
> > > ls character(30) DEFAULT 'xzxzxzxz',
> > > addr character(30) DEFAULT '123 xzxzxzxz',
> > > city character(25) DEFAULT 'xzxzxzxz',
> > > st character(2) DEFAULT 'xz',
> > > c character(1) DEFAULT 'x',
> > > start decimal(6) DEFAULT 122038,
> > > end decimal(6) DEFAULT 122038,
> > > CONSTRAINT handle PRIMARY KEY (id)
> > > ) WITHOUT OIDS;
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 2: you can get off all lists
> > > at once with the unregister command (send "unregister
> > > YourEmailAddres sHere" to majordomo@postg resql.org)[/color]
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org[/color]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postg resql.org so that your
> message can get through to the mailing list cleanly[/color]


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Matthew D. Fuller]

Re: Arrays and &quot;goodness& quot; in RDBMSs (was Re: join of array)

On Fri, Aug 15, 2003 at 01:37:50PM -0500 I heard the voice of
Andrew L. Gould, and lo! it spake thus:[color=blue]
> On Friday 15 August 2003 01:13 pm, Ron Johnson wrote:[color=green]
> >
> > Why are arrays even mentioned in the the same breath wrt relations
> > DBMSs? Aren't they an anathema to all we know and love?[/color]
>
> This gives rise to a couple of good questions:
>
> When and why would you want to use arrays instead of a relational model?[/color]

When it's appropriate 8-}

I've found it to be extremely rare, but it DOES happen. For instance, in
one project, I needed a bit of data in a record, which could store
between 1 and 7 (integer) values in it. Never less than 1, never more
than 7, and the ordering of them was essential to preserve. I *COULD* do
it in another table, with an order column (which would have to be
maintained 'manually' in the code), and, if one were neurotic, a seperate
constraint to keep from somehow getting too many records for each item in
the other table.... but it was far easier to just slap it in an array.

Now, any time ordering isn't important, OR a field can grow without
realistic bounds, arrays naturally aren't important. For instance, if
I'd needed 15 entries, I might well have gone ahead and referenced
another table, just because it would get too irritating dealing with the
arrays.



--
Matthew Fuller (MF4839) | fullermd@over-yonder.net
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/

"The only reason I'm burning my candle at both ends, is because I
haven't figured out how to light the middle yet"

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html