Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

LOL - I don't think there are any natural keys here. Traditional scientific
names are amazingly flaky. I guess I shouldn't call them flaky; it's just
that no one has ever figured out a way do deal with all the complexities of
classification. The new LSID's might be more stable - but which LSID does
one choose? But it's amazing how many "aliases" are attached to many
taxonomic names; utterly bewildering.

On Sun, Oct 25, 2015 at 10:09 PM, Adrian Klaver 
wrote:

> On 10/25/2015 09:10 PM, David Blomstrom wrote:
>
>> It's also interesting that some entities (e.g. EOL) are now using
>> something called Life Science ID's (or something like that) in lieu of
>> traditional scientific names. It sounds like a cool idea, but some of
>> the LSID's seem awfully big and complex to me. I haven't figured out
>> exactly what the codes mean.
>>
>
> Aah, the natural key vs surrogate key conversation rears its head.
>
>
>
>> Then again, when I navigate to the Encyclopedia of Life's aardvark page
>> @ http://www.eol.org/pages/327830/overview the code is actually
>> amazingly short.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

On 10/25/2015 09:10 PM, David Blomstrom wrote:

It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in lieu of
traditional scientific names. It sounds like a cool idea, but some of
the LSID's seem awfully big and complex to me. I haven't figured out
exactly what the codes mean.


Aah, the natural key vs surrogate key conversation rears its head.



Then again, when I navigate to the Encyclopedia of Life's aardvark page
@ http://www.eol.org/pages/327830/overview the code is actually
amazingly short.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Recursive Arrays 101

[David Blomstrom]

My ultimate goal is to have separate fields for 1) traditional scientific
names, 2) LSID's and 3) common names, which are the most confusing thing of
all. Some common names are relatively simple and more stable than
scientific names - e.g. aardvark and polar bear. The URL
MySite/life/polar-bear will always point to the same species, even if
scientists reclassified it as a plant or fungus.

But others are more confusing. For example, bison and beaver are both
common names and genus names. (Scientists now recognize two separate
species of beaver, both in the genus Castor.)

I also have to learn how to use the new search function, Elastic, or
whatever it's called. Speaking of which, I just discovered the new Russian
and Chinese search engines, Yandex and Baidu. They have some interesting
possibilities, too. ;)

On Sun, Oct 25, 2015 at 9:12 PM, David Blomstrom 
wrote:

> Making it more confusing, I believe there are several different series of
> numerical ID's. See this page, for example...
> https://www.wikidata.org/wiki/Q46212
>
> On Sun, Oct 25, 2015 at 9:10 PM, David Blomstrom <
> david.blomst...@gmail.com> wrote:
>
>> It's also interesting that some entities (e.g. EOL) are now using
>> something called Life Science ID's (or something like that) in lieu of
>> traditional scientific names. It sounds like a cool idea, but some of the
>> LSID's seem awfully big and complex to me. I haven't figured out exactly
>> what the codes mean.
>>
>> Then again, when I navigate to the Encyclopedia of Life's aardvark page @
>> http://www.eol.org/pages/327830/overview the code is actually amazingly
>> short.
>>
>> On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <
>> david.blomst...@gmail.com> wrote:
>>
>>> What was amazed me is the HUGE tables (as in too big to work with or
>>> publish online) that, as near as I can remember, have rows like this...
>>>
>>> panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia |
>>> Chordata | Animalia
>>>
>>> cramming virtually the entire hierarchy into every single row. Some of
>>> my tables have extra columns listing every species family and order, which
>>> most people would consider sloppy. But that's tame compared to how they do
>>> it.
>>>
>>> I've never been able to make their downloads work on my Mac laptop, and
>>> the PHP is too complex for me to figure out. Nor have they ever replied to
>>> my e-mails. But the websites using their scheme include the Encyclopedia of
>>> Life (EOL).
>>>
>>> I'm focusing on creating a polished database focusing on vertebrates,
>>> along with select invertebrates and plants. After I get that squared away,
>>> I'd like to try adding the Catalogue of Life's entire database. The
>>> Encyclopedia of Life and WIkipedia are both enormous projects, but there
>>> are some amazing gaps in both projects that I hope to fill.
>>>
>>> On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <
>>> adrian.kla...@aklaver.com> wrote:
>>>
 On 10/25/2015 06:10 PM, David Blomstrom wrote:

> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
> kingdoms, classes and on down to species. I'll research foreign keys
> and
> see what I can come up with. I hope I can make separate tables for
> mammal species, bird species, fish species, etc. There are just so many
> species - especially fish - the spreadsheets I use to organize them are
> just about maxed out as it is.
>

 If you go here:


 http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

 that is how you can drill down to a species in the CoL.

 It just seems to follow what is already there. No doubt, there are a
 lot of species. What is probably more important is that the relationships
 have changed over time and can be expected to change more, as genetic
 testing for the purpose of taxonomic classification becomes more prevalent.


> I've been using the Catalogue of Life as a guide, but I'm limited
> because I can never get their downloads to work. So all I can do is go
> to their website and copy a bunch of genera and species at a time.
>

 Well I downloaded the 2015 snapshot and it turns out it is MySQL
 specific. Recently upgraded this computer, will have to see if
 MySQL/Mariadb survived the process before I can go any further. It would be
 interesting to see how they tackled the relationships.



> However, I did open up some of the tables I downloaded and was amazed
> at
> how apparently amateurish they are. Yet their site works just fine and
> is fast enough.
>
> @ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
> Life, which is doing what I was attempting to do years ago.
>
>

 --
 Adrian Klaver
 adrian.kla...@aklaver.com

>>>
>>>
>>>
>>> --
>>> David Blomstrom
>>> Writer & Web Designer (Mac, M$ & Linux)
>>> www.geobop.org
>>>
>>
>>
>>
>>

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

Making it more confusing, I believe there are several different series of
numerical ID's. See this page, for example...
https://www.wikidata.org/wiki/Q46212

On Sun, Oct 25, 2015 at 9:10 PM, David Blomstrom 
wrote:

> It's also interesting that some entities (e.g. EOL) are now using
> something called Life Science ID's (or something like that) in lieu of
> traditional scientific names. It sounds like a cool idea, but some of the
> LSID's seem awfully big and complex to me. I haven't figured out exactly
> what the codes mean.
>
> Then again, when I navigate to the Encyclopedia of Life's aardvark page @
> http://www.eol.org/pages/327830/overview the code is actually amazingly
> short.
>
> On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <
> david.blomst...@gmail.com> wrote:
>
>> What was amazed me is the HUGE tables (as in too big to work with or
>> publish online) that, as near as I can remember, have rows like this...
>>
>> panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia |
>> Chordata | Animalia
>>
>> cramming virtually the entire hierarchy into every single row. Some of my
>> tables have extra columns listing every species family and order, which
>> most people would consider sloppy. But that's tame compared to how they do
>> it.
>>
>> I've never been able to make their downloads work on my Mac laptop, and
>> the PHP is too complex for me to figure out. Nor have they ever replied to
>> my e-mails. But the websites using their scheme include the Encyclopedia of
>> Life (EOL).
>>
>> I'm focusing on creating a polished database focusing on vertebrates,
>> along with select invertebrates and plants. After I get that squared away,
>> I'd like to try adding the Catalogue of Life's entire database. The
>> Encyclopedia of Life and WIkipedia are both enormous projects, but there
>> are some amazing gaps in both projects that I hope to fill.
>>
>> On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver > > wrote:
>>
>>> On 10/25/2015 06:10 PM, David Blomstrom wrote:
>>>
 @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
 kingdoms, classes and on down to species. I'll research foreign keys and
 see what I can come up with. I hope I can make separate tables for
 mammal species, bird species, fish species, etc. There are just so many
 species - especially fish - the spreadsheets I use to organize them are
 just about maxed out as it is.

