Re: [GENERAL] Duplicate rows during pg_dump

[Marc Mamin]

> -Original Message-
> From: Jim Nasby [mailto:jim.na...@bluetreble.com]
> Sent: Montag, 26. Oktober 2015 01:55
> To: Marc Mamin; Adrian Klaver; Chaz Yoon; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Duplicate rows during pg_dump
> 
> 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...

Last time I got this trouble was 4-5 years ago...

regards,

Marc Mamin




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

[Rafal Pietrak]

W dniu 26.10.2015 o 01:49, Jim Nasby pisze:
> On 10/25/15 2:30 AM, Rafal Pietrak wrote:
[]
> 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 ...

It certainly look nasty.

But in the long run I strongly believe in peoples ability to "settle for
conventions", and I think I could find my ways with that construct, if
only it was possible. Not that I'd prefere it to the explicit aliasing
for aliased table coluns, syntactically inside column alias list. My
personal point of view is that:
1. "a star" in select is not so harmfull, since backend gives out the
names along with the data; and you cen see them in psql output and
retrieve them from client library (libpq) thus adopting for any order "a
star" happen to create.
2. in all other cases (from which I know: table aliases and inserts),
the backend does not interfere only when your error in arrangeing the
list results in a data type missmatch. this is wrong.

But anyway, thenx for pointing me to column aliasing, which I didn't
know before.

-R


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

[Gavin Flower]

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 between tables will be more isolated from changes 
in scientific nomenclature!  Plus if the same animals known by different 
scientific names at different times, you can have several mappings to 
the same animal.  Also if an organism is moved from one phylum to 
another, you can find the organism via either new or old references.
I've heard of cases were one species, is suddenly found to be 2 or 
distinct species!



Cheers,
Gavin


On 26/10/15 18:19, David Blomstrom wrote:
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 
mailto:adrian.kla...@aklaver.com>> 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 




--
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] function null composite behavior

[Rikard Pavelic]

On Sun, 25 Oct 2015 22:31:03 +0100
Rikard Pavelic  wrote:

> I assume there is no way to get sql like result from plpgsql
> function?

I should try harder ;)

Managed to get it working using array and array_agg.

Regards,
Rikard

-- 
Rikard Pavelic
https://dsl-platform.com/
http://templater.info/


-- 
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] function null composite behavior

[dinesh kumar]

On Mon, Oct 26, 2015 at 3:34 PM, Rikard Pavelic  wrote:

> On Sun, 25 Oct 2015 22:31:03 +0100
> Rikard Pavelic  wrote:
>
> > I assume there is no way to get sql like result from plpgsql
> > function?
>


> I should try harder ;)
>
> Managed to get it working using array and array_agg.
>
> Yeah, that works.

Might be this is what

you are looking for.


> Regards,
> Rikard
>
> --
> Rikard Pavelic
> https://dsl-platform.com/
> http://templater.info/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

[GENERAL] function null composite behavior

[Rikard Pavelic]

Hi,

I'm trying to resolve an issue in production due to difference between
composite null handling in plpgsql function.

Is there some way to get plain sql behavior regarding null composites
within plpgsql?

From the example:

create type composite as (i int);
create table data (i int, c composite);
insert into data (i) values(2);

create function plain_sql(inout id int, out d data) returns record as
$$ select id, d from data d where i = id $$ language sql;

create function plpg_sql(inout id int, out d data) returns record as
$$ begin select * into d from data where i = id; end; $$ language
plpgsql;

When I run queries (and expect the same result)

select d from plain_sql(2);
select d from plpg_sql(2);

I get: "(2,)" and "(2,"()")"

I assume there is no way to get sql like result from plpgsql
function?

Regards,
Rikard

-- 
Rikard Pavelic
https://dsl-platform.com/
http://templater.info/


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

[GENERAL] Recurring corrupted page pointer panics on 9.4.4 hot-standby replica

[Michael Robinson]

Hi,

Two days ago, we started getting panics on a hot-standby replica as follows:

2015-10-24 14:16:46.489 UTC PANIC:  corrupted page pointers: lower = 17,
> upper = 0, special = 8176
> 2015-10-24 14:16:46.490 UTC CONTEXT:  xlog redo unlink_page: rel
> 1663/16416/254063; dead 11796080; left 1365037; right 3024097; btpo_xact
> 64542957; leaf 2456241; leafleft 11130443; leafright 1350594; topparent
> 4294967295
> 2015-10-26 04:51:40.530 UTC PANIC:  corrupted page pointers: lower = 17,
> upper = 0, special = 8176
> 2015-10-26 04:51:40.530 UTC CONTEXT:  xlog redo unlink_page: rel
> 1663/16416/254063; dead 9922828; left 2449142; right 3415026; btpo_xact
> 64982371; leaf 2290440; leafleft 5120238; leafright 1903321; topparent
> 4294967295
> 2015-10-26 10:24:02.613 UTC PANIC:  corrupted page pointers: lower = 17,
> upper = 0, special = 8176
> 2015-10-26 10:24:02.613 UTC CONTEXT:  xlog redo unlink_page: rel
> 1663/16416/401628; dead 2348571; left 2348281; right 2351431; btpo_xact
> 65010718; leaf 2348740; leafleft 2348434; leafright 2351568; topparent
> 4294967295


The replica is running on a dedicated EC2 instance, and has been running
without any problems for several months.  The build version is
 9.4.4-1.pgdg14.04+1 from the apt repository, running on Ubuntu 14.04
Trusty.  The database is around 440GB, and is under constant moderate
read-only load (100-1000 queries per second).

There have been no issues with the master database, nor have there been any
database shutdowns other than the panics.

I would be very grateful for any insights as to what may have caused this,
and how best to recover stable operation.

Best regards,
Michael Robinson

[GENERAL] Service not starting on Ubuntu 15.04

[Lasse Westh-Nielsen]

Hey,

I posted to stackoverflow about my problem upgrading from Ubuntu 14.04 to
Ubuntu 15.04:
http://stackoverflow.com/questions/33306475/ubuntu-15-04-postgresql-doesnt-start

Tl;dr: postgresql service does not start properly when installed as a
package using cloud-init.

And I can't figure out if I am doing something wrong, if the AMI is no
good, if the package has problems, ... I reckon I cannot be the first
person to use Postgres on Ubuntu Vivid, but I have been hammering my head
against the wall with this for hours.

Any help greatly appreciated!

Regards,

Lasse

Re: [GENERAL] Service not starting on Ubuntu 15.04

[Bill Moran]

On Mon, 26 Oct 2015 11:21:23 +
Lasse Westh-Nielsen  wrote:
> 
> I posted to stackoverflow about my problem upgrading from Ubuntu 14.04 to
> Ubuntu 15.04:
> http://stackoverflow.com/questions/33306475/ubuntu-15-04-postgresql-doesnt-start
> 
> Tl;dr: postgresql service does not start properly when installed as a
> package using cloud-init.
> 
> And I can't figure out if I am doing something wrong, if the AMI is no
> good, if the package has problems, ... I reckon I cannot be the first
> person to use Postgres on Ubuntu Vivid, but I have been hammering my head
> against the wall with this for hours.
> 
> Any help greatly appreciated!

I'm taking a shot in the dark here, but ...

The symptoms you describe seem to suggest that the script is starting
PostgreSQL asynchronously (i.e. in the background) which means that
the CREATE command runs too quickly and the server isn't started yet.

A quick way to _test_ this theory would be to put a sleep between the
install and the CREATE commands and see if the problem goes away.

If that does seem to be the problem, then a good _fix_ would be to
find a way to foreground the startup of the server, or have some command
that tests to ensure the server is started and blocks until it is
before running the create command.

The only point I'm unclear on is whether you've confirmed that
Postgres actually _is_ started once the server is up (albiet without
the CREATE statement having succeeded).

-- 
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] Service not starting on Ubuntu 15.04

[Adrian Klaver]

On 10/26/2015 04:21 AM, Lasse Westh-Nielsen wrote:

Hey,

I posted to stackoverflow about my problem upgrading from Ubuntu 14.04
to Ubuntu 15.04:
http://stackoverflow.com/questions/33306475/ubuntu-15-04-postgresql-doesnt-start


Pretty sure it is no coincidence that the 14.04 --> 15.04 upgrade 
changes the init process from Upstart to systemd. Might want to take a 
look at this article:


http://linux.softpedia.com/blog/Ubuntu-15-04-Users-Can-Switch-Off-Systemd-and-Use-Upstart-479373.shtml

and this:

https://wiki.ubuntu.com/SystemdForUpstartUsers




Tl;dr: postgresql service does not start properly when installed as a
package using cloud-init.


What does the /var/log/bootstrap.log show?



And I can't figure out if I am doing something wrong, if the AMI is no
good, if the package has problems, ... I reckon I cannot be the first
person to use Postgres on Ubuntu Vivid, but I have been hammering my
head against the wall with this for hours.

Any help greatly appreciated!

Regards,

Lasse





--
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] Service not starting on Ubuntu 15.04

[Lasse Westh-Nielsen]

Bill,

Thanks for your help. But I have already tried with a variant that gives
the postgres service time to do what it needs to do:

#!/bin/bash -eux

exec > >(tee /var/log/bootstrap.log)
exec 2>&1

apt-get --quiet --quiet update
apt-get --quiet --quiet upgrade

apt-get install --quiet --quiet postgresql

service postgresql status || true
sleep 10
service postgresql stop
service postgresql status || true
sleep 10
service postgresql start
service postgresql status || true
sleep 10
service postgresql status || true
pstree

sudo -u postgres psql -c "CREATE USER foo WITH PASSWORD 'foo';"


So, output from that:
 - service starts normally, i.e. first call to status says the service is
up.
 - sleep-then-stop: now calling status says it has stopped.
 - sleep-more-then-start: status reports service is up again
 - even-more-sleep: status says service is still up
 - however, that last call to pstree finds no trace of postgres

Sleeping 10 seconds came from me running these start and stop commands from
command line, where response time seems to be 2-3 seconds for start,
meaning 10 seconds should be plenty (and start seems to block, why else
would it take 3 seconds).

Thanks anyway,

Lasse




On Mon, Oct 26, 2015 at 12:09 PM, Bill Moran 
wrote:

> On Mon, 26 Oct 2015 11:21:23 +
> Lasse Westh-Nielsen  wrote:
> >
> > I posted to stackoverflow about my problem upgrading from Ubuntu 14.04 to
> > Ubuntu 15.04:
> >
> http://stackoverflow.com/questions/33306475/ubuntu-15-04-postgresql-doesnt-start
> >
> > Tl;dr: postgresql service does not start properly when installed as a
> > package using cloud-init.
> >
> > And I can't figure out if I am doing something wrong, if the AMI is no
> > good, if the package has problems, ... I reckon I cannot be the first
> > person to use Postgres on Ubuntu Vivid, but I have been hammering my head
> > against the wall with this for hours.
> >
> > Any help greatly appreciated!
>
> I'm taking a shot in the dark here, but ...
>
> The symptoms you describe seem to suggest that the script is starting
> PostgreSQL asynchronously (i.e. in the background) which means that
> the CREATE command runs too quickly and the server isn't started yet.
>
> A quick way to _test_ this theory would be to put a sleep between the
> install and the CREATE commands and see if the problem goes away.
>
> If that does seem to be the problem, then a good _fix_ would be to
> find a way to foreground the startup of the server, or have some command
> that tests to ensure the server is started and blocks until it is
> before running the create command.
>
> The only point I'm unclear on is whether you've confirmed that
> Postgres actually _is_ started once the server is up (albiet without
> the CREATE statement having succeeded).
>
> --
> Bill Moran
>

Re: [GENERAL] Service not starting on Ubuntu 15.04

[Lasse Westh-Nielsen]

Adrian,

Thanks. I know about the systemd change, and indeed the postgres package I
end up with _has_ systemd integration already:

$ cat /etc/systemd/system/multi-user.target.wants/postgresql.service
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target



Here are the contents of bootstrap.log; notice I added some checks and
waits. As you can see, service stop and start has effect in that calls to
status seem to say the service has started and stopped. However, it clearly
never starts. Interestingly, the same exact calls to service stop and start
_after_ cloud-init has finished will give me a running postgres service.

