Re: [GENERAL] Recursive Arrays 101

On 11/05/2015 11:08 AM, Gavin Flower wrote: On 06/11/15 04:33, Rob Sargent wrote: On 11/05/2015 04:56 AM, Achilleas Mantzios wrote: On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!),

Re: [GENERAL] Recursive Arrays 101

On 06/11/15 04:33, Rob Sargent wrote: On 11/05/2015 04:56 AM, Achilleas Mantzios wrote: On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, wha

Re: [GENERAL] Recursive Arrays 101

On 11/05/2015 04:56 AM, Achilleas Mantzios wrote: On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for t

Re: [GENERAL] Recursive Arrays 101

On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for this problem for our app is do it this way : paren

Re: [GENERAL] Recursive Arrays 101

Thanks for that tip, Achilleas. On Wed, Nov 4, 2015 at 7:53 AM, Rob Sargent wrote: > On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: > > Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), > and not having read > most of the replies, what we have been successfully doing for

Re: [GENERAL] Recursive Arrays 101

On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for this problem for our app is do it this way : parents int[] -- where parents stores the path

Re: [GENERAL] Recursive Arrays 101

Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for this problem for our app is do it this way : parents int[] -- where parents stores the path from the node to the root of the tree and then ha

Re: [GENERAL] Recursive Arrays 101

On Monday, October 26, 2015, John R Pierce wrote: > On 10/26/2015 7:44 PM, David G. Johnston wrote: > >> ​They both have their places. It is usually quite difficult to automate >> and version control the manual work that goes into using command line >> tools.​ >> > > I hope you mean, its difficu

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 7:44 PM, David G. Johnston wrote: ​They both have their places. It is usually quite difficult to automate and version control the manual work that goes into using command line tools.​ I hope you mean, its difficult to automate and version control clickity-clicky work that goes

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 10:14 PM, Adrian Klaver wrote: > On 10/26/2015 06:48 PM, David Blomstrom wrote: > >> Doesn't this thread demonstrate the advantage of GUI's??? I created a >> database and table with pgAdmin III with no help at all. >> > > At this point I would say sticking with pgAdmin is

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 06:48 PM, David Blomstrom wrote: Doesn't this thread demonstrate the advantage of GUI's??? I created a database and table with pgAdmin III with no help at all. At this point I would say sticking with pgAdmin is the best option. I then got a flurry of well-intentioned tips about

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 06:08 PM, David Blomstrom wrote: Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then That is definitely not true. OS X under the hood comes from the same basic lineage as Linux, by way o

Re: [GENERAL] Recursive Arrays 101

> On Oct 26, 2015, at 7:48 PM, David Blomstrom > wrote: > > Doesn't this thread demonstrate the advantage of GUI's??? I created a > database and table with pgAdmin III with no help at all. > > I then got a flurry of well-intentioned tips about the command-line tool. I > tried all of them. I

Re: [GENERAL] Recursive Arrays 101

Doesn't this thread demonstrate the advantage of GUI's??? I created a database and table with pgAdmin III with no help at all. I then got a flurry of well-intentioned tips about the command-line tool. I tried all of them. I tried them twice. I tried them upside down and backwards. People then bega

Re: [GENERAL] Recursive Arrays 101

You will do what you need to do but please do not claim that pg is not Mac compatible. Many on this thread are regular Mac/pg users. We all thought we were doing you a favour in trying to free you from GUI tools. For us they are restraining. For you they may be liberating. Stick with pgAdmin and

Re: [GENERAL] Recursive Arrays 101

Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then that's definitely the end of the line. I used M$ for years and would never go back. I used to be a passionate Linux supporter - largely because I wanted

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 05:54 PM, David Blomstrom wrote: That's exactly what I've been doing. I just did it again... Hmm, maybe time to try Robs suggestion: " Cmd-Spacebar Terminal This should find the actual normal terminal. Click on it." Edited from his original post- at the prompt therein: psql --

Re: [GENERAL] Recursive Arrays 101