>>>
>>> If you go here:
>>>
>>>
>>> http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57
>>>
>>> that is how you can drill down to a species in the CoL.
>>>
>>> It just seems to follow what is already there. No doubt, there are a lot
>>> of species. What is probably more important is that the relationships have
>>> changed over time and can be expected to change more, as genetic testing
>>> for the purpose of taxonomic classification becomes more prevalent.
>>>
>>>
 I've been using the Catalogue of Life as a guide, but I'm limited
 because I can never get their downloads to work. So all I can do is go
 to their website and copy a bunch of genera and species at a time.

>>>
>>> Well I downloaded the 2015 snapshot and it turns out it is MySQL
>>> specific. Recently upgraded this computer, will have to see if
>>> MySQL/Mariadb survived the process before I can go any further. It would be
>>> interesting to see how they tackled the relationships.
>>>
>>>
>>>
 However, I did open up some of the tables I downloaded and was amazed at
 how apparently amateurish they are. Yet their site works just fine and
 is fast enough.

 @ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
 Life, which is doing what I was attempting to do years ago.


>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>>
>> --
>> David Blomstrom
>> Writer & Web Designer (Mac, M$ & Linux)
>> www.geobop.org
>>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

It's also interesting that some entities (e.g. EOL) are now using something
called Life Science ID's (or something like that) in lieu of traditional
scientific names. It sounds like a cool idea, but some of the LSID's seem
awfully big and complex to me. I haven't figured out exactly what the codes
mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @
http://www.eol.org/pages/327830/overview the code is actually amazingly
short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom 
wrote:

> What was amazed me is the HUGE tables (as in too big to work with or
> publish online) that, as near as I can remember, have rows like this...
>
> panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata
> | Animalia
>
> cramming virtually the entire hierarchy into every single row. Some of my
> tables have extra columns listing every species family and order, which
> most people would consider sloppy. But that's tame compared to how they do
> it.
>
> I've never been able to make their downloads work on my Mac laptop, and
> the PHP is too complex for me to figure out. Nor have they ever replied to
> my e-mails. But the websites using their scheme include the Encyclopedia of
> Life (EOL).
>
> I'm focusing on creating a polished database focusing on vertebrates,
> along with select invertebrates and plants. After I get that squared away,
> I'd like to try adding the Catalogue of Life's entire database. The
> Encyclopedia of Life and WIkipedia are both enormous projects, but there
> are some amazing gaps in both projects that I hope to fill.
>
> On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver 
> wrote:
>
>> On 10/25/2015 06:10 PM, David Blomstrom wrote:
>>
>>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>>> kingdoms, classes and on down to species. I'll research foreign keys and
>>> see what I can come up with. I hope I can make separate tables for
>>> mammal species, bird species, fish species, etc. There are just so many
>>> species - especially fish - the spreadsheets I use to organize them are
>>> just about maxed out as it is.
>>>
>>
>> If you go here:
>>
>>
>> http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57
>>
>> that is how you can drill down to a species in the CoL.
>>
>> It just seems to follow what is already there. No doubt, there are a lot
>> of species. What is probably more important is that the relationships have
>> changed over time and can be expected to change more, as genetic testing
>> for the purpose of taxonomic classification becomes more prevalent.
>>
>>
>>> I've been using the Catalogue of Life as a guide, but I'm limited
>>> because I can never get their downloads to work. So all I can do is go
>>> to their website and copy a bunch of genera and species at a time.
>>>
>>
>> Well I downloaded the 2015 snapshot and it turns out it is MySQL
>> specific. Recently upgraded this computer, will have to see if
>> MySQL/Mariadb survived the process before I can go any further. It would be
>> interesting to see how they tackled the relationships.
>>
>>
>>
>>> However, I did open up some of the tables I downloaded and was amazed at
>>> how apparently amateurish they are. Yet their site works just fine and
>>> is fast enough.
>>>
>>> @ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
>>> Life, which is doing what I was attempting to do years ago.
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
>
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

What was amazed me is the HUGE tables (as in too big to work with or
publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata
| Animalia

cramming virtually the entire hierarchy into every single row. Some of my
tables have extra columns listing every species family and order, which
most people would consider sloppy. But that's tame compared to how they do
it.

I've never been able to make their downloads work on my Mac laptop, and the
PHP is too complex for me to figure out. Nor have they ever replied to my
e-mails. But the websites using their scheme include the Encyclopedia of
Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates, along
with select invertebrates and plants. After I get that squared away, I'd
like to try adding the Catalogue of Life's entire database. The
Encyclopedia of Life and WIkipedia are both enormous projects, but there
are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver 
wrote:

> On 10/25/2015 06:10 PM, David Blomstrom wrote:
>
>> @ Adrian Klaver: Oh, so you're suggesting I make separate tables for
>> kingdoms, classes and on down to species. I'll research foreign keys and
>> see what I can come up with. I hope I can make separate tables for
>> mammal species, bird species, fish species, etc. There are just so many
>> species - especially fish - the spreadsheets I use to organize them are
>> just about maxed out as it is.
>>
>
> If you go here:
>
>
> http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57
>
> that is how you can drill down to a species in the CoL.
>
> It just seems to follow what is already there. No doubt, there are a lot
> of species. What is probably more important is that the relationships have
> changed over time and can be expected to change more, as genetic testing
> for the purpose of taxonomic classification becomes more prevalent.
>
>
>> I've been using the Catalogue of Life as a guide, but I'm limited
>> because I can never get their downloads to work. So all I can do is go
>> to their website and copy a bunch of genera and species at a time.
>>
>
> Well I downloaded the 2015 snapshot and it turns out it is MySQL specific.
> Recently upgraded this computer, will have to see if MySQL/Mariadb survived
> the process before I can go any further. It would be interesting to see how
> they tackled the relationships.
>
>
>
>> However, I did open up some of the tables I downloaded and was amazed at
>> how apparently amateurish they are. Yet their site works just fine and
>> is fast enough.
>>
>> @ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
>> Life, which is doing what I was attempting to do years ago.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

On 10/25/2015 06:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.


If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot 
of species. What is probably more important is that the relationships 
have changed over time and can be expected to change more, as genetic 
testing for the purpose of taxonomic classification becomes more prevalent.




I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.


Well I downloaded the 2015 snapshot and it turns out it is MySQL 
specific. Recently upgraded this computer, will have to see if 
MySQL/Mariadb survived the process before I can go any further. It would 
be interesting to see how they tackled the relationships.




However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Duplicate rows during pg_dump

[Tom Lane]

Jim Nasby  writes:
> On 10/24/15 3:15 PM, Marc Mamin wrote:
> Any suggestions for what to look for next? Is it table corruption?
>> Most likely is the index corrupt, not the table.
>> You should check for further duplicates, fix them and as Adrian writes,
>> build a new index an then drop the corrupt one.
>> 
>> I've seen this a few times before, and if I recall well it was always after 
>> some plate got full.
>> Is AWS getting out of space:)