+ apt-get install --quiet --quiet postgresql
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
debconf: unable to initialize frontend: Readline
debconf: (This frontend requires a controlling tty.)
debconf: falling back to frontend: Teletype
dpkg-preconfigure: unable to re-open stdin:
Selecting previously unselected package libpq5:amd64.
(Reading database ... 53521 files and directories currently installed.)
Preparing to unpack .../libpq5_9.4.5-0ubuntu0.15.04_amd64.deb ...
Unpacking libpq5:amd64 (9.4.5-0ubuntu0.15.04) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../postgresql-client-common_166bzr2_all.deb ...
Unpacking postgresql-client-common (166bzr2) ...
Selecting previously unselected package postgresql-client-9.4.
Preparing to unpack
.../postgresql-client-9.4_9.4.5-0ubuntu0.15.04_amd64.deb ...
Unpacking postgresql-client-9.4 (9.4.5-0ubuntu0.15.04) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../ssl-cert_1.0.35_all.deb ...
Unpacking ssl-cert (1.0.35) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../postgresql-common_166bzr2_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by
postgresql-common'
Unpacking postgresql-common (166bzr2) ...
Selecting previously unselected package postgresql-9.4.
Preparing to unpack .../postgresql-9.4_9.4.5-0ubuntu0.15.04_amd64.deb ...
Unpacking postgresql-9.4 (9.4.5-0ubuntu0.15.04) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../postgresql_9.4+166bzr2_all.deb ...
Unpacking postgresql (9.4+166bzr2) ...
Processing triggers for man-db (2.7.0.2-5) ...
Processing triggers for systemd (219-7ubuntu6) ...
Processing triggers for ureadahead (0.100.0-19) ...
Setting up libpq5:amd64 (9.4.5-0ubuntu0.15.04) ...
Setting up postgresql-client-common (166bzr2) ...
Setting up postgresql-client-9.4 (9.4.5-0ubuntu0.15.04) ...
update-alternatives: using /usr/share/postgresql/9.4/man/man1/psql.1.gz to
provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up ssl-cert (1.0.35) ...
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
Setting up postgresql-common (166bzr2) ...
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new
version

Creating config file /etc/logrotate.d/postgresql-common with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Setting up postgresql-9.4 (9.4.5-0ubuntu0.15.04) ...
Creating new cluster 9.4/main ...
  config /etc/postgresql/9.4/main
  data   /var/lib/postgresql/9.4/main
  locale en_US.UTF-8
Flags of /var/lib/postgresql/9.4/main set as -e-C
  port   5432
update-alternatives: using
/usr/share/postgresql/9.4/man/man1/postmaster.1.gz to provide
/usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (9.4+166bzr2) ...
Processing triggers for libc-bin (2.21-0ubuntu4) ...
Processing triggers for systemd (219-7ubuntu6) ...
Processing triggers for ureadahead (0.100.0-19) ...
+ service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor
preset: enabled)
   Active: active (exited) since Mon 2015-10-26 10:57:51 UTC; 3s ago
 Main PID: 7481 (code=exited, status=0/SUCCESS)

Oct 26 10:57:51 ip-10-103-210-147 systemd[1]: Starting PostgreSQL RDBMS...
Oct 26 10:57:51 ip-10-103-210-147 systemd[1]: Started PostgreSQL RDBMS.
Oct 26 10:57:54 ip-10-103-210-147 systemd[1]: Started PostgreSQL RDBMS.
+ sleep 10
+ service postgresql stop
+ service postgresql status
● postgresql.service - Post

Re: [GENERAL] Service not starting on Ubuntu 15.04

[Stuart Bishop]

On 26 October 2015 at 18:21, Lasse Westh-Nielsen  wrote:
> Hey,
>
> I posted to stackoverflow about my problem upgrading from Ubuntu 14.04 to
> Ubuntu 15.04:
> http://stackoverflow.com/questions/33306475/ubuntu-15-04-postgresql-doesnt-start
>
> Tl;dr: postgresql service does not start properly when installed as a
> package using cloud-init.
>
> And I can't figure out if I am doing something wrong, if the AMI is no good,
> if the package has problems, ... I reckon I cannot be the first person to
> use Postgres on Ubuntu Vivid, but I have been hammering my head against the
> wall with this for hours.
>
> Any help greatly appreciated!

I'd be trying 'pg_ctlcluster --force 9.4 main stop'  and
'pg_ctlcluster 9.4 main start' instead of service start/stop. If it
works, you have narrowed down the problem to systemd and/or the
startup scripts. If it doesn't work, the problem is elsewhere (eg.
some required path like /var/run or /tmp or /var/log/postgresql
doesn't exist or has peculiar permissions). If nothing else, you
should get more informative output rather than systemd hiding it away
somewhere.

-- 
Stuart Bishop 
http://www.stuartbishop.net/


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

[Jim Nasby]

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, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-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

Re: [GENERAL] Service not starting on Ubuntu 15.04

[Adrian Klaver]

On 10/26/2015 07:08 AM, Lasse Westh-Nielsen wrote:

Adrian,

Thanks. I know about the systemd change, and indeed the postgres package
I end up with _has_ systemd integration already:

$ cat /etc/systemd/system/multi-user.target.wants/postgresql.service
# systemd service for managing all PostgreSQL clusters on the
system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target


The thing above the above is I do not see anything that starts Postgres.

Are you sure the same script is being called in cloud-init and outside 
of it?


Or more to the point, what script actually starts Postgres?




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

[Thomas Kellerer]

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

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 whether appending to an array doesn't 
actually copy it.

So "array[n] as path" in the root query and "taxons.path||n" in the recursive 
part. 

Any ideas? 





-- 
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] Service not starting on Ubuntu 15.04

[Lasse Westh-Nielsen]

Adrian,

The service starts once the package is installed.

 - It did that on Ubuntu14.04
 - On Ubuntu 15.04, `sudo service postgresql status` _claims_ it has been
started.



On Mon, Oct 26, 2015 at 2:57 PM, Adrian Klaver 
wrote:

> On 10/26/2015 07:08 AM, Lasse Westh-Nielsen wrote:
>
>> Adrian,
>>
>> Thanks. I know about the systemd change, and indeed the postgres package
>> I end up with _has_ systemd integration already:
>>
>> $ cat /etc/systemd/system/multi-user.target.wants/postgresql.service
>> # systemd service for managing all PostgreSQL clusters on the
>> system. This
>> # service is actually a systemd target, but we are using a service
>> since
>> # targets cannot be reloaded.
>>
>> [Unit]
>> Description=PostgreSQL RDBMS
>>
>> [Service]
>> Type=oneshot
>> ExecStart=/bin/true
>> ExecReload=/bin/true
>> RemainAfterExit=on
>>
>> [Install]
>> WantedBy=multi-user.target
>>
>
> The thing above the above is I do not see anything that starts Postgres.
>
> Are you sure the same script is being called in cloud-init and outside of
> it?
>
> Or more to the point, what script actually starts Postgres?
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Service not starting on Ubuntu 15.04

[Lasse Westh-Nielsen]

Stuart,

You are a life-saver!

With those two commands inserted into the cloud-init script, the service
actually starts and can complete my CREATE command.

Thanks!

 - Lasse




On Mon, Oct 26, 2015 at 2:31 PM, Stuart Bishop 
wrote:

> On 26 October 2015 at 18:21, Lasse Westh-Nielsen 
> wrote:
> > Hey,
> >
> > I posted to stackoverflow about my problem upgrading from Ubuntu 14.04 to
> > Ubuntu 15.04:
> >
> http://stackoverflow.com/questions/33306475/ubuntu-15-04-postgresql-doesnt-start
> >
> > Tl;dr: postgresql service does not start properly when installed as a
> > package using cloud-init.
> >
> > And I can't figure out if I am doing something wrong, if the AMI is no
> good,
> > if the package has problems, ... I reckon I cannot be the first person to
> > use Postgres on Ubuntu Vivid, but I have been hammering my head against
> the
> > wall with this for hours.
> >
> > Any help greatly appreciated!
>
> I'd be trying 'pg_ctlcluster --force 9.4 main stop'  and
> 'pg_ctlcluster 9.4 main start' instead of service start/stop. If it
> works, you have narrowed down the problem to systemd and/or the
> startup scripts. If it doesn't work, the problem is elsewhere (eg.
> some required path like /var/run or /tmp or /var/log/postgresql
> doesn't exist or has peculiar permissions). If nothing else, you
> should get more informative output rather than systemd hiding it away
> somewhere.
>
> --
> Stuart Bishop 
> http://www.stuartbishop.net/
>

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would have a 
single table with those four attributes on the particular life form.  
Now, the four attributes could be ids into definitional tables but I 
suspect the querying will be done string/name so why complicate the 
lookups: make the names a foreign key in the defs if necessary.


Personally I think the recursive structure is the way to go.

Re: [GENERAL] Service not starting on Ubuntu 15.04

[Adrian Klaver]

On 10/26/2015 08:04 AM, Lasse Westh-Nielsen wrote:

Adrian,

The service starts once the package is installed.

  - It did that on Ubuntu14.04
  - On Ubuntu 15.04, `sudo service postgresql status` _claims_ it has
been started.


Yes, it starts but it is a go nowhere, do nothing service:

$ cat /etc/systemd/system/multi-user.target.wants/postgresql.service
# systemd service for managing all PostgreSQL clusters on the 
system. This
# service is actually a systemd target, but we are using a service 
since

# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target

All it does is start /bin/true which is why the status shows it started, 
but Postgres does not run. My guess is the answer is in here:


http://askubuntu.com/questions/632882/does-upstart-really-handle-all-startup-scripts

Second answer

" PostgreSQL comes with two systemd service unit files in version 15, 
/lib/systemd/system/postgresql@.service and 
/lib/systemd/system/postgresql.service. System 5 rc stuff is thus 
completely ignored."



Best guess is /lib/systemd/system/postgresql@.service is the one that 
actually starts Postgres. Sort of confirmed by the comments in the 
service script above:


"# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded."

Pretty sure /lib/systemd/system/postgresql.service is just a placeholder 
script.


At any rate Stuarts answer and your application of it confirms the 
service script is the issue.






On Mon, Oct 26, 2015 at 2:57 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/26/2015 07:08 AM, Lasse Westh-Nielsen wrote:

Adrian,

Thanks. I know about the systemd change, and indeed the postgres
package
I end up with _has_ systemd integration already:

 $ cat
/etc/systemd/system/multi-user.target.wants/postgresql.service
 # systemd service for managing all PostgreSQL clusters on the
 system. This
 # service is actually a systemd target, but we are using a
service since
 # targets cannot be reloaded.

 [Unit]
 Description=PostgreSQL RDBMS

 [Service]
 Type=oneshot
 ExecStart=/bin/true
 ExecReload=/bin/true
 RemainAfterExit=on

 [Install]
 WantedBy=multi-user.target


The thing above the above is I do not see anything that starts Postgres.

Are you sure the same script is being called in cloud-init and
outside of it?

Or more to the point, what script actually starts Postgres?




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





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

[Adrian Klaver]

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


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made 
memorizing all those organisms a lot easier when I was in school:)




Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.



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

[Rob Sargent]

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


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made 
memorizing all those organisms a lot easier when I was in school:)


kingdom phylum class genus as attributes in species table.  Talk about 
your "natural key".  The hibernate boys would love it :)



Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.


Jtbc, I'm not advocating this structure but it may suit the OP's usage 
patterns.





--
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] Service not starting on Ubuntu 15.04

[Melvin Davidson]

Just out of curiosity, is there anything in the postgresql.log which gives
an indication of a problem?

On Mon, Oct 26, 2015 at 11:18 AM, Adrian Klaver 
wrote:

> On 10/26/2015 08:04 AM, Lasse Westh-Nielsen wrote:
>
>> Adrian,
>>
>> The service starts once the package is installed.
>>
>>   - It did that on Ubuntu14.04
>>   - On Ubuntu 15.04, `sudo service postgresql status` _claims_ it has
>> been started.
>>
>
> Yes, it starts but it is a go nowhere, do nothing service:
>
> $ cat /etc/systemd/system/multi-user.target.wants/postgresql.service
> # systemd service for managing all PostgreSQL clusters on the system.
> This
> # service is actually a systemd target, but we are using a service
> since
> # targets cannot be reloaded.
>
> [Unit]
> Description=PostgreSQL RDBMS
>
> [Service]
> Type=oneshot
> ExecStart=/bin/true
> ExecReload=/bin/true
> RemainAfterExit=on
>
> [Install]
> WantedBy=multi-user.target
>
> All it does is start /bin/true which is why the status shows it started,
> but Postgres does not run. My guess is the answer is in here:
>
>
> http://askubuntu.com/questions/632882/does-upstart-really-handle-all-startup-scripts
>
> Second answer
>
> " PostgreSQL comes with two systemd service unit files in version 15,
> /lib/systemd/system/postgresql@.service and
> /lib/systemd/system/postgresql.service. System 5 rc stuff is thus
> completely ignored."
>
>
> Best guess is /lib/systemd/system/postgresql@.service is the one that
> actually starts Postgres. Sort of confirmed by the comments in the service
> script above:
>
> "# systemd service for managing all PostgreSQL clusters on the system. This
> # service is actually a systemd target, but we are using a service since
> # targets cannot be reloaded."
>
> Pretty sure /lib/systemd/system/postgresql.service is just a placeholder
> script.
>
> At any rate Stuarts answer and your application of it confirms the service
> script is the issue.
>
>
>>
>>
>> On Mon, Oct 26, 2015 at 2:57 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/26/2015 07:08 AM, Lasse Westh-Nielsen wrote:
>>
>> Adrian,
>>
>> Thanks. I know about the systemd change, and indeed the postgres
>> package
>> I end up with _has_ systemd integration already:
>>
>>  $ cat
>> /etc/systemd/system/multi-user.target.wants/postgresql.service
>>  # systemd service for managing all PostgreSQL clusters on the
>>  system. This
>>  # service is actually a systemd target, but we are using a
>> service since
>>  # targets cannot be reloaded.
>>
>>  [Unit]
>>  Description=PostgreSQL RDBMS
>>
>>  [Service]
>>  Type=oneshot
>>  ExecStart=/bin/true
>>  ExecReload=/bin/true
>>  RemainAfterExit=on
>>
>>  [Install]
>>  WantedBy=multi-user.target
>>
>>
>> The thing above the above is I do not see anything that starts
>> Postgres.
>>
>> Are you sure the same script is being called in cloud-init and
>> outside of it?
>>
>> Or more to the point, what script actually starts Postgres?
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> 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
>



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

Re: [GENERAL] Recursive Arrays 101

[Harald Fuchs]

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 whether appending
> to an array doesn't actually copy it.

If you go that direction, you're not far away from the ltree extension
(which might also be a solution for the OP's problem).



-- 
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] Service not starting on Ubuntu 15.04

[Lasse Westh-Nielsen]

Nope, the log is empty. Literally zero bytes.

On Mon, Oct 26, 2015 at 3:57 PM, Melvin Davidson 
wrote:

> Just out of curiosity, is there anything in the postgresql.log which gives
> an indication of a problem?
>
> On Mon, Oct 26, 2015 at 11:18 AM, Adrian Klaver  > wrote:
>
>> On 10/26/2015 08:04 AM, Lasse Westh-Nielsen wrote:
>>
>>> Adrian,
>>>
>>> The service starts once the package is installed.
>>>
>>>   - It did that on Ubuntu14.04
>>>   - On Ubuntu 15.04, `sudo service postgresql status` _claims_ it has
>>> been started.
>>>
>>
>> Yes, it starts but it is a go nowhere, do nothing service:
>>
>> $ cat /etc/systemd/system/multi-user.target.wants/postgresql.service
>> # systemd service for managing all PostgreSQL clusters on the system.
>> This
>> # service is actually a systemd target, but we are using a service
>> since
>> # targets cannot be reloaded.
>>
>> [Unit]
>> Description=PostgreSQL RDBMS
>>
>> [Service]
>> Type=oneshot
>> ExecStart=/bin/true
>> ExecReload=/bin/true
>> RemainAfterExit=on
>>
>> [Install]
>> WantedBy=multi-user.target
>>
>> All it does is start /bin/true which is why the status shows it started,
>> but Postgres does not run. My guess is the answer is in here:
>>
>>
>> http://askubuntu.com/questions/632882/does-upstart-really-handle-all-startup-scripts
>>
>> Second answer
>>
>> " PostgreSQL comes with two systemd service unit files in version 15,
>> /lib/systemd/system/postgresql@.service and
>> /lib/systemd/system/postgresql.service. System 5 rc stuff is thus
>> completely ignored."
>>
>>
>> Best guess is /lib/systemd/system/postgresql@.service is the one that
>> actually starts Postgres. Sort of confirmed by the comments in the service
>> script above:
>>
>> "# systemd service for managing all PostgreSQL clusters on the system.
>> This
>> # service is actually a systemd target, but we are using a service since
>> # targets cannot be reloaded."
>>
>> Pretty sure /lib/systemd/system/postgresql.service is just a placeholder
>> script.
>>
>> At any rate Stuarts answer and your application of it confirms the
>> service script is the issue.
>>
>>
>>>
>>>
>>> On Mon, Oct 26, 2015 at 2:57 PM, Adrian Klaver
>>> mailto:adrian.kla...@aklaver.com>> wrote:
>>>
>>> On 10/26/2015 07:08 AM, Lasse Westh-Nielsen wrote:
>>>
>>> Adrian,
>>>
>>> Thanks. I know about the systemd change, and indeed the postgres
>>> package
>>> I end up with _has_ systemd integration already:
>>>
>>>  $ cat
>>> /etc/systemd/system/multi-user.target.wants/postgresql.service
>>>  # systemd service for managing all PostgreSQL clusters on
>>> the
>>>  system. This
>>>  # service is actually a systemd target, but we are using a
>>> service since
>>>  # targets cannot be reloaded.
>>>
>>>  [Unit]
>>>  Description=PostgreSQL RDBMS
>>>
>>>  [Service]
>>>  Type=oneshot
>>>  ExecStart=/bin/true
>>>  ExecReload=/bin/true
>>>  RemainAfterExit=on
>>>
>>>  [Install]
>>>  WantedBy=multi-user.target
>>>
>>>
>>> The thing above the above is I do not see anything that starts
>>> Postgres.
>>>
>>> Are you sure the same script is being called in cloud-init and
>>> outside of it?
>>>
>>> Or more to the point, what script actually starts Postgres?
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> 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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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, 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.


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)


kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)


Well in this classification system it would need to be:

kingdom phylum class order family genus

What makes it complicated is that these are just the slots. How 
organisms are slotted depends on attributes and there are a lot of them. 
This means there is a constant rearrangement in the slotting.





Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.



Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.





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

[Josip Rodin]

On Sun, Oct 25, 2015 at 07:57:48AM -0700, David Blomstrom wrote:
> 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.

There might be a causal relation between your first and your second
sentence ;) more stuff doesn't always mean more quality stuff.

The community size discrepancy really isn't that bad these days.
http://stackoverflow.com/questions/tagged/mysql - 350k
http://stackoverflow.com/questions/tagged/postgresql - 45k
Obviously the former number is larger, but it's not necessarily going to be
relevant since the latter number is large enough to support the vast
majority of typical questions that a newcomer is likely to have.

And that's just for that one site (albeit a very popular one) - Google
searches for typical PostgreSQL questions will often turn up fine answers
from the archives of this mailing list.

-- 
 2. That which causes joy or happiness.


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

[Rob Sargent]

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


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would 
have a

single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)


kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)


Well in this classification system it would need to be:

kingdom phylum class order family genus

Sorry, wasn't tracking carefully: 6 attributes


What makes it complicated is that these are just the slots. How 
organisms are slotted depends on attributes and there are a lot of 
them. This means there is a constant rearrangement in the slotting.


But at the end of the day, is it not the intent to have those six filled 
per species. Is your point that maintenance would be problematic?  
Agreed.  Certainly not just a single pointer redirect in a recursive 
structure.  All depends on OPs usage patterns.  I personally love 'with 
recursion' but it's more complicated than for example

select count(*) from species where class = ''
if, and only if, all 6 attributes are always there.  Which highlights 
your caveat "In this classification system".



Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.



Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.









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

[Alexander Reichstadt]

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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would
have a
single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)


kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)


Well in this classification system it would need to be:

kingdom phylum class order family genus

Sorry, wasn't tracking carefully: 6 attributes


What makes it complicated is that these are just the slots. How
organisms are slotted depends on attributes and there are a lot of
them. This means there is a constant rearrangement in the slotting.


But at the end of the day, is it not the intent to have those six filled
per species. Is your point that maintenance would be problematic?
Agreed.  Certainly not just a single pointer redirect in a recursive
structure.  All depends on OPs usage patterns.  I personally love 'with
recursion' but it's more complicated than for example
 select count(*) from species where class = ''
if, and only if, all 6 attributes are always there.  Which highlights
your caveat "In this classification system".


This is the current system. If you want to be historically complete then 
you have to take into account the ways things where classified before. 
Granted this is running in the crawl, walk , run sequence but it cannot 
be entirely ignored. Then there  are the more detailed versions of the 
above:


http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927

It comes done to what view of taxonomy you want to support.




Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.



Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.










--
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] question

[anj patnaik]

Quick update on the above:

1) I got past the password prompt by changing the pg_hba.conf on target
machine to trust.

2) I am still running this command to restore on remote machine and want
feedback as it appears to NOT create the desired table:

xzcat dump_xz |/opt/PostgreSQL/9.4/bin/psql -h mycomp.com postgres -U
postgres


Thanks.

On Fri, Oct 23, 2015 at 6:07 PM, anj patnaik  wrote:

> Thanks Adrian. I've got postgres 9.4 running on a second RHEL 6.5 machine.
> This is the machine I want to restore the pg_dump to.
>
> Last week, I ran this command on the main PG 9.4 db server:
>
> ./pg_dump -t RECORDER  -Fc postgres -Z0 | xz -9 > /tmp/dump_xz
>
> The -Z0 turns off compression. So I used xz and noticed the file was not
> that big.
>
> Now, I got PG 9.4 running today on a 2nd machine. I just did the initdb,
> started service, updated the hba.conf and postgres.conf to accept
> connections from the outside and then restarted service.
>
> 1) When i attempt to restore the archived, I get an error to provide
> password and I am confused what to do here.
>
> 2) is it correct to do xzcat and then pipe that to pgsl? I want to restore
> on mymachine.com
>
> 3) on the target machine, I ran initdb and by default it creates postgres.
> Will that be a problem when restoring?
>
> On this machine I am connected as root.
>
> xzcat dump_xz |/opt/PostgreSQL/9.4/bin/psql -h mymachine.com postgres
> Password:
>
>
> Thank you!
>
> On Sat, Oct 17, 2015 at 7:42 AM, Francisco Olarte 
> wrote:
>
>> Hi Anj:
>>
>> On Sat, Oct 17, 2015 at 3:11 AM, anj patnaik  wrote:
>> > My question is for Francisco who replied regarding xz. I was curious
>> what
>> > options he used. Thanks.
>>
>> 1st, we do not normally top post on this list.
>>
>> Second, I do not remember the exact options I use. I can look them up,
>> but they are going to be surely useless ( they are for a custom
>> process with does several things with files, it uses gzip ( in Fc
>> backups ), plus xz ( for some files which need to be kept for a long
>> time and are nearly never needed ), plus lzo ( as I found lzo
>> compressed temporary files were faster than uncompressed ones ), and a
>> lot of code. But in the development process we did a full comparison
>> of several compressor, and found what I stated with bzip2, it was
>> surpassed in every combination of options by xz ( plain bzip2, plain
>> xz ).
>>
>> Francisco Olarte.
>>
>
>

Re: [GENERAL] question

[anj patnaik]

Thanks Adrian. I've got postgres 9.4 running on a second RHEL 6.5 machine.
This is the machine I want to restore the pg_dump to.

Last week, I ran this command on the main PG 9.4 db server:

./pg_dump -t RECORDER  -Fc postgres -Z0 | xz -9 > /tmp/dump_xz

The -Z0 turns off compression. So I used xz and noticed the file was not
that big.

Now, I got PG 9.4 running today on a 2nd machine. I just did the initdb,
started service, updated the hba.conf and postgres.conf to accept
connections from the outside and then restarted service.

1) When i attempt to restore the archived, I get an error to provide
password and I am confused what to do here.