The law of O/S & databases: For every Linux / PostgreSQL user, there is and equal an opposite Mac / MySQL user. However, the latter is completely useless. On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom wrote: > That's exactly what I've been doing. I just did it again... > > Last login: Mon Oct

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom wrote: > That's exactly what I've been doing. I just did it again... > > Last login: Mon Oct 26 17:53:05 on ttys001 > > Davids-MacBook-Pro-2:~ davidblomstrom$ > /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit > > Server [localhost]: Server [localh

Re: [GENERAL] Recursive Arrays 101

That's exactly what I've been doing. I just did it again... Last login: Mon Oct 26 17:53:05 on ttys001 Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit Server [localhost]: Server [localhost Database [postgres]: Database [postgres]: Port [5432]: Port [5432

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 13:29, John R Pierce wrote: On 10/26/2015 5:20 PM, David G. Johnston wrote: What exactly are you showing us here? he's demonstrating a lack of reading comprehension. I'm done. thread on ignore. I think its proof that Apple products rot your brain! -- Sent via pgsql-gener

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 05:27 PM, David Blomstrom wrote: I'm just showing you what happened when I typed in \l and hit enter, then typed in \dt and hit enter, etc. To repeat: Do: Server [localhost]: Database [postgres]: Port [5432]: Username [postgres]: Just hit the Enter key after each of the abov

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 5:20 PM, David G. Johnston wrote: What exactly are you showing us here? he's demonstrating a lack of reading comprehension. I'm done. thread on ignore. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Recursive Arrays 101

I'm just showing you what happened when I typed in \l and hit enter, then typed in \dt and hit enter, etc. As Adrian Klaver said, this isn't going anywhere. The Seattle PostgreSQL User Group has its monthly meeting in a month or two. I work night shift, so I can't really make it, but if I can drop

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom wrote: > Server [localhost]: Server [localhost]: > > Database [postgres]: Database [postgres]: > > Port [5432]: Port [5432]: > > Username [postgres]: Username [postgres]: > What exactly are you showing us here? David J.

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 05:07 PM, David Blomstrom wrote: On Mon, Oct 26, 2015 at 5:02 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 10/26/2015 04:42 PM, David Blomstrom wrote: Last login: Mon Oct 26 16:35:25 on ttys002 /Library/PostgreSQL/9.5/scripts/runpsql.sh; ex

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 5:02 PM, Adrian Klaver wrote: > On 10/26/2015 04:42 PM, David Blomstrom wrote: > >> Last login: Mon Oct 26 16:35:25 on ttys002 >> >> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit >> >> Davids-MacBook-Pro-2:~ davidblomstrom$ >> /Library/PostgreSQL/9.5/scripts/runpsql.sh;

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 04:42 PM, David Blomstrom wrote: Last login: Mon Oct 26 16:35:25 on ttys002 /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit Server [localhost]: \l Database [postgres]: \dt Port [5432]: Use

Re: [GENERAL] Recursive Arrays 101

Last login: Mon Oct 26 16:35:25 on ttys002 /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit Davids-MacBook-Pro-2:~ davidblomstrom$ /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit Server [localhost]: \l Database [postgres]: \dt Port [5432]: Username [postgres]: Cmd-Spacebar Terminalpsql: cou

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 7:36 PM, David Blomstrom wrote: > I just deleted the PSQL icon from the dock, so I have to go into > Applications > PostgreSQL and open it. When I click on the PSQL icon, it > opens my Apple terminal, with the following text: > > Last login: Mon Oct 26 16:35:25 on ttys002

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 12:27, David Blomstrom wrote: LOL - This is precisely why I prefer GUI's. ;) I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin III) and a little monitor (PSQL). When I click on PSQL, it always opens at least two windows or instances. When I type things in and hit

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 05:28 PM, David Blomstrom wrote: No, I'm on a Mac running OS X El Capitan. I don't have my mac with me today so this is a little rough. Cmd-Spacebar Terminal This should find the actual normal terminal. Click on it. at the prompt therein: psql --username davdi --host localhos

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 04:21 PM, David Blomstrom wrote: On Mon, Oct 26, 2015 at 4:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 10/26/2015 04:13 PM, David Blomstrom wrote: Oops, let me try it again... Still not making sense. Show the exact command you are usi

Re: [GENERAL] Recursive Arrays 101

I just deleted the PSQL icon from the dock, so I have to go into Applications > PostgreSQL and open it. When I click on the PSQL icon, it opens my Apple terminal, with the following text: Last login: Mon Oct 26 16:35:25 on ttys002 /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit Davids-MacBook-P

Re: [GENERAL] Recursive Arrays 101

I thought PSQL seemed awfully similar to my Apple Terminal. I just noticed that when I click on the PSQL icon, it opens an Apple Terminal icon in the taskbar. I think that's why I'm getting different results - I'm typing into a PostgreSQL terminal and an Apple terminal. On Mon, Oct 26, 2015 at 4:3

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 04:26 PM, John R Pierce wrote: On 10/26/2015 4:22 PM, Gavin Flower wrote: By type of shell, is meant are using a bash shell in your terminal, or csh, or something else? Bash stands for BOurne Again Shell, it process commands like 'psql' that you type into the shell. Please copy i

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 4:27 PM, David Blomstrom wrote: I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin III) and a little monitor (PSQL). When I click on PSQL, it always opens at least two windows or instances. When I type things in and hit enter, it spawns more windows. I've counted

Re: [GENERAL] Recursive Arrays 101

No, I'm on a Mac running OS X El Capitan.

Re: [GENERAL] Recursive Arrays 101

LOL - This is precisely why I prefer GUI's. ;) I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin III) and a little monitor (PSQL). When I click on PSQL, it always opens at least two windows or instances. When I type things in and hit enter, it spawns more windows. I've counted a

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 4:22 PM, Gavin Flower wrote: By type of shell, is meant are using a bash shell in your terminal, or csh, or something else? Bash stands for BOurne Again Shell, it process commands like 'psql' that you type into the shell. Please copy i the email addresses of the other helping y

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 12:15, David Blomstrom wrote: Server [localhost]: \c geozoo2 david Database [postgres]: *Shell2* Username [postgres]: \c geozoo2 david psql: warning: extra command-line argument "david" ignored psql: warning: extra command-line argument "l" ignored psql: invalid port number: "

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 4:13 PM, David Blomstrom wrote: Oops, let me try it again... *Shell1* Server [localhost]: \l Database [postgres]: its asking you for the server host to log onto, and what database on that host. you can't issue commands, including metacommands like \l, until you've logged on

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 7:13 PM, David Blomstrom wrote: > Oops, let me try it again... > > *Shell1* > > Server [localhost]: \l > > Database [postgres]: > > * * * * * > > Server [localhost]: \dt > > Database [postgres]: > > > *Shell2* > > Database [postgres]: \l > > Port [5432]: > > * * * * * > >

Re: [GENERAL] Recursive Arrays 101

On Mon, Oct 26, 2015 at 4:19 PM, Adrian Klaver wrote: > On 10/26/2015 04:13 PM, David Blomstrom wrote: > >> Oops, let me try it again... >> > > Still not making sense. > > Show the exact command you are using to get the below and explain where > you are running it. > > >> *Shell1* >> >> Server [l

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 04:13 PM, David Blomstrom wrote: Oops, let me try it again... Still not making sense. Show the exact command you are using to get the below and explain where you are running it. *Shell1* Server [localhost]: \l Database [postgres]: * * * * * Server [localhost]: \dt Datab

Re: [GENERAL] Recursive Arrays 101

Oops, let me try it again... *Shell1* Server [localhost]: \l Database [postgres]: * * * * * Server [localhost]: \dt Database [postgres]: *Shell2* Database [postgres]: \l Port [5432]: * * * * * Port [5432]: \dt Username [postgres]:

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 04:10 PM, David Blomstrom wrote: The below makes no sense to me. Server [localhost]: /l Database [postgres]: * * * * * Server [localhost]: /dt Database [postgres]: * * * * * However...I've noticed that when I open up the shell, Again, what shell? I get multiple instances

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:18, David Blomstrom wrote: I pasted this into the shell... CREATE ROLE david LOGIN CREATEDB; CREATE DATABASE GeoZoo2 OWNER david; and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there are no new databases. And when I paste this in, it doe

Re: [GENERAL] Recursive Arrays 101

Server [localhost]: /l Database [postgres]: * * * * * Server [localhost]: /dt Database [postgres]: * * * * * However...I've noticed that when I open up the shell, I get multiple instances - sometimes over half a dozen. If I type the same things into one of the other instances, I get this: Pr

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 03:57 PM, David Blomstrom wrote: When I type in /l, it just says "database Postgres," even though I can see TWO databases in pgAdmin III. When I type in /dt, it says Username [postgres]. Huh. It should be \l and \dt Step us through what you are doing, using cut and paste directly

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:57, David Blomstrom wrote: When I type in /l, it just says "database Postgres," even though I can see TWO databases in pgAdmin III. When I type in /dt, it says Username [postgres]. On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote:

Re: [GENERAL] Recursive Arrays 101

When I type in /l, it just says "database Postgres," even though I can see TWO databases in pgAdmin III. When I type in /dt, it says Username [postgres]. On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower wrote: > On 27/10/15 11:18, David Blomstrom wrote: > [...] > >> CREATE DATABASE GeoZoo2 >> > [..

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:18, David Blomstrom wrote: [...] CREATE DATABASE GeoZoo2 [...] Would strongly advise NOT using capital letters in names of databases in pg! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mai

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:18, David Blomstrom wrote: I pasted this into the shell... CREATE ROLE david LOGIN CREATEDB; CREATE DATABASE GeoZoo2 OWNER david; and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there are no new databases. And when I paste this in, it doe

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 09:29, David Blomstrom wrote: [...] Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other w

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 03:18 PM, David Blomstrom wrote: I pasted this into the shell... What shell, psql? CREATE ROLE david LOGIN CREATEDB; CREATE DATABASE GeoZoo2 OWNER david; Did you see any errors? and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there ar

Re: [GENERAL] Recursive Arrays 101

I pasted this into the shell... CREATE ROLE david LOGIN CREATEDB; CREATE DATABASE GeoZoo2 OWNER david; and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there are no new databases. And when I paste this in, it doesn't create a table... CREATE TABLE public.g

Re: [GENERAL] Recursive Arrays 101

OK, I figured out how to drop the primary key and change the NULLS. So it looks like this now: CREATE TABLE public.gz_life_mammals ( id integer NOT NULL, taxon text NOT NULL, parent text NOT NULL, slug text, namecommon text, plural text, extinct smallint NOT NULL, rank smallint NO

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 01:51 PM, David Blomstrom wrote: I'm focusing primarily on vertebrates at the moment, which have a total of (I think) about 60,000-70,000 rows for all taxons (species, families, etc.). My goal is to create a customized database that does a really good job of handling vertebrates fir

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 10:26, David Blomstrom wrote: Here's what it looks like now: CREATE TABLE public.gz_life_mammals ( id integer NOT NULL, taxon text NOT NULL, parent text NOT NULL, slug text, namecommon text, plural text, extinct smallint NOT NULL, rank smallint NOT NULL, key smallin

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 02:26 PM, David Blomstrom wrote: Here's what it looks like now: CREATE TABLE public.gz_life_mammals ( id integer NOT NULL, taxon text NOT NULL, parent text NOT NULL, slug text, namecommon text, plural text, extinct smallint NOT NULL, rank smallint NOT NULL,

Re: [GENERAL] Recursive Arrays 101

Here's what it looks like now: CREATE TABLE public.gz_life_mammals ( id integer NOT NULL, taxon text NOT NULL, parent text NOT NULL, slug text, namecommon text, plural text, extinct smallint NOT NULL, rank smallint NOT NULL, key smallint NOT NULL, CONSTRAINT "Primary Key" PRIM

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 03:21 PM, Gavin Flower wrote: On 27/10/15 10:17, David Blomstrom wrote: What does "top post" mean? And what do you mean by "embedded spaces"? Are you referring to the underscores in the TABLE name? On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 10:17, David Blomstrom wrote: What does "top post" mean? And what do you mean by "embedded spaces"? Are you referring to the underscores in the TABLE name? On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: Hi David, Please don't

Re: [GENERAL] Recursive Arrays 101

What does "top post" mean? And what do you mean by "embedded spaces"? Are you referring to the underscores in the TABLE name? On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower wrote: > Hi David, > > Please don't top post! > > > On 27/10/15 09:42, David Blomstrom wrote: > >> I've created my first tab

Re: [GENERAL] Recursive Arrays 101

Hi David, Please don't top post! On 27/10/15 09:42, David Blomstrom wrote: I've created my first table in postgreSQL. I'd like to ask 1) if you see any errors, 2) do you have any suggestions for improving it, and 3) can you give me the code I need to paste into the shell (or whatever you call

Re: [GENERAL] Recursive Arrays 101

Incidentally, this is the script I'm using to display pages. It queries each table twice (field Taxon for scientific names, Slug for common names). It then picks up three auxiliary tables listing "parataxa," floaters and groups. $sql = "SELECT SUM(num) as num FROM ( SELECT COUNT(Taxon) AS num FR

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 02:51 PM, David Blomstrom wrote: I'm focusing primarily on vertebrates at the moment, which have a total of (I think) about 60,000-70,000 rows for all taxons (species, families, etc.). My goal is to create a customized database that does a really good job of handling vertebrates f

Re: [GENERAL] Recursive Arrays 101

I'm focusing primarily on vertebrates at the moment, which have a total of (I think) about 60,000-70,000 rows for all taxons (species, families, etc.). My goal is to create a customized database that does a really good job of handling vertebrates first, manually adding a few key invertebrates and p

Re: [GENERAL] Recursive Arrays 101

I've created my first table in postgreSQL. I'd like to ask 1) if you see any errors, 2) do you have any suggestions for improving it, and 3) can you give me the code I need to paste into the shell (or whatever you call the command-line tool) to recreate it? This is what the table's schema looks li

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 02:29 PM, David Blomstrom wrote: Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe. Anyway, where do I begin? Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save

Re: [GENERAL] Recursive Arrays 101

Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe. Anyway, where do I begin? Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database tab

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 10:33 AM, Rob Sargent wrote: On 10/26/2015 11:14 AM, Adrian Klaver wrote: On 10/26/2015 08:32 AM, Rob Sargent wrote: On 10/26/2015 09:22 AM, Adrian Klaver wrote: On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8:10 PM, David Bloms

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 11:14 AM, Adrian Klaver wrote: On 10/26/2015 08:32 AM, Rob Sargent wrote: On 10/26/2015 09:22 AM, Adrian Klaver wrote: On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8:10 PM, David Blomstrom wrote: @ Adrian Klaver: Oh, so you're

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 08:32 AM, Rob Sargent wrote: On 10/26/2015 09:22 AM, Adrian Klaver wrote: On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8:10 PM, David Blomstrom wrote: @ Adrian Klaver: Oh, so you're suggesting I make separate tables for kingdoms

Re: [GENERAL] Recursive Arrays 101

Thomas Kellerer writes: > I always wonder whether it's more efficient to aggregate this path > using an array rather than a varchar. Mainly because representing the > numbers as varchars will require more memory than as integer, but then > I don't know the overhead of an array structure and wheth

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 09:22 AM, Adrian Klaver wrote: On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8: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 resea

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8: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 wi

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8: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 ma

Re: [GENERAL] Recursive Arrays 101

Alban Hertroys schrieb am 25.10.2015 um 22:07: > 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

Re: [GENERAL] Recursive Arrays 101

On 10/25/15 8: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,

Re: [GENERAL] Recursive Arrays 101

I would suggest that you use int or bigint for primary keys, and have mapping tables to convert the scientific term to the surrogate primary key. If the mapping table has additional attributes, like date of change & reason, then you can also print a history of changes. Then the relationships

Re: [GENERAL] Recursive Arrays 101

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

Re: [GENERAL] Recursive Arrays 101

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

Re: [GENERAL] Recursive Arrays 101

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 wil

Re: [GENERAL] Recursive Arrays 101

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 cal

Re: [GENERAL] Recursive Arrays 101

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

Re: [GENERAL] Recursive Arrays 101

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

Re: [GENERAL] Recursive Arrays 101

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 specie

Re: [GENERAL] Recursive Arrays 101

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

@ 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 - especial

Re: [GENERAL] Recursive Arrays 101

> 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

Re: [GENERAL] Recursive Arrays 101

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 No

Re: [GENERAL] Recursive Arrays 101

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-mys

Re: [GENERAL] Recursive Arrays 101

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/mammal

[GENERAL] Recursive Arrays 101

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