> You should report this to the RDS team, because an out of space 
> condition shouldn't leave multiple values in the index. I suspect 
> they've made a modification somewhere that is causing this. It could be 
> a base Postgres bug, but I'd think we'd have caught such a bug by now...

Notable also is that pg_dump invariably reads tables with a plain "COPY foo"
or "SELECT * FROM foo", which should ignore all indexes and just read the
table contents.  So I doubt that reindexing will fix anything: you almost
certainly do have duplicate rows in the base table.  It's highly likely
that the index is corrupt, which is what would be necessary to get into
such a state ... but you will need to manually remove the dup rows before
rebuilding the unique index will succeed.

regards, tom lane


-- 
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] Recursive Arrays 101

[John R Pierce]

On 10/25/2015 6:10 PM, David Blomstrom wrote:

What does EOL mean?


"End of Life"



--
john r pierce, recycling bits in santa cruz



--
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] Recursive Arrays 101

[David Blomstrom]

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for mammal
species, bird species, fish species, etc. There are just so many species -
especially fish - the spreadsheets I use to organize them are just about
maxed out as it is.

I've been using the Catalogue of Life as a guide, but I'm limited because I
can never get their downloads to work. So all I can do is go to their
website and copy a bunch of genera and species at a time.

However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and is
fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

On Sun, Oct 25, 2015 at 2:07 PM, Alban Hertroys  wrote:

>
> > On 25 Oct 2015, at 19:38, Adrian Klaver 
> wrote:
> >
> > On 10/25/2015 11:12 AM, David Blomstrom wrote:
> >> I'm sorry, I don't know exactly what you mean by "definitions." The
> >> fields Taxon and Parent are both varchar, with a 50-character limit.
> >> ParentID is int(1).
> >
> > By definition I meant the schema, so from the below:
> >
> > CREATE TABLE t (
> > N INT(6) default None auto_increment,
> > Taxon varchar(50) default NULL,
> > Parent varchar(25) default NULL,
> > NameCommon varchar(50) default NULL,
> > Rank smallint(2) default 0
> > PRIMARY KEY (N)
> > ) ENGINE=MyISAM
>
> That can indeed be solved using a hierarchical query (provided you have a
> suitable table in PG); something akin to:
>
> WITH RECURSIVE taxons AS (
> --  Hierarchical root nodes
> SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A
> useful addition explained further down
> FROM t
> WHERE ParentID IS NULL
>
> -- Child nodes
> UNION ALL
> SELECT  N AS id, Taxon, Rank, taxons.level +1 AS level,
> taxons.Path || ':' || N AS Path
> FROM taxons
> JOIN t ON taxons.id = t.ParentID
> )
> SELECT id, Taxon, Rank, level
> FROM taxons
> ORDER BY Path
> ;
>
> The Path-bit looks complicated, but basically that just appends ID's
> within the same hierarchy such that, when sorted on that field, you get the
> hierarchy in their hierarchical order. What the hierarchy would look like
> if it were shown as a file hierarchy with sub-directories expanded, for
> example. That's pretty much the only viable alternative (alternatives vary
> on the column used to create the hierarchy), which is why I added it to the
> example.
>
> The fun thing with hierarchical queries is that you can add all kinds of
> extra information and make it trickle down to the child nodes, such as the
> items that make up the root of the hierarchy (pretty useful for grouping),
> for example or a field that calculates a string to prepend for indentation,
> etc. Or a computation that depends on values in parent items (I used this
> successfully in a bill of materials to calculate absolute quantities by
> volume, quantities by weight and cost of components in the end product
> where they were given relative to 1 kg of their parent, for example).
>
> It's highly flexible and powerful (and standard SQL), but it takes a bit
> of time to get in the right mindset.
>
> PS. I usually write my hierarchical queries in Oracle, which isn't quite
> as good at them as Postgres is, but it's what we have @work. Hence, I'm not
> sure I got the syntax 100% correct. We're working on getting PG in for a
> project upgrade (replacing RDB on OpenVMS, which will go EOL in <10 years!)
> - fingers crossed.
>
> Cheers!
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>


-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Duplicate rows during pg_dump

[Jim Nasby]

On 10/24/15 3:15 PM, Marc Mamin wrote:

Any suggestions for what to look for next? Is it table corruption?

Most likely is the index corrupt, not the table.
You should check for further duplicates, fix them and as Adrian writes,
build a new index an then drop the corrupt one.

I've seen this a few times before, and if I recall well it was always after 
some plate got full.
Is AWS getting out of space:)


You should report this to the RDS team, because an out of space 
condition shouldn't leave multiple values in the index. I suspect 
they've made a modification somewhere that is causing this. It could be 
a base Postgres bug, but I'd think we'd have caught such a bug by now...

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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] partial JOIN (was: ID column naming convention)

[Jim Nasby]

On 10/25/15 2:30 AM, Rafal Pietrak wrote:

So personally, I don't see a star in a select list so harmfull, quite
the oposit.


Using * outside the database is generally very dangerous. Using it 
*inside* the database can be very useful, because frequently it's 
exactly what you want (give me all the columns, dammit!). In particular, 
I find it useful in views where I want the original data, along with 
some calculated or other values. For example,


SELECT nspname, c.oid, * FROM pg_class c LEFT JOIN pg_namespace ON...

But there's also times I've wanted a way to manipulate what * would 
normally do. In particular, *_except_for(field_list), and *_replace( 
regexp_replace to run on each field name). If those existed (and maybe a 
way to combine them), it wouldn't be terribly hard for you do handle 
your sled query with something like:


SELECT s.*, l.*( s/.*/left_%/ ), r.*( s/.*/right_& )
  FROM sled s JOIN runner l ON ... JOIN runner r ON ...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] I'm starting a PostgreSQL user's group in Pittsburgh

[Bill Moran]

I'm going to see if I can drum up enough interest for a PostgreSQL
user's group in the Pittsburgh area.

After talking to the organizers of the Philadelphia PUG, I decided
to try using Meetup to coordinate things:
http://www.meetup.com/Pittsburgh-PostgreSQL-Users-Group/

If you're in the Pittsburgh area and would like to get involved,
please show your interest by joining the meetup. I'll get a first
event scheduled as soon as we have enough people signed up to make
it interesting.

If you haven't used meetup before: it's a service specifically for
coordinating things like user's groups, and it does a pretty good
job of letting us coordinate activities. Basic membership on the
site is free and includes participating in as many groups as you
desire. (it only costs something if you want to host your own group).

Hope to see you soon.

-- 
Bill Moran


-- 
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] Recursive Arrays 101

[Alban Hertroys]