2) is it correct to do xzcat and then pipe that to pgsl? I want to restore
on mymachine.com

3) on the target machine, I ran initdb and by default it creates postgres.
Will that be a problem when restoring?

On this machine I am connected as root.

xzcat dump_xz |/opt/PostgreSQL/9.4/bin/psql -h mymachine.com postgres
Password:


Thank you!

On Sat, Oct 17, 2015 at 7:42 AM, Francisco Olarte 
wrote:

> Hi Anj:
>
> On Sat, Oct 17, 2015 at 3:11 AM, anj patnaik  wrote:
> > My question is for Francisco who replied regarding xz. I was curious what
> > options he used. Thanks.
>
> 1st, we do not normally top post on this list.
>
> Second, I do not remember the exact options I use. I can look them up,
> but they are going to be surely useless ( they are for a custom
> process with does several things with files, it uses gzip ( in Fc
> backups ), plus xz ( for some files which need to be kept for a long
> time and are nearly never needed ), plus lzo ( as I found lzo
> compressed temporary files were faster than uncompressed ones ), and a
> lot of code. But in the development process we did a full comparison
> of several compressor, and found what I stated with bzip2, it was
> surpassed in every combination of options by xz ( plain bzip2, plain
> xz ).
>
> Francisco Olarte.
>

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 table. It would be very hard to break with that tradition, because
I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different
taxonomic levels (kingdom, class, etc.). So I can easily sort a table into
specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders
and even families into a table. But genera might be harder, and species
probably won't work; there are simply too many. My spreadsheet program is
almost overwhelmed by fish species alone. The only solution would be if I
could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But
that might be kind of tedious, especially if I have to make multiple
updates.

As for "attributes," I'll post my table's schema, with a description, next.

On Mon, Oct 26, 2015 at 10:44 AM, Adrian Klaver 
wrote:

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

>>>
>>> The suggestion is simply to have 7 tables:
>>>
>>> CREATE TABLE kingdom(
>>>   kingdom_id serial PRIMARY KEY
>>>   , kingdom_name text NOT NULL
>>>   , ...
>>> );
>>> CREATE TABLE phylum(
>>>   phylum_id serial PRIMARY KEY
>>>   , kingdom_id int NOT NULL REFERENCES kingdom
>>>   , ...
>>> );
>>> CREATE TABLE class(
>>> ...
>>> );
>>>
>>> and so-on.
>>>
>> Seems to me that if life boils down to four attributes one would
>> have a
>> single table with those four attributes on the particular life form.
>>
>
> Out of curiosity what are those four attributes? It would have made
> memorizing all those organisms a lot easier when I was in school:)
>
> kingdom phylum class genus as attributes in species table.  Talk about
 your "natural key".  The hibernate boys would love it :)

>>>
>>> Well in this classification system it would need to be:
>>>
>>> kingdom phylum class order family genus
>>>
>> Sorry, wasn't tracking carefully: 6 attributes
>>
>>>
>>> What makes it complicated is that these are just the slots. How
>>> organisms are slotted depends on attributes and there are a lot of
>>> them. This means there is a constant rearrangement in the slotting.
>>>
>>> But at the end of the day, is it not the intent to have those six filled
>> per species. Is your point that maintenance would be problematic?
>> Agreed.  Certainly not just a single pointer redirect in a recursive
>> structure.  All depends on OPs usage patterns.  I personally love 'with
>> recursion' but it's more complicated than for example
>>  select count(*) from species where class = ''
>> if, and only if, all 6 attributes are always there.  Which highlights
>> your caveat "In this classification system".
>>
>
> This is the current system. If you want to be historically complete then
> you have to take into account the ways things where classified before.
> Granted this is running in the crawl, walk , run sequence but it cannot be
> entirely ignored. Then there  are the more detailed versions of the above:
>
>
> http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927
>
> It comes done to what view of taxonomy you want to support.
>
>
>> Now, the four attributes could be ids into definitional tables but I
>> suspect the querying will be done string/name so why complicate the
>> lookups: make the names a foreign key in the defs if necessary.
>>
>> Personally I think the recursive structure is the way to go.
>>
>
> Jtbc, I'm not advocating this structure but it may suit the OP's usage
 patterns.



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

[Rob Sargent]

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 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 way to organize my data.


On the other hand, I have a column (Rank) that identifies different 
taxonomic levels (kingdom, class, etc.). So I can easily sort a table 
into specific taxonomic levels and save one level at a time for a 
database table.


There is one problem, though. I can easily put all the vertebrate 
orders and even families into a table. But genera might be harder, and 
species probably won't work; there are simply too many. My spreadsheet 
program is almost overwhelmed by fish species alone. The only solution 
would be if I could import Mammals.csv, then import Birds.csv, 
Reptiles.csv, etc. But that might be kind of tedious, especially if I 
have to make multiple updates.


Yes I suspect you spreadsheet will be limited in rows, but of course you 
can send all the spreadsheets to a single table in the database. If 
that's what you want.  You don't have to, but you see mention of tables 
millions of records routinely.  On the other hand, if performance 
becomes an issue with the single table approach you might want to look 
at "partitioning".  But I would be surprised if you had to go there.


What is your data source?  How much hand-entry are you doing? There are 
tools which (seriously) upgrade the basic 'COPY into ' command.


As for "attributes," I'll post my table's schema, with a description, 
next.






--
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'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 like in MySQL...

N - int(6) [Primary Key]
Taxon - varchar(50) [Unique Key]
Parent - varchar(50) [Index Key]
ParentID - tinyint(1) [Index Key]
Slug - varchar(50) [Index Key]
NameCommon - varchar(50)
Plural - varchar(50)
Extinct - tinyint(1)
Rank - tinyint(2)
Key - tinyint(1)

The table type is MyIsam, collation is latin1_general_ci

Slug, NameCommon and Plural are NULL.

All of my tables have a default first column named N or ID, which is simply
a numerical key that begins with 1. It's always designated the primary key.

All the other columns in this table can be divided into two categories,
text (varchar) and numerical (tinyint).

The values in the columns Taxon and Slug serve as URL's, so they can have
no spaces, apostrophes, accents, etc. (Taxon handles scientific names, Slug
common names, if any.) So a row focusing on the Steller's jay would have
values like these:

NameCommmon - Steller’s jay
Plural - Steller’s jays
Taxon - Cyanocitta-stelleri
Slug - stellers-jay
Parent - Cyanocitta

The column ParentID - which I want to use for hierarchical relationships -
has values ranging from 1 for Mammalia (the first row) to 5 for the species
level. The column Extinct has the value 1 (not extinct) or 2, 3 or 4 for
various categories of extinct taxons.

The column Rank has the value 25 for the first row (class Mammalia), 35 for
each order (e.g. Carnivora), 45 for each family, 55 for each genus and 65
for each species. The value for Key is 1 (for every row), designating it a
tetrapod. The bird, reptile and amphibian tables have the same key value,
while fish, invertebrates and plants have their own unique keys.

I have Unique keys on N and Taxon, Index keys (not unique) on Parent,
ParentID and Slug.

My PostgreSQL table is in a database named GeoZoo. When I go into pgAdmin3
> SQLPane, it looks like this:

CREATE TABLE public.gz_life_mammals
(
  "N" integer NOT NULL,
  "Taxon" character varying(50) NOT NULL,
  "Parent" character varying(50) NOT NULL,
  "ParentID" smallint NOT NULL,
  "Slug" character varying(50),
  "NameCommon" character varying(50),
  "Plural" character varying(50),
  "Extinct" smallint NOT NULL,
  "Rank" smallint NOT NULL,
  "Key" smallint NOT NULL,
  CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
  CONSTRAINT "Unique Key" UNIQUE ("Taxon")
[I haven't added any non-unique keys yet.]
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

I should also mention that Taxon is the column I use to UNION or JOIN this
table with other tables.

P.S. If I decide to organize things by taxonomic levels (e.g. kingdom,
class, etc.), then it should be easy to rename the table, delete a few
columns, and refill it with data associated with a particular class.



On Mon, Oct 26, 2015 at 1: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 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 way to organize my data.
>
> On the other hand, I have a column (Rank) that identifies different
> taxonomic levels (kingdom, class, etc.). So I can easily sort a table into
> specific taxonomic levels and save one level at a time for a database table.
>
> There is one problem, though. I can easily put all the vertebrate orders
> and even families into a table. But genera might be harder, and species
> probably won't work; there are simply too many. My spreadsheet program is
> almost overwhelmed by fish species alone. The only solution would be if I
> could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But
> that might be kind of tedious, especially if I have to make multiple
> updates.
>
> As for "attributes," I'll post my table's schema, with a description, next.
>
> On Mon, Oct 26, 2015 at 10:44 AM, Adrian Klaver  > wrote:
>
>> 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 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
>

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 plants as needed.

I couldn't possibly repeat the process with invertebrates or plants, which
are simply overwhelming. So, if I ever figure out the Catalogue of Life's
database, then I'm simply going to modify its tables so they work with my
system. My vertebrates database will override their vertebrate rows (except
for any extra information they have to offer).

As for "hand-entry," I do almost all my work in spreadsheets. I spent a day
or two copying scientific names from the Catalogue of Life into my
spreadsheet. Common names and slugs (common names in a URL format) is a
project that will probably take years. I might type a scientific name or
common name into Google and see where it leads me. If a certain scientific
name is associated with the common name "yellow birch," then its slug
becomes yellow-birch. If two or more species are called yellow birch, then
I enter yellow-birch in a different table ("Floaters"), which leads to a
disambiguation page.

For organisms with two or more popular common names - well, I haven't
really figured that out yet. I'll probably have to make an extra table for
additional names. Catalogue of Life has common names in its database, but
they all have upper case first letters - like American Beaver. That works
fine for a page title but in regular text I need to make beaver lowercase
without changing American. So I'm just starting from square one and
recreating all the common names from scratch.

It gets still more complicated when you get into "specialist names." ;) But
the system I've set up so far seems to be working pretty nicely.

On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent  wrote:

> 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 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 way to organize my data.
>>
>> On the other hand, I have a column (Rank) that identifies different
>> taxonomic levels (kingdom, class, etc.). So I can easily sort a table into
>> specific taxonomic levels and save one level at a time for a database table.
>>
>> There is one problem, though. I can easily put all the vertebrate orders
>> and even families into a table. But genera might be harder, and species
>> probably won't work; there are simply too many. My spreadsheet program is
>> almost overwhelmed by fish species alone. The only solution would be if I
>> could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But
>> that might be kind of tedious, especially if I have to make multiple
>> updates.
>>
>> Yes I suspect you spreadsheet will be limited in rows, but of course you
> can send all the spreadsheets to a single table in the database. If that's
> what you want.  You don't have to, but you see mention of tables millions
> of records routinely.  On the other hand, if performance becomes an issue
> with the single table approach you might want to look at "partitioning".
> But I would be surprised if you had to go there.
>
> What is your data source?  How much hand-entry are you doing? There are
> tools which (seriously) upgrade the basic 'COPY into ' command.
>
>
> As for "attributes," I'll post my table's schema, with a description, next.
>>
>>
>


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

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

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 first, manually adding a few 
key invertebrates and plants as needed.


I couldn't possibly repeat the process with invertebrates or plants, 
which are simply overwhelming. So, if I ever figure out the Catalogue 
of Life's database, then I'm simply going to modify its tables so they 
work with my system. My vertebrates database will override their 
vertebrate rows (except for any extra information they have to offer).


As for "hand-entry," I do almost all my work in spreadsheets. I spent 
a day or two copying scientific names from the Catalogue of Life into 
my spreadsheet. Common names and slugs (common names in a URL format) 
is a project that will probably take years. I might type a scientific 
name or common name into Google and see where it leads me. If a 
certain scientific name is associated with the common name "yellow 
birch," then its slug becomes yellow-birch. If two or more species are 
called yellow birch, then I enter yellow-birch in a different table 
("Floaters"), which leads to a disambiguation page.


For organisms with two or more popular common names - well, I haven't 
really figured that out yet. I'll probably have to make an extra table 
for additional names. Catalogue of Life has common names in its 
database, but they all have upper case first letters - like American 
Beaver. That works fine for a page title but in regular text I need to 
make beaver lowercase without changing American. So I'm just starting 
from square one and recreating all the common names from scratch.


Multiple names can be handled in at least two ways.  A child table of 
species which has species id and alternate name per record - then you 
can get all other-names back by species id.  Of course going from 
altername-name back to species may get you multiple species. Or, welcome 
to postgres' arrays-as-column: you can have one column, maybe called 
aliases which is an array of string.


It gets still more complicated when you get into "specialist names." 
;) But the system I've set up so far seems to be working pretty nicely.


On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent > wrote:


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 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 way to organize my data.

On the other hand, I have a column (Rank) that identifies
different taxonomic levels (kingdom, class, etc.). So I can
easily sort a table into specific taxonomic levels and save
one level at a time for a database table.

There is one problem, though. I can easily put all the
vertebrate orders and even families into a table. But genera
might be harder, and species probably won't work; there are
simply too many. My spreadsheet program is almost overwhelmed
by fish species alone. The only solution would be if I could
import Mammals.csv, then import Birds.csv, Reptiles.csv, etc.
But that might be kind of tedious, especially if I have to
make multiple updates.

Yes I suspect you spreadsheet will be limited in rows, but of
course you can send all the spreadsheets to a single table in the
database. If that's what you want.  You don't have to, but you see
mention of tables millions of records routinely.  On the other
hand, if performance becomes an issue with the single table
approach you might want to look at "partitioning".  But I would be
surprised if you had to go there.

What is your data source?  How much hand-entry are you doing?
There are tools which (seriously) upgrade the basic 'COPY into
' command.


As for "attributes," I'll post my table's schema, with a
description, next.





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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 FROM gz_life_kingclass WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_kingclass WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_mammals WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_mammals WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_birds WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_birds WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_reptiles WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_reptiles WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_amphibians WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_amphibians WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_fish WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_fish WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_fish2 WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_fish2 WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_inverts WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_inverts WHERE Slug = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life_plants WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(Slug) AS num FROM gz_life_plants WHERE Slug = :MyURL
  ) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

switch($Total['num'])
{
 case 1:
 require_once($BaseINC."/2b/inc/C/C_Child.php");
 break;

 case 0:
$sql = "SELECT SUM(num) as num FROM (
  SELECT COUNT(Taxon) AS num FROM gz_life_parataxa WHERE Taxon = :MyURL
  UNION ALL
  SELECT COUNT(URL) AS num FROM gz_life_floaters WHERE URL = :MyURL
  UNION ALL
  SELECT COUNT(URL) AS num FROM gz_life_groups WHERE URL = :MyURL
  ) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();


On Mon, Oct 26, 2015 at 1: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 first, manually adding a few key invertebrates
> and plants as needed.
>
> I couldn't possibly repeat the process with invertebrates or plants, which
> are simply overwhelming. So, if I ever figure out the Catalogue of Life's
> database, then I'm simply going to modify its tables so they work with my
> system. My vertebrates database will override their vertebrate rows (except
> for any extra information they have to offer).
>
> As for "hand-entry," I do almost all my work in spreadsheets. I spent a
> day or two copying scientific names from the Catalogue of Life into my
> spreadsheet. Common names and slugs (common names in a URL format) is a
> project that will probably take years. I might type a scientific name or
> common name into Google and see where it leads me. If a certain scientific
> name is associated with the common name "yellow birch," then its slug
> becomes yellow-birch. If two or more species are called yellow birch, then
> I enter yellow-birch in a different table ("Floaters"), which leads to a
> disambiguation page.
>
> For organisms with two or more popular common names - well, I haven't
> really figured that out yet. I'll probably have to make an extra table for
> additional names. Catalogue of Life has common names in its database, but
> they all have upper case first letters - like American Beaver. That works
> fine for a page title but in regular text I need to make beaver lowercase
> without changing American. So I'm just starting from square one and
> recreating all the common names from scratch.
>
> It gets still more complicated when you get into "specialist names." ;)
> But the system I've set up so far seems to be working pretty nicely.
>
> On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent 
> wrote:
>
>> 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 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 way to organize my data.
>>>
>>> On the other hand, I have a column (Rank) that identifie

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 the command-line tool) to recreate it?


This is what the table's schema looks like in MySQL...

N - int(6) [Primary Key]
Taxon - varchar(50) [Unique Key]
Parent - varchar(50) [Index Key]
ParentID - tinyint(1) [Index Key]
Slug - varchar(50) [Index Key]
NameCommon - varchar(50)
Plural - varchar(50)
Extinct - tinyint(1)
Rank - tinyint(2)
Key - tinyint(1)

The table type is MyIsam, collation is latin1_general_ci

Slug, NameCommon and Plural are NULL.

All of my tables have a default first column named N or ID, which is 
simply a numerical key that begins with 1. It's always designated the 
primary key.


All the other columns in this table can be divided into two 
categories, text (varchar) and numerical (tinyint).


The values in the columns Taxon and Slug serve as URL's, so they can 
have no spaces, apostrophes, accents, etc. (Taxon handles scientific 
names, Slug common names, if any.) So a row focusing on the Steller's 
jay would have values like these:


NameCommmon - Steller’s jay
Plural - Steller’s jays
Taxon - Cyanocitta-stelleri
Slug - stellers-jay
Parent - Cyanocitta

The column ParentID - which I want to use for hierarchical 
relationships - has values ranging from 1 for Mammalia (the first row) 
to 5 for the species level. The column Extinct has the value 1 (not 
extinct) or 2, 3 or 4 for various categories of extinct taxons.


The column Rank has the value 25 for the first row (class Mammalia), 
35 for each order (e.g. Carnivora), 45 for each family, 55 for each 
genus and 65 for each species. The value for Key is 1 (for every row), 
designating it a tetrapod. The bird, reptile and amphibian tables have 
the same key value, while fish, invertebrates and plants have their 
own unique keys.


I have Unique keys on N and Taxon, Index keys (not unique) on Parent, 
ParentID and Slug.


My PostgreSQL table is in a database named GeoZoo. When I go into 
pgAdmin3 > SQLPane, it looks like this:


CREATE TABLE public.gz_life_mammals
(
  "N" integer NOT NULL,
  "Taxon" character varying(50) NOT NULL,
  "Parent" character varying(50) NOT NULL,
  "ParentID" smallint NOT NULL,
  "Slug" character varying(50),
  "NameCommon" character varying(50),
  "Plural" character varying(50),
  "Extinct" smallint NOT NULL,
  "Rank" smallint NOT NULL,
  "Key" smallint NOT NULL,
  CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
  CONSTRAINT "Unique Key" UNIQUE ("Taxon")
[I haven't added any non-unique keys yet.]
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

I should also mention that Taxon is the column I use to UNION or JOIN 
this table with other tables.


P.S. If I decide to organize things by taxonomic levels (e.g. kingdom, 
class, etc.), then it should be easy to rename the table, delete a few 
columns, and refill it with data associated with a particular class.

[...]

Would suggest using lower case column names without embedded spaces, if 
possible!


NEVER assign tables to the postgres user, application tables should be 
owned by a user!


Note that PRIMARY KEY gives you both NON NULL & uniqueness.  So you 
don't need a separate PRIMARY KEY constraint!


'id' would be better than 'N' for the primary key name. ==> 'id int  
PRIMARY KEY'


Using 'text' rather than 'character varying(50)' would probably be better.

Since you are making a single column unique, suggest 'taxon   text 
UNIQUE NOT NULL'


You don't need to specify 'OIDS=FALSE', as that is now the defualt.



Cheers,
Gavin




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

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 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 like in MySQL...
>>
>> N - int(6) [Primary Key]
>> Taxon - varchar(50) [Unique Key]
>> Parent - varchar(50) [Index Key]
>> ParentID - tinyint(1) [Index Key]
>> Slug - varchar(50) [Index Key]
>> NameCommon - varchar(50)
>> Plural - varchar(50)
>> Extinct - tinyint(1)
>> Rank - tinyint(2)
>> Key - tinyint(1)
>>
>> The table type is MyIsam, collation is latin1_general_ci
>>
>> Slug, NameCommon and Plural are NULL.
>>
>> All of my tables have a default first column named N or ID, which is
>> simply a numerical key that begins with 1. It's always designated the
>> primary key.
>>
>> All the other columns in this table can be divided into two categories,
>> text (varchar) and numerical (tinyint).
>>
>> The values in the columns Taxon and Slug serve as URL's, so they can have
>> no spaces, apostrophes, accents, etc. (Taxon handles scientific names, Slug
>> common names, if any.) So a row focusing on the Steller's jay would have
>> values like these:
>>
>> NameCommmon - Steller’s jay
>> Plural - Steller’s jays
>> Taxon - Cyanocitta-stelleri
>> Slug - stellers-jay
>> Parent - Cyanocitta
>>
>> The column ParentID - which I want to use for hierarchical relationships
>> - has values ranging from 1 for Mammalia (the first row) to 5 for the
>> species level. The column Extinct has the value 1 (not extinct) or 2, 3 or
>> 4 for various categories of extinct taxons.
>>
>> The column Rank has the value 25 for the first row (class Mammalia), 35
>> for each order (e.g. Carnivora), 45 for each family, 55 for each genus and
>> 65 for each species. The value for Key is 1 (for every row), designating it
>> a tetrapod. The bird, reptile and amphibian tables have the same key value,
>> while fish, invertebrates and plants have their own unique keys.
>>
>> I have Unique keys on N and Taxon, Index keys (not unique) on Parent,
>> ParentID and Slug.
>>
>> My PostgreSQL table is in a database named GeoZoo. When I go into
>> pgAdmin3 > SQLPane, it looks like this:
>>
>> CREATE TABLE public.gz_life_mammals
>> (
>>   "N" integer NOT NULL,
>>   "Taxon" character varying(50) NOT NULL,
>>   "Parent" character varying(50) NOT NULL,
>>   "ParentID" smallint NOT NULL,
>>   "Slug" character varying(50),
>>   "NameCommon" character varying(50),
>>   "Plural" character varying(50),
>>   "Extinct" smallint NOT NULL,
>>   "Rank" smallint NOT NULL,
>>   "Key" smallint NOT NULL,
>>   CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
>>   CONSTRAINT "Unique Key" UNIQUE ("Taxon")
>> [I haven't added any non-unique keys yet.]
>> )
>> WITH (
>>   OIDS=FALSE
>> );
>> ALTER TABLE public.gz_life_mammals
>>   OWNER TO postgres;
>>
>> I should also mention that Taxon is the column I use to UNION or JOIN
>> this table with other tables.
>>
>> P.S. If I decide to organize things by taxonomic levels (e.g. kingdom,
>> class, etc.), then it should be easy to rename the table, delete a few
>> columns, and refill it with data associated with a particular class.
>>
> [...]
>
> Would suggest using lower case column names without embedded spaces, if
> possible!
>
> NEVER assign tables to the postgres user, application tables should be
> owned by a user!
>
> Note that PRIMARY KEY gives you both NON NULL & uniqueness.  So you don't
> need a separate PRIMARY KEY constraint!
>
> 'id' would be better than 'N' for the primary key name. ==> 'id int
> PRIMARY KEY'
>
> Using 'text' rather than 'character varying(50)' would probably be better.
>
> Since you are making a single column unique, suggest 'taxon   text UNIQUE
> NOT NULL'
>
> You don't need to specify 'OIDS=FALSE', as that is now the defualt.
>
>
>
> Cheers,
> Gavin
>
>
>


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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 top post!


[...]

Top posting is when you put your reply at the top of the post, as you 
did just now in response to my post.


Here I am bottom posting, which is the norm for postgres mailing lists.

Ignore my comment about embedded spaces, I misread what you had written, 
underlines are fine.



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

[Rob Sargent]

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 
> wrote:


Hi David,

Please don't top post!


[...]

Top posting is when you put your reply at the top of the post, as you 
did just now in response to my post.


Here I am bottom posting, which is the norm for postgres mailing lists.

Ignore my comment about embedded spaces, I misread what you had 
written, underlines are fine.
But _really_ recommend lowercase column names and table names.  You'll 
have to quote them every time you want to use them in manual sql or scripts.

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

 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" PRIMARY KEY (id),
  CONSTRAINT "Unique Key" UNIQUE (taxon)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

* * * * *

I don't even have a clue what OIDS=FALSE means; I haven't read up on it
yet. It's just there by default. I haven't figured out how to change the
NULL value for any columns, other than toggle back and forth between NULL
and NOT NULL.

To assign a user, would I just ask it to associate a table with my
username? Can I do that with pgAdmin3?

Thanks.

[GENERAL] Importing CSV File

[David Blomstrom]

I tried to import a CSV file into a PostgreSQL table using pgAdmin III. I
got an error message: "extra data after last column."


All my spreadsheets have an "end of data" column that has /r/n in each
cell. When I import a CSV file into a MySQL table, everything beyond /r/n
is ignored. Is there some way to tell PostgreSQL to stop at /r/n?

Thanks.

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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,
   key smallint NOT NULL,
   CONSTRAINT "Primary Key" PRIMARY KEY (id),
   CONSTRAINT "Unique Key" UNIQUE (taxon)
)
WITH (
   OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
   OWNER TO postgres;

* * * * *

I don't even have a clue what OIDS=FALSE means;


That is not necessary. OIDs on user tables are no longer automatically 
included, so that is FALSE by default. OID is object id. In the old days 
it used to be a default hidden column on all tables. That turned out not 
to be a good idea, so they are no longer there for user tables. You will 
see then on system tables if you specifically do select oid, * from 
some_system_table.


I haven't read up on it

yet. It's just there by default. I haven't figured out how to change the
NULL value for any columns, other than toggle back and forth between
NULL and NOT NULL.


Is this what you are talking about?:

ALTER TABLE some_table ALTER COLUMN SET NOT NULL

or

ALTER TABLE some_table ALTER COLUMN DROP NOT NULL

http://www.postgresql.org/docs/9.4/interactive/sql-altertable.html



To assign a user, would I just ask it to associate a table with my
username? Can I do that with pgAdmin3?

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

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 smallint NOT NULL,
  CONSTRAINT "Primary Key" PRIMARY KEY (id),
  CONSTRAINT "Unique Key" UNIQUE (taxon)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

* * * * *

I don't even have a clue what OIDS=FALSE means; I haven't read up on 
it yet. It's just there by default. I haven't figured out how to 
change the NULL value for any columns, other than toggle back and 
forth between NULL and NOT NULL.


To assign a user, would I just ask it to associate a table with my 
username? Can I do that with pgAdmin3?


Thanks.

Hi David,

Constructing SQL in an editor and executing the SQL script using psql is 
often a lot easier than using pgadmin3, and gives you far more control!  
I use both, but more often use psql.


From the postgres user and using psql, you can create a user & database 
like:


CREATE ROLE gavin
LOGIN
CREATEDB;

CREATE DATABASE gavin
OWNER gavin;


Obviously, you can create a database with a different name for the same 
user.  Just that the above means that if you call up psql from a 
terminal of that user, you don't need to explicitly tell it what 
database to use.


I created an SQL script create_table.sql (usually better to have a more 
descriptive name!) in an editor:


CREATE TABLE public.gz_life_mammals
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  extinct   smallint NOT NULL,
  rank  smallint NOT NULL,
  key   smallint NOT NULL
);

Here is a session where I create the table (I created the terminal in 
the same directory as the SQL script, you can also simply cd to the 
relevant directory before executing psql):

$ psql
psql (9.4.4)
Type "help" for help.

gavin=> \i create_table.sql
CREATE TABLE
gavin=> \q
$

You might be able to do all the above using pgadmin3...


Cheers,
Gavin



--
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/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 first, manually adding a few key
invertebrates and plants as needed.

I couldn't possibly repeat the process with invertebrates or plants,
which are simply overwhelming. So, if I ever figure out the Catalogue of
Life's database, then I'm simply going to modify its tables so they work
with my system. My vertebrates database will override their vertebrate
rows (except for any extra information they have to offer).

As for "hand-entry," I do almost all my work in spreadsheets. I spent a
day or two copying scientific names from the Catalogue of Life into my
spreadsheet. Common names and slugs (common names in a URL format) is a
project that will probably take years. I might type a scientific name or
common name into Google and see where it leads me. If a certain
scientific name is associated with the common name "yellow birch," then
its slug becomes yellow-birch. If two or more species are called yellow
birch, then I enter yellow-birch in a different table ("Floaters"),
which leads to a disambiguation page.

For organisms with two or more popular common names - well, I haven't
really figured that out yet. I'll probably have to make an extra table
for additional names. Catalogue of Life has common names in its
database, but they all have upper case first letters - like American
Beaver. That works fine for a page title but in regular text I need to
make beaver lowercase without changing American. So I'm just starting
from square one and recreating all the common names from scratch.


I think there has to be a better way as this is just a formatting issue 
Can't remember what programming language you are working in, but in Python:


In [13]: s = 'American Beaver'

In [14]: s.capitalize()
Out[14]: 'American beaver'

In [15]: s.lower()
Out[15]: 'american beaver'



It gets still more complicated when you get into "specialist names." ;)
But the system I've set up so far seems to be working pretty nicely.

On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent mailto:robjsarg...@gmail.com>> wrote:

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 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 way
to organize my data.

On the other hand, I have a column (Rank) that identifies
different taxonomic levels (kingdom, class, etc.). So I can
easily sort a table into specific taxonomic levels and save one
level at a time for a database table.

There is one problem, though. I can easily put all the
vertebrate orders and even families into a table. But genera
might be harder, and species probably won't work; there are
simply too many. My spreadsheet program is almost overwhelmed by
fish species alone. The only solution would be if I could import
Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But that
might be kind of tedious, especially if I have to make multiple
updates.

Yes I suspect you spreadsheet will be limited in rows, but of course
you can send all the spreadsheets to a single table in the database.
If that's what you want.  You don't have to, but you see mention of
tables millions of records routinely.  On the other hand, if
performance becomes an issue with the single table approach you
might want to look at "partitioning".  But I would be surprised if
you had to go there.

What is your data source?  How much hand-entry are you doing? There
are tools which (seriously) upgrade the basic 'COPY into '
command.


As for "attributes," I'll post my table's schema, with a
description, next.





--
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] Importing CSV File

[Adrian Klaver]

On 10/26/2015 02:45 PM, David Blomstrom wrote:

I tried to import a CSV file into a PostgreSQL table using pgAdmin III.
I got an error message: "extra data after last column."


All my spreadsheets have an "end of data" column that has /r/n in each
cell. When I import a CSV file into a MySQL table, everything beyond
/r/n is ignored. Is there some way to tell PostgreSQL to stop at /r/n?


Is it really /r/n not \r\n?



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

Re: [GENERAL] Importing CSV File

[Gavin Flower]

On 27/10/15 10:45, David Blomstrom wrote:
I tried to import a CSV file into a PostgreSQL table using pgAdmin 
III. I got an error message: "extra data after last column."



All my spreadsheets have an "end of data" column that has /r/n in each 
cell. When I import a CSV file into a MySQL table, everything beyond 
/r/n is ignored. Is there some way to tell PostgreSQL to stop at /r/n?


Thanks.
You can import the CSV into a spreadsheet (such as LibreOffice Calc), 
and manually delete the extraneous lines.




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

 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 NOT NULL,
  key smallint NOT NULL,
  CONSTRAINT "Unique Key" UNIQUE (taxon)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

[GENERAL] PAM LDAP CREATE USER

[Aaron_Wright]

I recently upgraded from 8.4 to 9.3, and my custom LDAP PAM module no 
longer works. 
In brief, my LDAP PAM module authenticates a centralized user and then 
creates a matching database user, using a separate super user connection 
to the database, before returning successfully from the PAM module. This 
used to work beautifully, but now I get a FATAL error, "role %s does not 
exist". 
Did I depend on a "feature" that got fixed at some point? Is this an issue 
with the connection already being started, so no new users are considered? 
Is there a way to make this work? 

Re: [GENERAL] Importing CSV File

[Adrian Klaver]

On 10/26/2015 02:45 PM, David Blomstrom wrote:

I tried to import a CSV file into a PostgreSQL table using pgAdmin III.
I got an error message: "extra data after last column."


All my spreadsheets have an "end of data" column that has /r/n in each
cell. When I import a CSV file into a MySQL table, everything beyond
/r/n is ignored. Is there some way to tell PostgreSQL to stop at /r/n?


No Postgres will not stop at /r/n. The specifics of what goes on can be 
found here:


http://www.postgresql.org/docs/9.4/interactive/sql-copy.html

Short version, you can import a CSV file with fewer fields then the 
table you are importing to but not the other way around. There has to be 
 a place for all the CSV fields in the table.




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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

 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.gz_life_mammals2
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  extinct   smallint NOT NULL,
  rank  smallint NOT NULL,
  key   smallint NOT NULL
);

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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 are no new databases.


No LOGIN is an attribute of ROLE it is not the password


And when I paste this in, it doesn't create a table...

CREATE TABLE public.gz_life_mammals2
(
   idint PRIMARY KEY,
   taxon text UNIQUE NOT NULL,
   parenttext NOT NULL,
   slug  text,
   name_common   text,
   pluraltext,
   extinct   smallint NOT NULL,
   rank  smallint NOT NULL,
   key   smallint NOT NULL
);



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

[Gavin Flower]

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 way to organize my data.


On the other hand, I have a column (Rank) that identifies different 
taxonomic levels (kingdom, class, etc.). So I can easily sort a table 
into specific taxonomic levels and save one level at a time for a 
database table.


There is one problem, though. I can easily put all the vertebrate 
orders and even families into a table. But genera might be harder, and 
species probably won't work; there are simply too many. My spreadsheet 
program is almost overwhelmed by fish species alone.

[...]

The maximum number of rows in the LibreOffice spreadsheet (Calc) is 
1,073,741,824


LibreOffice is free and available for Apple Macs (as well as for Linux & 
Microsoft O/S's), see

http://www.libreoffice.org/download
I suggest you download the 5.0 version


Cheers,
Gavin



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

[GENERAL] how to insert stream into table using nodejs?

[Leonardo]

Hello all,

i'm looking for a way to insert a file into a table using available binding
for nodejs.

just for comparison, if i where using java on server the upload code would
be like this:

protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
cors(response);
LOG.info("POST started: " + request.getContentType());
Collection parts = request.getParts();
for (Part part : parts) {
long len = part.getSize();
String name = part.getName();
LOG.info("attepmt to upload " + name + " from "
+ request.getRemoteAddr());
if ("file".equals(name)) {
String mime = part.getContentType();
try (InputStream in = part.getInputStream()) {
String q = "insert into media (mediamime,mediadata) values (?,?)";
try (Connection con = ds.getConnection()) {
try (PreparedStatement ps = //
con.prepareStatement(q, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, mime);
ps.setBinaryStream(2, in, len);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()){
String m = ""+rs.getInt(1);
LOG.info("new media: "+m);
response.getWriter().write(m);
}
}
}
} catch (Exception e) {
LOG.severe(e.getMessage());
e.printStackTrace();
response.setStatus(500);
response.getWriter().write(e.toString());
}
}
}
}
}

Attention for ps.setBinaryStream(2, in, len); part.

Im' looking for a equivalent to this using pg, knex, anything capable of
this in the nodejs ecossytem.

Thanks in advance.

Re: [GENERAL] PAM LDAP CREATE USER

[Tom Lane]

aaron_wri...@selinc.com writes:
> I recently upgraded from 8.4 to 9.3, and my custom LDAP PAM module no 
> longer works. 

8.4.what and 9.3.what?  Have you checked the behavior in any other releases?

> In brief, my LDAP PAM module authenticates a centralized user and then 
> creates a matching database user, using a separate super user connection 
> to the database, before returning successfully from the PAM module. This 
> used to work beautifully, but now I get a FATAL error, "role %s does not 
> exist". 