> On 25 Oct 2015, at 19:38, Adrian Klaver  wrote:
> 
> On 10/25/2015 11:12 AM, David Blomstrom wrote:
>> I'm sorry, I don't know exactly what you mean by "definitions." The
>> fields Taxon and Parent are both varchar, with a 50-character limit.
>> ParentID is int(1).
> 
> By definition I meant the schema, so from the below:
> 
> CREATE TABLE t (
> N INT(6) default None auto_increment,
> Taxon varchar(50) default NULL,
> Parent varchar(25) default NULL,
> NameCommon varchar(50) default NULL,
> Rank smallint(2) default 0
> PRIMARY KEY (N)
> ) ENGINE=MyISAM

That can indeed be solved using a hierarchical query (provided you have a 
suitable table in PG); something akin to:

WITH RECURSIVE taxons AS (
--  Hierarchical root nodes
SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful 
addition explained further down
FROM t
WHERE ParentID IS NULL

-- Child nodes
UNION ALL
SELECT  N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || 
':' || N AS Path
FROM taxons
JOIN t ON taxons.id = t.ParentID
)
SELECT id, Taxon, Rank, level
FROM taxons
ORDER BY Path
;

The Path-bit looks complicated, but basically that just appends ID's within the 
same hierarchy such that, when sorted on that field, you get the hierarchy in 
their hierarchical order. What the hierarchy would look like if it were shown 
as a file hierarchy with sub-directories expanded, for example. That's pretty 
much the only viable alternative (alternatives vary on the column used to 
create the hierarchy), which is why I added it to the example.

The fun thing with hierarchical queries is that you can add all kinds of extra 
information and make it trickle down to the child nodes, such as the items that 
make up the root of the hierarchy (pretty useful for grouping), for example or 
a field that calculates a string to prepend for indentation, etc. Or a 
computation that depends on values in parent items (I used this successfully in 
a bill of materials to calculate absolute quantities by volume, quantities by 
weight and cost of components in the end product where they were given relative 
to 1 kg of their parent, for example).

It's highly flexible and powerful (and standard SQL), but it takes a bit of 
time to get in the right mindset.

PS. I usually write my hierarchical queries in Oracle, which isn't quite as 
good at them as Postgres is, but it's what we have @work. Hence, I'm not sure I 
got the syntax 100% correct. We're working on getting PG in for a project 
upgrade (replacing RDB on OpenVMS, which will go EOL in <10 years!) - fingers 
crossed.

Cheers!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
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] Recursive Arrays 101

[Adrian Klaver]

On 10/25/2015 11:12 AM, David Blomstrom wrote:

I'm sorry, I don't know exactly what you mean by "definitions." The
fields Taxon and Parent are both varchar, with a 50-character limit.
ParentID is int(1).


By definition I meant the schema, so from the below:

CREATE TABLE t (
 N INT(6) default None auto_increment,
 Taxon varchar(50) default NULL,
 Parent varchar(25) default NULL,
 NameCommon varchar(50) default NULL,
 Rank smallint(2) default 0
 PRIMARY KEY (N)
) ENGINE=MyISAM



Here's a discussion that describes the table in a little more detail --
http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii

And this is the discussion where someone suggested I check out
PostgreSQL --
http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure



Seems to me it would be easier to use what already exists:

Kingdom, Phylum, Class, Order, Family, Genus, and Species.

So.

Kingdom table <--> Phylum table <--> Class table <-->, on down the line.

Where the tables are linked by Foreign Keys(something not possible with 
MyISAM).


See:

http://www.postgresql.org/docs/9.5/static/sql-createtable.html

"REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE 
action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn 
[, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action 
] (table constraint)"


--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Recursive Arrays 101

[David Blomstrom]

I'm sorry, I don't know exactly what you mean by "definitions." The fields
Taxon and Parent are both varchar, with a 50-character limit. ParentID is
int(1).

Here's a discussion that describes the table in a little more detail --
http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii

And this is the discussion where someone suggested I check out PostgreSQL
-- 
http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure

On Sun, Oct 25, 2015 at 10:59 AM, Adrian Klaver 
wrote:

> On 10/25/2015 08:48 AM, David Blomstrom wrote:
>
>> I'm creating a website focusing on living things (mostly animals). I
>> have multiple huge MySQL database tables with animal taxons arranged in
>> a parent-child relationship. I was trying to figure out how I could
>> navigate to a URL like MySite/life/mammals and display the number of
>> children (i.e. orders), grandchildren (families), great grandchildren
>> (genera) and great great grand children (species).
>>
>> I was then steered towards some sort of MySQL substitute for a full
>> outer join (which can apparently only be done in Postgre), followed by
>> an introduction to stored procedures. Pretty complicated stuff.
>>
>> Then someone told me it's stupid to jump through all those hoops when
>> you can easily do that sort of thing with Postgre.
>>
>> So that's my specific goal - to set up my animals website so it can
>> quickly and efficiently calculate and display things like grandchildren,
>> great grandparents, the number of children that are extinct, etc.
>>
>> My database tables look something like this, where Taxon, Parent and
>> ParentID are the names of the key fields:
>>
>> Taxon | Parent | ParentID
>> Animalia | Life | (NULL)
>> Chordata | Animalia | (NULL)
>> Animalia | Chordata | 0
>> Mammalia | Animalia | 1
>> Carnivora | Mammalia | 2
>> Felidae | Carnivora | 3
>> Panthera | Felidae | 2
>> Panthera-leo | Panthera | 1
>> Panthera-tirgis | Panthera | 1
>>
>
> I am not entirely following the above. Could you post the actual table
> definitions?
>
>
>
>> Is that table structure sufficient for PostgreSQL to calculate
>> grand-children, etc., or will I have to modify it? I think the key words
>> are "hierarchical query" and/or "nested set." There's a popular tutorial
>> (though I can't find it at the moment) that illustrates the procedure,
>> which involves creating TWO numerical fields - a process that I think
>> would be overwhelming when working with over 50,000 taxonomic names.
>>
>> So that's my question; can I do all this recursive stuff in Postgre with
>> the table structure posted above, or will I still have to add a second
>> numerical column (or otherwise my table)?
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

On 10/25/2015 08:48 AM, David Blomstrom wrote:

I'm creating a website focusing on living things (mostly animals). I
have multiple huge MySQL database tables with animal taxons arranged in
a parent-child relationship. I was trying to figure out how I could
navigate to a URL like MySite/life/mammals and display the number of
children (i.e. orders), grandchildren (families), great grandchildren
(genera) and great great grand children (species).

I was then steered towards some sort of MySQL substitute for a full
outer join (which can apparently only be done in Postgre), followed by
an introduction to stored procedures. Pretty complicated stuff.

Then someone told me it's stupid to jump through all those hoops when
you can easily do that sort of thing with Postgre.

So that's my specific goal - to set up my animals website so it can
quickly and efficiently calculate and display things like grandchildren,
great grandparents, the number of children that are extinct, etc.

My database tables look something like this, where Taxon, Parent and
ParentID are the names of the key fields:

Taxon | Parent | ParentID
Animalia | Life | (NULL)
Chordata | Animalia | (NULL)
Animalia | Chordata | 0
Mammalia | Animalia | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 2
Panthera-leo | Panthera | 1
Panthera-tirgis | Panthera | 1


I am not entirely following the above. Could you post the actual table 
definitions?




Is that table structure sufficient for PostgreSQL to calculate
grand-children, etc., or will I have to modify it? I think the key words
are "hierarchical query" and/or "nested set." There's a popular tutorial
(though I can't find it at the moment) that illustrates the procedure,
which involves creating TWO numerical fields - a process that I think
would be overwhelming when working with over 50,000 taxonomic names.

So that's my question; can I do all this recursive stuff in Postgre with
the table structure posted above, or will I still have to add a second
numerical column (or otherwise my table)?






--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Using PostgreSQL with MySQL

[David Blomstrom]

Oh my God, Whatcom County. I went to school in Bellingham years ago, when
the population was just 50,000. It was such a beautiful place, I'd be
afraid to go back. ;)

Thanks for the tips.

On Sun, Oct 25, 2015 at 10:46 AM, Adrian Klaver 
wrote:

> On 10/25/2015 09:37 AM, David Blomstrom wrote:
>
>> I've already learned that I can install and use MySQL and PostgreSQL
>> simultaneously on my laptop, though I haven't yet learned how to connect
>> to my Postgre database from a web page, write a query, etc. So here's
>> what I'm planning...
>>
>
> FYI, the short version of the name is Postgres.
>
>
>> I have several big, complex websites driven by a MySQL database. I'd
>> like to install PostgreSQL online and use it alongside MySQL in a
>> limited way, until I have time to learn it more fully and upgrade my
>> database tables and queries. Are there any major snags or pitfalls you
>> can see in such an arrangement?
>>
>
> Well you could go the FDW(Foreign Data Wrapper) route:
>
> https://wiki.postgresql.org/wiki/Fdw#Foreign_Data_Wrappers
>
> Though that is a later chapter thing.
>
>
>> I work night shift and am going to be overwhelmed during the holiday
>> season. I'm hoping to get a major website(s) upgrade published before
>> Thanksgiving. So I'm hoping to hire someone to help me get up to speed -
>> create one table the proper way, make a connection and query, then
>> recreate everything online.
>>
>> Do you think there are people in the community who'd be willing to do
>> something like that for $100? I don't think it would take more than an
>> hour.
>>
>> I've found a Seattle PostgreSQL Users Group, but there's no contact
>> information, and they only meet once a month - at night. So I can't make
>> their meetings, unless I slip in for the first half hour or so.
>>
>
> Well if you find yourself heading North you might see what is on the
> docket at Whatcom PostgreSQL(http://www.meetup.com/Whatcom-PostgreSQL/).
> We meet at night also so it may not work, but it is another source of local
> contacts.
>
>
>> Anyway, I just wondered what kind of resources are available for this
>> sort of thing.
>>
>
> This mailing list is probably the best place to start. Ask a question,
> there will someone that has done it or has an opinion on how to do it:)
>
>
>> Thanks.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Using PostgreSQL with MySQL

[Adrian Klaver]

On 10/25/2015 09:37 AM, David Blomstrom wrote:

I've already learned that I can install and use MySQL and PostgreSQL
simultaneously on my laptop, though I haven't yet learned how to connect
to my Postgre database from a web page, write a query, etc. So here's
what I'm planning...


FYI, the short version of the name is Postgres.



I have several big, complex websites driven by a MySQL database. I'd
like to install PostgreSQL online and use it alongside MySQL in a
limited way, until I have time to learn it more fully and upgrade my
database tables and queries. Are there any major snags or pitfalls you
can see in such an arrangement?


Well you could go the FDW(Foreign Data Wrapper) route:

https://wiki.postgresql.org/wiki/Fdw#Foreign_Data_Wrappers

Though that is a later chapter thing.



I work night shift and am going to be overwhelmed during the holiday
season. I'm hoping to get a major website(s) upgrade published before
Thanksgiving. So I'm hoping to hire someone to help me get up to speed -
create one table the proper way, make a connection and query, then
recreate everything online.

Do you think there are people in the community who'd be willing to do
something like that for $100? I don't think it would take more than an hour.

I've found a Seattle PostgreSQL Users Group, but there's no contact
information, and they only meet once a month - at night. So I can't make
their meetings, unless I slip in for the first half hour or so.


Well if you find yourself heading North you might see what is on the 
docket at Whatcom PostgreSQL(http://www.meetup.com/Whatcom-PostgreSQL/). 
We meet at night also so it may not work, but it is another source of 
local contacts.




Anyway, I just wondered what kind of resources are available for this
sort of thing.


This mailing list is probably the best place to start. Ask a question, 
there will someone that has done it or has an opinion on how to do it:)




Thanks.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using PostgreSQL with MySQL

[David Blomstrom]

I've already learned that I can install and use MySQL and PostgreSQL
simultaneously on my laptop, though I haven't yet learned how to connect to
my Postgre database from a web page, write a query, etc. So here's what I'm
planning...

I have several big, complex websites driven by a MySQL database. I'd like
to install PostgreSQL online and use it alongside MySQL in a limited way,
until I have time to learn it more fully and upgrade my database tables and
queries. Are there any major snags or pitfalls you can see in such an
arrangement?

I work night shift and am going to be overwhelmed during the holiday
season. I'm hoping to get a major website(s) upgrade published before
Thanksgiving. So I'm hoping to hire someone to help me get up to speed -
create one table the proper way, make a connection and query, then recreate
everything online.

Do you think there are people in the community who'd be willing to do
something like that for $100? I don't think it would take more than an hour.

I've found a Seattle PostgreSQL Users Group, but there's no contact
information, and they only meet once a month - at night. So I can't make
their meetings, unless I slip in for the first half hour or so.

Anyway, I just wondered what kind of resources are available for this sort
of thing.

Thanks.

Re: [GENERAL] Where do I enter commands?

[David Blomstrom]

No problem. I'm pretty grumpy when people invade MY turf - biology and
education reform. As a former Seattle teacher who spent sixteen years in
the meat grinder before becoming a whistle-blower, I've seen it all - and I
know it all. ;)

On Sun, Oct 25, 2015 at 8:41 AM, Karsten Hilbert 
wrote:

> On Sun, Oct 25, 2015 at 08:32:43AM -0700, David Blomstrom wrote:
>
> > Someone said when you add a new column in Postgre, it's appended to the
> end
> > of the table. Does that mean that's where it has to stay, or can you
> > rearrange columns
>
> No, unless you drop/re-create the table (manually or with
>
> pg_dump -t 
>
> But you can use a view over the table to show a desired
> column order if you so wish.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

[GENERAL] Recursive Arrays 101

[David Blomstrom]

I'm creating a website focusing on living things (mostly animals). I have
multiple huge MySQL database tables with animal taxons arranged in a
parent-child relationship. I was trying to figure out how I could navigate
to a URL like MySite/life/mammals and display the number of children (i.e.
orders), grandchildren (families), great grandchildren (genera) and great
great grand children (species).

I was then steered towards some sort of MySQL substitute for a full outer
join (which can apparently only be done in Postgre), followed by an
introduction to stored procedures. Pretty complicated stuff.

Then someone told me it's stupid to jump through all those hoops when you
can easily do that sort of thing with Postgre.

So that's my specific goal - to set up my animals website so it can quickly
and efficiently calculate and display things like grandchildren, great
grandparents, the number of children that are extinct, etc.

My database tables look something like this, where Taxon, Parent and
ParentID are the names of the key fields:

Taxon | Parent | ParentID
Animalia | Life | (NULL)
Chordata | Animalia | (NULL)
Animalia | Chordata | 0
Mammalia | Animalia | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 2
Panthera-leo | Panthera | 1
Panthera-tirgis | Panthera | 1

Is that table structure sufficient for PostgreSQL to calculate
grand-children, etc., or will I have to modify it? I think the key words
are "hierarchical query" and/or "nested set." There's a popular tutorial
(though I can't find it at the moment) that illustrates the procedure,
which involves creating TWO numerical fields - a process that I think would
be overwhelming when working with over 50,000 taxonomic names.

So that's my question; can I do all this recursive stuff in Postgre with
the table structure posted above, or will I still have to add a second
numerical column (or otherwise my table)?

Re: [GENERAL] Where do I enter commands?

[Karsten Hilbert]

On Sun, Oct 25, 2015 at 08:32:43AM -0700, David Blomstrom wrote:

> Someone said when you add a new column in Postgre, it's appended to the end
> of the table. Does that mean that's where it has to stay, or can you
> rearrange columns

No, unless you drop/re-create the table (manually or with

pg_dump -t 

But you can use a view over the table to show a desired
column order if you so wish.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Where do I enter commands?

[Rob Sargent]

> On Oct 25, 2015, at 3:21 AM, Joshua D. Drake  wrote:
> 
> I would ignore Rob, he obviously is suffering from a lack of coffee. Our 
> community always tries to help new users. It is great to see you here.
> 
> Sincerely,
> 
> JD
> 

Always the best advice :)

OK, Coffee’d up now.  Apologies for my grumpiness. The rest of the thread says 
it much more nicely.




-- 
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] Where do I enter commands?

[David Blomstrom]

Wow, great tips; you answered a question I was about to post either here or
on a forum. One question, though.

Someone said when you add a new column in Postgre, it's appended to the end
of the table. Does that mean that's where it has to stay, or can you
rearrange columns, as in MySQL?

On Sun, Oct 25, 2015 at 8:14 AM, Alexander Reichstadt 
wrote:

> Hello David,
>
> This cookbook has worked for me for the last five years on Mac OS X,
> always and totally reliable up to incl. El Capitan and every intermittent
> release before. And most of all, it worked even as a repair measure. So if
> my server would stop working after a small Mac Os X update, I took these
> steps, and without migrations it just worked again then:
>
> 1. google
>
> enterprise Postgres installer
>
> 2. hit the first link, follow your intuition on where you get until you
> download the binary for the platform you want
> 3. install postgres and then launch package maker
> 4. in package maker, you can scroll down and navigate to the category web
> development, and there select phpPgAdmin with apache
> 5. run the installation, it is going to install a special instance of
> apache only for phpPgAdmin
> 6. it will guide you through to the end including to select a port and
> website to connect to, just hit return 'til the whole things shuts up and
> gives you the summary page
> 7. make a screenshot for your files, log on trough a web browser, you will
> see, it explains itself
>
> Note 1: depending on where you want to allow logons from to your page,
> this is another chapter, but actually quite straight forward.
>
> Note 2: Given you are in the middle of another approach, this might fail
> now. Regardless of any prior data you have, disregard it until here. Once
> the server is running, you have the next piece to cover and it is normally
> quite easy to do then.
>
> I did also both, Mysql and Postgres, and even though it seemed a little
> less intuitive at first, and even though I find a waste of time in any
> matter aggravating, there is a host of reasons it was a very good and time
> saving decision in the long run to have switched and thus gained access to
> tons of features mysql does not offer, not to mention the license giving
> you freedom mysql is never going to be able to offer.
>
> Finally of that, I have rarely found an open source community as
> supportive and responsive as the postgres community, you can measure that
> against premium service of some providers who charge you money for it. Hang
> in there for the start, it is going to be rewarding.
>
> Hope this helps your decision process
> Alex
>
>
> > On 25 Oct 2015, at 1:28 p.m., Adrian Klaver 
> wrote:
> >
> > On 10/24/2015 09:19 PM, David Blomstrom wrote:
> >> I'm a writer. I studied programing and MySQL so I could create websites
> >> that I can publish my articles to. I don't have time to keep up with the
> >> endless technology - MySQL, PDO, stored procedures, PHP, JavaScript,
> >> JQuery, and on and on - especially when I have to work for a living.
> >> I've been using MySQL for years, so I'm familiar with it. It therefore
> >> makes sense for me to find a GUI as similar to MySQL as possible.
> >>
> >> With phpMyAdmin, I can easily create, modify, copy and migrate tables
> >> between databases. If that can be done as easily with a
> >> command-line-tool, even after surviving the learning curve, then I'm
> >> interested. But it's really hard to imagine how that could be.
> >
> > pgAdmin will allow you to do those things. phpPgAdmin also, though I
> have never used it, so I can not be of much help there. The predominate
> command line tool folks are referring to is psql:
> >
> > http://www.postgresql.org/docs/9.4/interactive/app-psql.html
> >
> > For dumping databases or their contained objects there is pg_dump:
> >
> > http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
> >
> > for restoring non-plain text dumps there is pg_restore
> >
> > http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
> >
> > for plain text dumps just use psql.
> >
> > These three programs will cover most of your use cases. The benefit to
> using these tools is that you end of working with scripts that then can be
> put under version control. Takes a little bit of time to set up but the
> payoff is worth it for anything above the really simple level.
> >
> >>
> >> Thanks for the tips.
> >>
> >> On Sat, Oct 24, 2015 at 9:07 PM, Adrian Klaver
> >> mailto:adrian.kla...@aklaver.com>> wrote:
> >>
> >>On 10/24/2015 08:52 PM, Rob Sargent wrote:
> >>
> >>ok. now who has the url to the pithy
> >>heres-why-you-/really/-want-the-command-line.
> >>
> >>It distills to something about actually knowing what you’re
> doing.
> >>
> >>
> >>Everyone has to start somewhere. The point is get someone using
> >>Postgres in manner they are comfortable with, then they can start
> >>exploring the possibilities. I personally find the command line more
> >> 

Re: [GENERAL] Where do I enter commands?

[Adrian Klaver]

On 10/25/2015 07:57 AM, David Blomstrom wrote:

It's hard to imagine creating a table with a command-line tool - in the
step-by-step process I use with phpMyAdmin, that is. If you can learn
the proper syntax for creating a table and put together a script for a
generic table that you can easily modify, then maybe it would be a lot
easier with a command-line tool.

In phpMyAdmin, I've become accustomed to simply copying existing tables,
then adding, deleting and renaming columns as needed.


In psql:

test=> CREATE TABLE orig_test(id integer, fld_1 varchar, fld_2 boolean, 
fld_3 numeric(7,3));

CREATE TABLE

test=> \d orig_test
Table "public.orig_test"
Column | Type | Modifiers
+---+---
id | integer |
fld_1 | character varying |
fld_2 | boolean |
fld_3 | numeric(7,3) |

test=> create table cp_test AS select * from orig_test ;
SELECT 0

test=> \d cp_test
Table "public.cp_test"
Column | Type | Modifiers
+---+---
id | integer |
fld_1 | character varying |
fld_2 | boolean |
fld_3 | numeric(7,3) |

test=> alter table cp_test add column fld_4 date;
ALTER TABLE
test=> \d cp_test
 Table "public.cp_test"
 Column |   Type| Modifiers
+---+---
 id | integer   |
 fld_1  | character varying |
 fld_2  | boolean   |
 fld_3  | numeric(7,3)  |
 fld_4  | date  |

One note, in Postgres new columns will always be added to end of table.



I can see PostgreSQL is going to have a learning curve - hopefully
shorter than the years it took me to learn MySQL - but it looks
interesting. The community seems painfully small compared to MySQL, and
there are less online resources. But I'm guessing that will change in
the coming years. I remember when CSS was a strange, foreign thing. ;)