That seems mighty Rube Goldbergian ... but it's not clear why it used to
work and doesn't anymore.  If you'd said 9.4 I'd have guessed at a corner
case in catalog snapshot invalidation, but I think 9.3 would just be
looking for the role with SnapshotNow, which should pretty much always
work.  (You're sure the transaction in the background is getting committed
in time, right?  And it's being sent to the 9.3 DB not the 8.4 one?)

Also, just to clarify: this is a PAM auth module that just happens to talk
to some LDAP server behind the scenes, right?  If Postgres thinks this is
LDAP auth method then some other possibilities open up --- but AFAICS
we've not touched the PAM code since 8.4.2.

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

[Gavin Flower]

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 doesn't create a table...

CREATE TABLE public.gz_life_mammals2
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  extinct   smallint NOT NULL,
  rank  smallint NOT NULL,
  key   smallint NOT NULL
);
You can list all the databases with '\l' and the tables with '\dt' in 
psql, for example:


$ psql
psql (9.4.4)
Type "help" for help.

gavin=> \l
   List of databases
Name|   Owner| Encoding |   Collate |Ctype
|   Access privileges

++--+-+-+---
 gavin  | gavin  | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 greeter_quickstart | jboss_dev  | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 jboss_dev_db   | jboss_dev  | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 jboss_sys_db   | jboss_sys  | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 jboss_test_db  | jboss_test | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 mydb   | mydb_admin | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 pgsp2ed| gavin  | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres   | postgres   | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 postgresql_book| gavin  | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 template0  | postgres   | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 
| =c/postgres  +
||  | | | 
postgres=CTc/postgres
 template1  | postgres   | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 
| =c/postgres  +
||  | | | 
postgres=CTc/postgres

(11 rows)

gavin=> \c pgsp2ed
You are now connected to database "pgsp2ed" as user "gavin".
pgsp2ed=> \dt
  List of relations
 Schema |   Name   | Type  |  Owner
+--+---+--
 public | accounts | table | gavin
 public | application_settings_new | table | gavin
 public | application_settings_old | table | gavin
 public | fiverow  | table | gavin
 public | fiverows | table | gavin
 public | fruit| table | gavin
 public | fruit_in_stock   | table | gavin
 public | fruit_offer  | table | gavin
 public | modified_table   | table | gavin
 public | modify_test  | table | gavin
 public | my_data  | table | gavin
 public | name | table | gavin
 public | names| table | gavin
 public | namex| table | gavin
 public | notify_test  | table | gavin
 public | original_database| table | gavin
 public | original_user| table | gavin
 public | salary   | table | fred
 public | salary_change_log| table | fred
 public | test | table | postgres
 public | test1| table | gavin
 public | test2| table | gavin
 public | test3| table | gavin
 public | tmp_setting  | table | gavin
 public | word | table | gavin
(25 rows)

pgsp2ed=> \q
$



Cheers,
Gavin


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

[Gavin Flower]

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/mailpref/pgsql-general

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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
>>
> [...]
>
> Would strongly advise NOT using capital letters in names of databases in
> pg!
>
>


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

Re: [GENERAL] PAM LDAP CREATE USER

[Aaron_Wright]

> From: Tom Lane 
> aaron_wri...@selinc.com writes:
> > I recently upgraded from 8.4 to 9.3, and my custom LDAP PAM module no 
> > longer works. 
> 
> 8.4.what and 9.3.what?

8.4.16 to 9.3.4 

> Have you checked the behavior in any other releases?

Not yet. I was interested in getting a laugh test from the mailing list 
first; to see if I was completely off my rocker or not.

> > In brief, my LDAP PAM module authenticates a centralized user and then 

> > creates a matching database user, using a separate super user 
connection 
> > to the database, before returning successfully from the PAM module. 
This 
> > used to work beautifully, but now I get a FATAL error, "role %s does 
not 
> > exist". 
> 
> That seems mighty Rube Goldbergian

>From what I've researched this is the only way to accomplish what I'm 
trying to. Everything I read online keeps telling me that in order for 
LDAP to work with postgresql, the user must already exist in the database. 
Most of the workarounds for this, involve a cron job that sucks up the 
entire directory of users and creates matching users in the database 
periodically.

That seems a little crazy to me, so I have a PAM LDAP module which creates 
the users on the fly.

> ... but it's not clear why it used to
> work and doesn't anymore.  If you'd said 9.4 I'd have guessed at a 
corner
> case in catalog snapshot invalidation, but I think 9.3 would just be
> looking for the role with SnapshotNow, which should pretty much always
> work.  (You're sure the transaction in the background is getting 
committed
> in time, right?  And it's being sent to the 9.3 DB not the 8.4 one?)

The PAM LDAP module uses PQconnectdb to create a super user connection to 
the database. It uses PQexec to run "CREATE USER 'user' PASSWORD NULL IN 
ROLE 'role';". And finishes up with a PQfinish before PAM_SUCCESS is 
returned to postgres. I'm a bit limited in my database knowledge, so 
please let me know if that sequence is leaving something dangling. I see 
the "CREATE USER" query in the pg_log file.

Also, if I try to log in a second time, it works fine. This is presumably 
because the user now exists.

> Also, just to clarify: this is a PAM auth module that just happens to 
talk
> to some LDAP server behind the scenes, right?  If Postgres thinks this 
is
> LDAP auth method then some other possibilities open up --- but AFAICS
> we've not touched the PAM code since 8.4.2.

You're correct, this is a PAM auth module that handles talking to the LDAP 
server and authenticating the user.

pg_hba.conf line includes "host all all 0.0.0.0/0 pam pamservice=..." and 
there's a matching pam configuration file.

I'm not familiar with the "LDAP auth method", but I don't think I can use 
that as the documents say, "user must already exist" in that situation, 
which is the same problem I'm trying to fix.

>  regards, tom lane

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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:


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!




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


What does '\l' and '\du' actually give you (gives us the actual output)?


--
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/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 from 
the terminal.


So something like:

aklaver@panda:~> psql -d test -U aklaver -h localhost

psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)

Type "help" for help.

test=>

test=> \l
  List of databases
  Name   |   Owner| Encoding |   Collate   |Ctype| 
  Access privileges

-++--+-+-+---
 business_app| app_admin  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 hplc| hplc_admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 postgres| postgres   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 production  | postgres   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0   | postgres   | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
=c/postgres  +
 ||  | | | 
postgres=CTc/postgres
 template1   | postgres   | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
=c/postgres  +
 ||  | | | 
postgres=CTc/postgres

 test| postgres   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 test_hplc   | hplc_admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 test_production | postgres   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 track_stocks| aklaver| UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(10 rows)

test=> \dt
List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | app_sessions | table | postgres
 public | app_users| table | postgres
 public | cp_test  | table | aklaver
 public | data | table | aklaver
 public | date_test| table | postgres
 public | excl_test| table | aklaver
 public | fk_rel   | table | postgres
 public | foo  | table | aklaver
 public | inet_test| table | postgres
 public | json_test| table | postgres
 public | lat_long | table | postgres
 public | orig_test| table | aklaver
 public | pk_rel   | table | postgres
 public | pr_test  | table | aklaver
 public | rowtype_test | table | aklaver
 public | sub_item | table | postgres
 public | ts_test  | table | postgres
 public | users| table | aklaver




On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower
mailto:gavinflo...@archidevsys.co.nz>>
wrote:

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!




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

[David Blomstrom]

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:

Press  to continue.../l

logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.

[Process completed]

* * * * *

Username [postgres]: /dt

psql: invalid port number: "/dt"

Press  to continue...

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 doesn't create a table...

CREATE TABLE public.gz_life_mammals2
(
  idint PRIMARY KEY,
  taxon text UNIQUE NOT NULL,
  parenttext NOT NULL,
  slug  text,
  name_common   text,
  pluraltext,
  extinct   smallint NOT NULL,
  rank  smallint NOT NULL,
  key   smallint NOT NULL
);

trying typing into psql, assuming this is a postgres user session:
\c geozoo2 david
(format is '\c DATABASE USERID').

If there is an error, please copy & paste the exact error message returned!



--
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/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 - sometimes over half a dozen. If I type the same things into
one of the other instances, I get this:

Press  to continue.../l

logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.

[Process completed]

* * * * *

Username [postgres]: /dt

psql: invalid port number: "/dt"

Press  to continue...





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

 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

[Adrian Klaver]

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

Database [postgres]:


*Shell2*

Database [postgres]: \l

Port [5432]:

* * * * *

Port [5432]: \dt

Username [postgres]:





--
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] Importing CSV File

[David Blomstrom]

Wow, I must be getting dyxlexic; yes, it is

\r\n

I guess it would be easy enough to just copy the stuff I want to import
into a separate spreadsheet and save it as a CSV. I've done that before
with really big spreadsheets, actually. Thanks.

Re: [GENERAL] Recursive Arrays 101

[David G. Johnston]

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]:
>
> * * * * *
>
> Port [5432]: \dt
>
> Username [postgres]:
>
>
> ​Please realize that what you show above is non-standard.  What it appears
to be doing is walking you through the process of connecting to a database
in a step-by-step manner.  What you are showing are prompts asking for a
specific piece of data.  The first prompt is asking for the host you wish
to connect to with a default value of localhost.  Then port, with a default
of 5432.  Database, default postgres.  Username, default postgres.
Assuming all of the defaults are valid what happens if you simply hit enter
for each of the prompts until you get to a prompt that is for something
other than a setting with a default value?

Typing in \l or \dt at one of the above prompts is non-sensicial.  Whatever
you are running is not yet ready to accept generic commands.  Likely even
when the prompting is completed what you end up with will not be a generic
psql shell where these meta-commands will have meaning.

You should learn what "Shell" (its seems more like an application) you are
using and ask whomever wrote it what you should be doing.

David J.
​

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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 [localhost]: \l
>>
>> Database [postgres]:
>>
>> * * * * *
>>
>> Server [localhost]: \dt
>>
>> Database [postgres]:
>>
>>
>> *Shell2*
>>
>> Database [postgres]: \l
>>
>> Port [5432]:
>>
>> * * * * *
>>
>> Port [5432]: \dt
>>
>> Username [postgres]:
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


When I click on the SQL Shell (PSQL) icon, it opens two instances. This
time, they both gave the same results when I typed in \l and \dt...

Server [localhost]: \l

Database [postgres]: \dt

Port [5432]:

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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: "dt"


Press  to continue...



In my psql seesion I get...

gavin=> \c mydb mydb_admin
You are now connected to database "mydb" as user "mydb_admin".
mydb=>


If you have multiple psql sessions, suggest you delete all except 1 or 2.

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 you & the mailing 
list!



--
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/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 you & the 
mailing list! 


he said some time ago, he's running MS Windows, with EnterpriseDB's 
installation package of PostgreSQL.   'SQL Shell' is a Start Menu 
item/shortcut that references a batch script, 
D:\PostgreSQL\9.3\scripts\runpsql.bat   which in turn looks like...


@echo off
REM Copyright (c) 2012-2014, EnterpriseDB Corporation.  All rights reserved

REM PostgreSQL server psql runner script for Windows

SET server=localhost
SET /P server="Server [%server%]: "

SET database=postgres
SET /P database="Database [%database%]: "

SET port=5432
SET /P port="Port [%port%]: "

SET username=postgres
SET /P username="Username [%username%]: "

for /f "delims=" %%a in ('chcp ^|find /c "932"') do @ SET 
CLIENTENCODING_JP=%%a

if "%CLIENTENCODING_JP%"=="1" SET PGCLIENTENCODING=SJIS
if "%CLIENTENCODING_JP%"=="1" SET /P PGCLIENTENCODING="Client Encoding 
[%PGCLIENTENCODING%]: "


REM Run psql
"D:\PostgreSQL\9.3\bin\psql.exe" -h %server% -U %username% -d %database% 
-p %port%


pause

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

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 at least as many as a dozen.

Sometimes a window will stop working: nothing happens when I type something
and hit enter. In that event, I have to force close PSQL and start from
scratch.

When I open pgAdmin III, a second icon appears on the taskbar. However, it
appears that they're one and the same; just a fluke, I guess.

On Mon, Oct 26, 2015 at 4:22 PM, Gavin Flower  wrote:

> 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: "dt"
>>
>>
>> Press  to continue...
>>
>>
> In my psql seesion I get...
>
> gavin=> \c mydb mydb_admin
> You are now connected to database "mydb" as user "mydb_admin".
> mydb=>
>
>
> If you have multiple psql sessions, suggest you delete all except 1 or 2.
>
> 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 you & the mailing
> list!
>



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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 at least as many as a dozen.