Last time I there was a count on the people on this mailing list I 
remember a number of 33,000-34,000.




On Sun, Oct 25, 2015 at 6:28 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/24/2015 09:19 PM, David Blomstrom wrote:

I'm a writer. I studied programing and MySQL so I could create
websites
that I can publish my articles to. I don't have time to keep up
with the
endless technology - MySQL, PDO, stored procedures, PHP, JavaScript,
JQuery, and on and on - especially when I have to work for a living.
I've been using MySQL for years, so I'm familiar with it. It
therefore
makes sense for me to find a GUI as similar to MySQL as possible.

With phpMyAdmin, I can easily create, modify, copy and migrate
tables
between databases. If that can be done as easily with a
command-line-tool, even after surviving the learning curve, then I'm
interested. But it's really hard to imagine how that could be.


pgAdmin will allow you to do those things. phpPgAdmin also, though I
have never used it, so I can not be of much help there. The
predominate command line tool folks are referring to is psql:

http://www.postgresql.org/docs/9.4/interactive/app-psql.html

For dumping databases or their contained objects there is pg_dump:

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

for restoring non-plain text dumps there is pg_restore

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

for plain text dumps just use psql.

These three programs will cover most of your use cases. The benefit
to using these tools is that you end of working with scripts that
then can be put under version control. Takes a little bit of time to
set up but the payoff is worth it for anything above the really
simple level.


Thanks for the tips.

On Sat, Oct 24, 2015 at 9:07 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>> wrote:

 On 10/24/2015 08:52 PM, Rob Sargent wrote:

 ok. now who has the url to the pithy
 heres-why-you-/really/-want-the-command-line.

 It distills to something about actually knowing what
you’re doing.


 Everyone has to start somewhere. The point is get someone using
 Postgres in manner they are comfortable with, then they can
start
 exploring the possibilities. I personally find the command
line more
 productive, but there is a learning curve.





 --
 Adrian Klaver
adrian.kla...@aklaver.com 
>




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org  



--
Adrian Klaver
adrian.kla...@aklaver.com 

Re: [GENERAL] Where do I enter commands?

[Andrew Sullivan]

On Sun, Oct 25, 2015 at 07:57:48AM -0700, David Blomstrom wrote:
> In phpMyAdmin, I've become accustomed to simply copying existing tables,
> then adding, deleting and renaming columns as needed.

Oh!  Interesting.  I suspect you're actually _undermining_ your
ability to use the database (because often when you copy a table it's
a sign that you're using the database like a spreadsheet, and you're
giving up a lot of functionality that way).

But, suppose you're wanting to do that, then here's the easy way to do
it:

--- if you want the same table structure without the data

CREATE TABLE newtable AS SELECT * FROM oldtable WHERE 1=0;

-- if you want the same table with some data

CREATE TABLE newtable AS SELECT columns, you, want, in, order
FROM oldtable
[WHERE conditions];

If you want only some columns or new order or something, the WHERE
clause in the latter statement should be 1=0.  It makes a null set
always.  Handy trick.

> I can see PostgreSQL is going to have a learning curve - hopefully shorter
> than the years it took me to learn MySQL - but it looks interesting.

It should be much easier.  You have the basics from MySQL already.
Consistency and rigour are the changes ;-)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] Where do I enter commands?

[David Blomstrom]

It's hard to imagine creating a table with a command-line tool - in the
step-by-step process I use with phpMyAdmin, that is. If you can learn the
proper syntax for creating a table and put together a script for a generic
table that you can easily modify, then maybe it would be a lot easier with
a command-line tool.

In phpMyAdmin, I've become accustomed to simply copying existing tables,
then adding, deleting and renaming columns as needed.

I can see PostgreSQL is going to have a learning curve - hopefully shorter
than the years it took me to learn MySQL - but it looks interesting. The
community seems painfully small compared to MySQL, and there are less
online resources. But I'm guessing that will change in the coming years. I
remember when CSS was a strange, foreign thing. ;)

On Sun, Oct 25, 2015 at 6:28 AM, Adrian Klaver 
wrote:

> On 10/24/2015 09:19 PM, David Blomstrom wrote:
>
>> I'm a writer. I studied programing and MySQL so I could create websites
>> that I can publish my articles to. I don't have time to keep up with the
>> endless technology - MySQL, PDO, stored procedures, PHP, JavaScript,
>> JQuery, and on and on - especially when I have to work for a living.
>> I've been using MySQL for years, so I'm familiar with it. It therefore
>> makes sense for me to find a GUI as similar to MySQL as possible.
>>
>> With phpMyAdmin, I can easily create, modify, copy and migrate tables
>> between databases. If that can be done as easily with a
>> command-line-tool, even after surviving the learning curve, then I'm
>> interested. But it's really hard to imagine how that could be.
>>
>
> pgAdmin will allow you to do those things. phpPgAdmin also, though I have
> never used it, so I can not be of much help there. The predominate command
> line tool folks are referring to is psql:
>
> http://www.postgresql.org/docs/9.4/interactive/app-psql.html
>
> For dumping databases or their contained objects there is pg_dump:
>
> http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
>
> for restoring non-plain text dumps there is pg_restore
>
> http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
>
> for plain text dumps just use psql.
>
> These three programs will cover most of your use cases. The benefit to
> using these tools is that you end of working with scripts that then can be
> put under version control. Takes a little bit of time to set up but the
> payoff is worth it for anything above the really simple level.
>
>
>> Thanks for the tips.
>>
>> On Sat, Oct 24, 2015 at 9:07 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/24/2015 08:52 PM, Rob Sargent wrote:
>>
>> ok. now who has the url to the pithy
>> heres-why-you-/really/-want-the-command-line.
>>
>> It distills to something about actually knowing what you’re doing.
>>
>>
>> Everyone has to start somewhere. The point is get someone using
>> Postgres in manner they are comfortable with, then they can start
>> exploring the possibilities. I personally find the command line more
>> productive, but there is a learning curve.
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>>
>> --
>> David Blomstrom
>> Writer & Web Designer (Mac, M$ & Linux)
>> www.geobop.org 
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Re: [GENERAL] Where do I enter commands?

[Adrian Klaver]

On 10/24/2015 09:19 PM, David Blomstrom wrote:

I'm a writer. I studied programing and MySQL so I could create websites
that I can publish my articles to. I don't have time to keep up with the
endless technology - MySQL, PDO, stored procedures, PHP, JavaScript,
JQuery, and on and on - especially when I have to work for a living.
I've been using MySQL for years, so I'm familiar with it. It therefore
makes sense for me to find a GUI as similar to MySQL as possible.

With phpMyAdmin, I can easily create, modify, copy and migrate tables
between databases. If that can be done as easily with a
command-line-tool, even after surviving the learning curve, then I'm
interested. But it's really hard to imagine how that could be.


pgAdmin will allow you to do those things. phpPgAdmin also, though I 
have never used it, so I can not be of much help there. The predominate 
command line tool folks are referring to is psql:


http://www.postgresql.org/docs/9.4/interactive/app-psql.html

For dumping databases or their contained objects there is pg_dump:

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

for restoring non-plain text dumps there is pg_restore

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

for plain text dumps just use psql.

These three programs will cover most of your use cases. The benefit to 
using these tools is that you end of working with scripts that then can 
be put under version control. Takes a little bit of time to set up but 
the payoff is worth it for anything above the really simple level.




Thanks for the tips.

On Sat, Oct 24, 2015 at 9:07 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/24/2015 08:52 PM, Rob Sargent wrote:

ok. now who has the url to the pithy
heres-why-you-/really/-want-the-command-line.

It distills to something about actually knowing what you’re doing.


Everyone has to start somewhere. The point is get someone using
Postgres in manner they are comfortable with, then they can start
exploring the possibilities. I personally find the command line more
productive, but there is a learning curve.





--
Adrian Klaver
adrian.kla...@aklaver.com 




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Where do I enter commands?

[Melvin Davidson]

Since you are just starting, you should probably familarize yourself with
how PgAdmin works with PostgreSQL.
Therefore, it is best you refer to the documentation for PgAdmin.

PgAdmin III
http://www.pgadmin.org/docs/1.20/index.html

I also suggest you obtain a copy of one, or both, of the following two
books to help you with PostgreSQL.

Beginning Databases with PostgreSQL: From Novice to Professional
http://www.amazon.com/gp/product/1590594789?keywords=postgresql&qid=1445778326&ref_=sr_1_3&s=books&sr=1-3

PostgreSQL Administration Essentials
http://www.amazon.com/gp/product/1783988983?keywords=postgresql&qid=1445778326&ref_=sr_1_7&s=books&sr=1-7

You can also find a very extensive listing of other books about PostgreSQL
here:

http://www.postgresql.org/docs/books/

On Sun, Oct 25, 2015 at 5:21 AM, Joshua D. Drake 
wrote:

> On 10/24/2015 09:19 PM, David Blomstrom wrote:
>
>> I'm a writer. I studied programing and MySQL so I could create websites
>> that I can publish my articles to. I don't have time to keep up with the
>> endless technology - MySQL, PDO, stored procedures, PHP, JavaScript,
>> JQuery, and on and on - especially when I have to work for a living.
>> I've been using MySQL for years, so I'm familiar with it. It therefore
>> makes sense for me to find a GUI as similar to MySQL as possible.
>>
>> With phpMyAdmin, I can easily create, modify, copy and migrate tables
>> between databases. If that can be done as easily with a
>> command-line-tool, even after surviving the learning curve, then I'm
>> interested. But it's really hard to imagine how that could be.
>>
>> Thanks for the tips.
>>
>
> I would ignore Rob, he obviously is suffering from a lack of coffee. Our
> community always tries to help new users. It is great to see you here.
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Where do I enter commands?

[Joshua D. Drake]

On 10/24/2015 09:19 PM, David Blomstrom wrote:

I'm a writer. I studied programing and MySQL so I could create websites
that I can publish my articles to. I don't have time to keep up with the
endless technology - MySQL, PDO, stored procedures, PHP, JavaScript,
JQuery, and on and on - especially when I have to work for a living.
I've been using MySQL for years, so I'm familiar with it. It therefore
makes sense for me to find a GUI as similar to MySQL as possible.

With phpMyAdmin, I can easily create, modify, copy and migrate tables
between databases. If that can be done as easily with a
command-line-tool, even after surviving the learning curve, then I'm
interested. But it's really hard to imagine how that could be.

Thanks for the tips.


I would ignore Rob, he obviously is suffering from a lack of coffee. Our 
community always tries to help new users. It is great to see you here.


Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] partial JOIN (was: ID column naming convention)

[Rafal Pietrak]

W dniu 24.10.2015 o 23:25, Dane Foster pisze:
> 
> On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster  > wrote:
> 

[]
> --
> Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> ​
> You may be able to accomplish that using aliased sub-selects as
> in-line views. The purpose of the sub-selects in this use-case is
> simply to cherry pick the columns you want.
> SELECT *
> FROM
>   (SELECT col1, col2, col4 FROM tablea) AS iv
>   JOIN (SELECT co1, col3, col5 FROM tableb) AS tb1 USING (col1))
>   JOIN (SELECT col1, col6, col7 FROM tableb) AS tb22 USING (col1)
> 
> Please note, this may be a performance nightmare for large tables
> because w/o a WHERE clause that can be pushed down to the
> sub-selects each sub-select will do a full table scan.

Yes. And that's why this is not truely an option. I'd rather give all
coluns aliases (when coding), then opt for subquery on every execute.

> 
> Please note that the 3rd JOIN clause is nutty (I translated it from
> your original) because why would you join a table to itself just to
> select a different set of columns?

One example (a bit artificial, I know) might be the address data, for
waybill:
create table purchases( basket int, customer int, delivery int, ...);
select * from purchases p join buyers b(customer, city, address) using
(customer) join buyers d (delivery, to_city, to_address, to_zip) using
(delivery);

... or something like that. ZIP code is not actually needed to indicate
customer (SSN might be instead).

But I wouldn't agrue if real life programming actually needs that. I've
just wanted to have the most generic example I've imagined.

> 
> Good luck,
> 
> Dane
> 
> ​
> For the record SELECT * in my example is absolutely the wrong thing to
> do but your original didn't leave me w/ any other option.
> 

Hmmm. I've seen people say that. I do keep that in mind, but frankly I
actually never had to avoid that to get my code working (and
maintained). I do that sometimes to limit the bandwidth necesery to
deliver the results, but not so often.

But I'd say, that "the standard" sort of does that (i.e. the star)
notoriusly:
1. with table aliasing (the case we are discussing now), standard
expects us to give column aliases *in order* they are defined within the
aliased table - that's nothing else but a "hidden star" somwhere there.
And I really wish they did it without that.
2. see the systax of INSERT: a list of column names to be prowided with
values is optional, and when you don't give it, it's like you've written
"a star" in its place. This I find *very bad* and never use it myself.
But standard people thought otherwise.

So personally, I don't see a star in a select list so harmfull, quite
the oposit.

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general