Sometimes a window will stop working: nothing happens when I type 
something and hit enter. In that event, I have to force close PSQL and 
start from scratch.


thats VERY odd.   I just used SQL Shell (psql) on my Windows system, I 
get one window with that batch file prompting for host etc...


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

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 the email addresses of the other helping you & the
mailing list!


he said some time ago, he's running MS Windows, with EnterpriseDB's
installation package of PostgreSQL.   'SQL Shell' is a Start Menu
item/shortcut that references a batch script,


Actually OS X :

http://www.postgresql.org/message-id/CAA54Z0hdYkqDDYP=8s577rwvz4qrn9+-mjkeyrot69um3ra...@mail.gmail.com


--
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 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:30 PM, John R Pierce  wrote:

> 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 at least as many as a dozen.
>>
>> Sometimes a window will stop working: nothing happens when I type
>> something and hit enter. In that event, I have to force close PSQL and
>> start from scratch.
>>
>
> thats VERY odd.   I just used SQL Shell (psql) on my Windows system, I get
> one window with that batch file prompting for host etc...
>
> --
> 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
>



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

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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-Pro-2:~ davidblomstrom$
/Library/PostgreSQL/9.5/scripts/runpsql.sh; exit

Server [localhost]:



On Mon, Oct 26, 2015 at 4:33 PM, Adrian Klaver 
wrote:

> 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 the email addresses of the other helping you & the
>>> mailing list!
>>>
>>
>> he said some time ago, he's running MS Windows, with EnterpriseDB's
>> installation package of PostgreSQL.   'SQL Shell' is a Start Menu
>> item/shortcut that references a batch script,
>>
>
> Actually OS X :
>
>
> http://www.postgresql.org/message-id/CAA54Z0hdYkqDDYP=8s577rwvz4qrn9+-mjkeyrot69um3ra...@mail.gmail.com
>
>
> --
> 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
>



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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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 using to get the below and explain
where you are running it.


*Shell1*

Server [localhost]: \l

Database [postgres]:

* * * * *

Server [localhost]: \dt

Database [postgres]:


*Shell2*

Database [postgres]: \l

Port [5432]:

* * * * *

Port [5432]: \dt

Username [postgres]:




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



When I click on the SQL Shell (PSQL) icon, it opens two instances. This
time, they both gave the same results when I typed in \l and \dt...

Server [localhost]: \l

Database [postgres]: \dt

Port [5432]:



Alright so it is prompting for the connection information. Just hit 
enter to each prompt, it might ask for a password after the above. At 
that point you should actually be in psql and then can run the schema 
creation commands.


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

[Rob Sargent]

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 localhost

this should connect you to the db, I'm not sure what the prompt looks like

\l
will list the databases

\c 
\dt will list tables

Re: [GENERAL] Recursive Arrays 101

[Gavin Flower]

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 
enter, it spawns more windows. I've counted at least as many as a dozen.



[...]

Please DO NOT top post!!!

I use both GUI's & terminals.

The 'don't worry your tiny little mind' philosophy of Apple is an 
anathema to me - it tries to hide too much of the useful stuff from 
users.  I use the 'Mate' Desktop (http://mate-desktop.org) Environment 
from Fedora Linux - I have 35 virtual desktops each with 2 highly 
configured panels that auto hide, and both my terminals and directory 
windows allow multiple tabs (just like web browsers). Apple does not 
permit such extensive customisation.


To get a proper terminal in an Apple Mac, have a look at:

https://en.wikipedia.org/wiki/Terminal_%28OS_X%29
http://www.macworld.co.uk/feature/mac-software/get-more-out-of-os-x-terminal-3608274

Note that it uses the bash shell, see:
https://www.gnu.org/software/bash


--
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 G. Johnston]

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
>
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Davids-MacBook-Pro-2:~ davidblomstrom$
> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>
> Server [localhost]:
>

​So your Mac has this same connection helper script as Windows.  Once you
get connected to the actual "psql" prompt you can start entering the
commands you were given.  The correct values for the prompts would depend
on your setup but it cannot hurt to simply accept all defaults and see what
happens.

David J.
​

Re: [GENERAL] Recursive Arrays 101

[David Blomstrom]

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: could not translate host name "l" to address: nodename nor
servname provided, or not known


Press  to continue...

logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.


[Process completed]


* * * * * *


After that it froze; I can't type anything else.

[GENERAL] Red Hat Policies Regarding PostgreSQL

[Michael Convey]

>From ​http://www.postgresql.org/download/linux/redhat/ comes the following
quote:

--
Due to policies for Red Hat family distributions, the PostgreSQL
installation will not be enabled for automatic start or have the database
initialized automatically. To make your database installation complete, you
need to perform these two steps:

service postgresql initdb
chkconfig postgresql on

or, on Fedora 19 and other later derived distributions:

postgresql-setup initdb
systemctl enable postgresql.service
--

To which policies are they referring? Licensing, security, or other?​

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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]:

Username [postgres]: Cmd-Spacebar


You are mixing instructions. Do:

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

where you just hit Enter after each prompt above. The [parameter]s are 
default values and I would just accept them for now. Once you get 
through the prompts it will open a terminal with psql loaded.





Terminalpsql: could not translate host name "l" to address: nodename nor
servname provided, or not known


Press  to continue...

logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.


[Process completed]


* * * * * *


After that it froze; I can't type anything else.





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

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; exit
>>
>> Server [localhost]: \l
>>
>> Database [postgres]: \dt
>>
>> Port [5432]:
>>
>> Username [postgres]: Cmd-Spacebar
>>
>
> You are mixing instructions. Do:
>
> Server [localhost]:
>
> Database [postgres]:
>
> Port [5432]:
>
> Username [postgres]:
>
> where you just hit Enter after each prompt above. The [parameter]s are
> default values and I would just accept them for now. Once you get through
> the prompts it will open a terminal with psql loaded.
>
>

Last login: Mon Oct 26 16:48:35 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]: Server [localhost]:

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press  to continue...CREATE TABLE public.gz_life_mammals2

(

  idint PRIMARY KEY,

  taxon text UNIQUE NOT NULL,

  parenttext NOT NULL,

  slug  text,

  name_common   text,

  pluraltext,

  extinct   smallint NOT NULL,

  rank  smallint NOT NULL,

  key   smallint NOT NULL

);logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.


[Process completed]


* * * * *

@ Rob Sargent - I already have two databases, postrgres and geozoo. Geozoo
has two tables in it. I was just trying to create another table with the
shell (PSQL).

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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


You are mixing instructions. Do:

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

where you just hit Enter after each prompt above. The [parameter]s
are default values and I would just accept them for now. Once you
get through the prompts it will open a terminal with psql loaded.

Last login: Mon Oct 26 16:48:35 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]: Server [localhost]:

Database [postgres]: Database [postgres]:

Port [5432]: Port [5432]:

Username [postgres]: Username [postgres]:


So hitting Enter created the second prompt on each line?



psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Well this went nowhere.




Press  to continue...CREATE TABLE public.gz_life_mammals2

(

   idint PRIMARY KEY,

   taxon text UNIQUE NOT NULL,

   parenttext NOT NULL,

   slug  text,

   name_common   text,

   pluraltext,

   extinct   smallint NOT NULL,

   rank  smallint NOT NULL,

   key   smallint NOT NULL

);logout

Saving session...

...copying shared history...

...saving history...truncating history files...

...completed.


[Process completed]


* * * * *

@ Rob Sargent - I already have two databases, postrgres and geozoo.
Geozoo has two tables in it. I was just trying to create another table
with the shell (PSQL).




--
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 G. Johnston]

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

[David Blomstrom]

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 in at the very beginning
meeting, maybe I can leave a note for them. If I can hire someone to set up
PostgreSQL for me, create a database and table and publish them online, so
it's actually working, and I know how to repeat what they did, then it
might work out.

Otherwise, PostgreSQL is obviously too advanced for me. I don't have a clue
about what's going on. Thanks for all the tips, though. I got some good
general pointers for designing my animals database.

On Mon, Oct 26, 2015 at 5:20 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom <
> david.blomst...@gmail.com> 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.
>
>


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

Re: [GENERAL] Recursive Arrays 101

[John R Pierce]

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 make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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 above prompts, do NOT enter any 
values.




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 in at the very
beginning meeting, maybe I can leave a note for them. If I can hire
someone to set up PostgreSQL for me, create a database and table and
publish them online, so it's actually working, and I know how to repeat
what they did, then it might work out.

Otherwise, PostgreSQL is obviously too advanced for me. I don't have a
clue about what's going on. Thanks for all the tips, though. I got some
good general pointers for designing my animals database.


The above really has nothing to do with Postgres as you have not 
actually got to it yet.




On Mon, Oct 26, 2015 at 5:20 PM, David G. Johnston
mailto:david.g.johns...@gmail.com>> wrote:

On Mon, Oct 26, 2015 at 8:07 PM, David Blomstrom
mailto:david.blomst...@gmail.com>>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.




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

[Gavin Flower]

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

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]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press  to continue...

On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower  wrote:

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

Re: [GENERAL] Recursive Arrays 101

[David G. Johnston]

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 [localhost
>
> Database [postgres]: Database [postgres]:
>
> Port [5432]: Port [5432]:
>
> Username [postgres]: Username [postgres]:
>
> psql: warning: extra command-line argument "[5432]:" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: warning: extra command-line argument "Database" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: invalid port number: "Port"
>
>
> ​Then the script you are running is broken and you should run "psql"
yourself from a Mac terminal prompt.

David J.
​

Re: [GENERAL] Recursive Arrays 101

[Melvin Davidson]

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 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]:
>
> Username [postgres]: Username [postgres]:
>
> psql: warning: extra command-line argument "[5432]:" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: warning: extra command-line argument "Database" ignored
>
> psql: warning: extra command-line argument "[postgres]:" ignored
>
> psql: invalid port number: "Port"
>
>
> Press  to continue...
>
> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <
> gavinflo...@archidevsys.co.nz> wrote:
>
>> 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-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
>



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

Re: [GENERAL] Recursive Arrays 101

[Adrian Klaver]

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 --username postgres --host localhost





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]:

Username [postgres]: Username [postgres]:

psql: warning: extra command-line argument "[5432]:" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: warning: extra command-line argument "Database" ignored

psql: warning: extra command-line argument "[postgres]:" ignored

psql: invalid port number: "Port"


Press  to continue...


On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower
mailto:gavinflo...@archidevsys.co.nz>>
wrote:

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



--
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] Red Hat Policies Regarding PostgreSQL

[Tom Lane]

Michael Convey  writes:
> Due to policies for Red Hat family distributions, the PostgreSQL
> installation will not be enabled for automatic start or have the database
> initialized automatically.

> To which policies are they referring? Licensing, security, or other?​

Packaging policy: daemons shall not run merely by virtue of having been
installed.  Otherwise, if you install a boatload of software without
checking each package, you'd have a boatload of probably-unwanted and
possibly-incorrectly-configured daemons running.  Which is a performance
problem and likely a security hazard too.

It's a good policy IMO (though I used to work there so no doubt I've just
drunk too much Red Hat koolaid).

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

[David Blomstrom]

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 to see it compete with Microsoft - but the Linux community never
could understand the concept of "user friendly."

I get far more service from my Mac than I ever got from M$, and I won't
waste my time with any software that isn't Mac-compatible.

On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson 
wrote:

> 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 <
> david.blomst...@gmail.com> 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 [localhost
>>
>> Database [postgres]: Database [postgres]:
>>
>> Port [5432]: Port [5432]:
>>
>> Username [postgres]: Username [postgres]:
>>
>> psql: warning: extra command-line argument "[5432]:" ignored
>>
>> psql: warning: extra command-line argument "[postgres]:" ignored
>>
>> psql: warning: extra command-line argument "Database" ignored
>>
>> psql: warning: extra command-line argument "[postgres]:" ignored
>>
>> psql: invalid port number: "Port"
>>
>>
>> Press  to continue...
>>
>> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower <
>> gavinflo...@archidevsys.co.nz> wrote:
>>
>>> 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-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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



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