[GENERAL] unnesting hstore data
Hi,
I'm new to hstore type and I couldn't figure out how to use
each(hstore) so I am using akeys() and avals() along with unnest().
Here is a simple example:
db=> CREATE TABLE pktest (id int8 PRIMARY KEY, tags hstore) ;
CREATE TABLE
db=> INSERT INTO pktest VALUES (1, '"key 1"=>"value 1","key 2"=>"value
2","key 100"=>"value 100","key 5150"=>"value 5150"');
INSERT 0 1
db=> INSERT INTO pktest VALUES (2, '"key 1"=>"value 1","key 2"=>"value
2","key 99"=>"value 99","key 100"=>"value 100","key 5150"=>"value
5150",name=>"id 2"');
INSERT 0 1
db=> SELECT id,tags->'name' "name",
db->unnest(akeys(tags)) "key",
db->unnest(avals(tags)) "val"
db-> FROM pktest
db-> ORDER BY id,key;
id | name | key|val
+--+--+
1 | | key 1| value 1
1 | | key 100 | value 100
1 | | key 2| value 2
1 | | key 5150 | value 5150
2 | id 2 | key 1| value 1
2 | id 2 | key 100 | value 100
2 | id 2 | key 2| value 2
2 | id 2 | key 5150 | value 5150
2 | id 2 | key 99 | value 99
2 | id 2 | name | id 2
(10 rows)
The idea being to eventually run queries like this:
db=> WITH foo AS (
db(> SELECT id,tags->'name' "name",
db(> unnest(akeys(tags)) "key",
db(> unnest(avals(tags)) "val"
db(> FROM pktest
db(>WHERE (tags->'name') IS NOT NULL
db(> ) SELECT * FROM foo
db->WHERE key IN ('key 2', 'key 5150', 'duck')
db-> ORDER BY id,key ;
id | name | key|val
+--+--+
2 | id 2 | key 2| value 2
2 | id 2 | key 5150 | value 5150
(2 rows)
Now, two questions:
1. Is there something that guarantees the "key" and "val" columns
will maintain their expected pairing?
... or am i just getting lucky with this simple example?
2. What design considerations sway one's decision to opt for an hstore
type vs. a separate key-value table for such "tags"?
Thanks for reading/answering,
--patrick
--
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] Upgrade from 9.3 to 9.4 issue
On 31/10/15 13:32, Tom Lane wrote: Stephen Davies writes: I have just upgraded from Fedora 21 to 22. This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster to fail because the existing databases are still at 9.3. As suggested, I then ran postgresql-setup --upgrade but this failed with: lc_collate cluster values do not match: old "en_US.UTF-8", new "en_AU.UTF-8" Hm, apparently you changed the system-wide language setting between F21 and F22? One thing you should do is file a bug in Red Hat's bugzilla, pointing out that postgresql-setup needs to endeavor to create the new cluster with lc_collate and lc_ctype matching the old one. Then, depending on what you want to do: 1. You really want to switch to en_AU, not just system-wide but for the database: gonna have to dump and reload, I'm afraid. pg_upgrade will not handle this scenario. 2. You want to switch to en_AU system-wide but it's okay to leave the database running in en_US: what I'd do personally is edit the postgresql-setup shell script and add "export LANG=en_US.UTF-8" just before the initdb call. This won't stick across your next Fedora upgrade, but hopefully by then Red Hat will have addressed your bug. 3. This was a mistake and you'd rather stay in en_US all round: you can probably change the system-wide language setting somewhere, but I do not remember where right at the moment. regards, tom lane I have resolved this issue. I chose your option 2 (almost). I couldn't get postgresql-setup to do what I wanted so I reverted to doing the upgrade by hand. Luckily, PostgreSQL 9.3 had not been removed from the box so I was able to manually initdb with the US collation etc to create a new cluster where I wanted it to be and then run a regular pg_upgrade. This is actually easier than mucking about with postgesql-setup. -- = Stephen Davies Consulting P/L Phone: 08-8177 1595 Adelaide, South Australia.Mobile:040 304 0583 Records & Collections Management. -- 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] Hierarchical Query Question (PHP)
Awesome; thanks! On Sat, Oct 31, 2015 at 7:19 AM, Andy Colson wrote: > On 10/30/2015 05:10 PM, David Blomstrom wrote: > >> Just so I understand what's going on, I can create a lookup table by >> pasting this code... >> >> > I don't know anything about biology so this data might be laughable, but > its based on your original question: > > > http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query > > > It seemed like the parent_id column was really the rank, and I couldn't > tell if it should be taxon_rank or parent_rank. Thinking more about the > problem, I normalized a little, renamed some, and came up with this > script. It lacks indexes and proper names, etc, etc, because I wanted to > tackle one problem at a time. Hopefully its a good example. > > create table rank ( > id integer, > descr text > ); > > insert into rank values(1, 'Classes'); > insert into rank values(2, 'Orders'); > insert into rank values(3, 'Families'); > insert into rank values(4, 'Genera'); > insert into rank values(5, 'Species'); > > create table mammals ( > id integer, > taxon text, > rank integer, > parentid integer > ); > > insert into mammals values (0, 'Chordata', 1, null); > insert into mammals values (1, 'Mammalia', 1, 0); > insert into mammals values (2, 'Carnivora', 2, 1); > insert into mammals values (3, 'Canidae' , 3, 2); > insert into mammals values (4, 'Canis', 4, 3); > insert into mammals values (5, 'Canis-lupus', 5, 4); > insert into mammals values (6, 'Canis-latrans', 5, 4); > > > -- This query shows you the basic results. It only > -- returns the id columns. further queries build on this base one. > -- (you could this of this query as Order Chordata :-) ) > with recursive heir(id, rank, parentid) as ( > select m.id, 0, m.parentid > from mammals m > where taxon = 'Canis' > union all > select m.id, m.rank, m.parentid > from heir > inner join mammals m on m.parentid = heir.id > ) > select * from heir; > > Results: > id | rank | parentid > +--+-- > 4 |0 |3 > 5 |5 |4 > 6 |5 |4 > (3 rows) > > > > -- This looks up the columns for a more meaningful result: > with recursive heir(id, rank, parentid) as ( > select m.id, 0, m.parentid > from mammals m > where taxon = 'Canidae' > union all > select m.id, m.rank, m.parentid > from heir > inner join mammals m on m.parentid = heir.id > ) > select m.taxon, r.descr > from heir > inner join mammals m on m.id = heir.id > inner join rank r on heir.rank=r.id > > Results: > taxon | descr > ---+- > Canis | Genera > Canis-lupus | Species > Canis-latrans | Species > > > - > -- This, finally, groups and counts, like your original question > with recursive heir(id, rank, parentid) as ( > select m.id, 0, m.parentid > from mammals m > where taxon = 'Canidae' > union all > select m.id, m.rank, m.parentid > from heir > inner join mammals m on m.parentid = heir.id > ) > select r.id, r.descr, count(*) > from heir > inner join mammals m on m.id = heir.id > inner join rank r on heir.rank=r.id > group by r.id, r.descr > order by r.id > > > Results: > id | descr | count > +-+--- > 4 | Genera | 1 > 5 | Species | 2 > > > -- David Blomstrom Writer & Web Designer (Mac, M$ & Linux) www.geobop.org
Re: [GENERAL] Selectively Importing Data
Thanks for the tips. On Sat, Oct 31, 2015 at 7:12 AM, Charles Clavadetscher < clavadetsc...@swisspug.org> wrote: > Hello > > This should work: > > \copy taxon (descr) from ; > > This is true if your sheet has only one column, so you should delete that > column. However if you happen to have another table with a foreign key on > taxonid you will probably screw up the references. But from your > description I take it, that this is not the case. > > Bye > Charles > > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Raymond O'Donnell > > Sent: Samstag, 31. Oktober 2015 12:42 > > To: David Blomstrom ; > pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Selectively Importing Data > > > > On 31/10/2015 00:24, David Blomstrom wrote: > > > First consider the following table: > > > > > > create table taxon ( > > > taxonid serial, > > > descr text > > > ); > > > > > > As I understand it, "serial" means that column will automatically > > > populate with a numerical key. > > > > > > If I want to fill the field 'descr' with a list of scientific names > > > stored in a spreadsheet, then how would I proceed? > > > > > > I have a spreadsheet that has about a dozen columns. I copied the field > > > with scientific names into a new spreadsheet. Then I created an empty > > > field on the left. So I have a spreadsheet with two columns. I saved it > > > as a CSV file. > > > > > > When I try to import it with pgAdmin III, I get the error message > > > > > > WARNING: null value in column "taxonid" violates non-null constraint > > > > > > How can I import that single column into the second column in this > > > table? Thanks. > > > > Serial columns will only get populated if there's no value (or no NULL > > either) inserted into that column. "Serial" [1] is not a real data type; > > it's just a handy shorthand for creating a sequence, setting a DEFAULT > > of nextval([sequence name]) on the column, and making that column NOT > NULL. > > > > At a guess, I think that - by creating the blank column in the > > spreadsheet and then importing from it - you were actually inserting > > NULL into the taxonid column, hence violating the NOT NULL constraint. > > > > You need to find some way of excluding the taxonid column from the > > import, so that the DEFAULT mechanism will populate it for you. I don't > > know how you do that with pgAdmin; I know it can be done with COPY from > > the command-line. You could try posting to the pgAdmin mailing list [2] > > about it. > > > > I hope that helps, > > > > Ray. > > > > > > [1] > > > http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL > > > > [2] http://www.pgadmin.org/support/list.php > > > > > > -- > > Raymond O'Donnell :: Galway :: Ireland > > r...@iol.ie > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > > -- > 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] does pg_dump get delayed if I have a loop that does continuous insertions
2015-10-28 21:09 GMT+01:00 anj patnaik :
> I was trying to achieve smallest file possible so tried the xz. Right now
> the db size returned from SELECT
> pg_size_pretty(pg_database_size('postgres') is 1.4 GB and the size of the
> dump with xz is 2.2 GB.
>
> Is there a limit to the size of the database that pg_dump will run on?
>
No limit..
> Will it work when db is in TBs?
>
>
... though this would big a rather bad idea. It will work, but it will be
damn slow. And its restoration will last way too much. Much better to do
PITR backups with databases bigger than 100GB.
> Also, I want to know if anyone has found any handy cron scripts for
> automated backups to run on a daily/weekly basis? i found some on google,
> but interested to know if there are better ones.
>
>
I really like pg_back (though, as a disclaimer, I should say this is
written by one of my co-workers, Nicolas Thauvin). Anyway, it's on
https://github.com/orgrim/pg_back if you want to take a look.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
Re: [GENERAL] Hierarchical Query Question (PHP)
On 10/30/2015 05:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... I don't know anything about biology so this data might be laughable, but its based on your original question: http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query It seemed like the parent_id column was really the rank, and I couldn't tell if it should be taxon_rank or parent_rank. Thinking more about the problem, I normalized a little, renamed some, and came up with this script. It lacks indexes and proper names, etc, etc, because I wanted to tackle one problem at a time. Hopefully its a good example. create table rank ( id integer, descr text ); insert into rank values(1, 'Classes'); insert into rank values(2, 'Orders'); insert into rank values(3, 'Families'); insert into rank values(4, 'Genera'); insert into rank values(5, 'Species'); create table mammals ( id integer, taxon text, rank integer, parentid integer ); insert into mammals values (0, 'Chordata', 1, null); insert into mammals values (1, 'Mammalia', 1, 0); insert into mammals values (2, 'Carnivora', 2, 1); insert into mammals values (3, 'Canidae' , 3, 2); insert into mammals values (4, 'Canis', 4, 3); insert into mammals values (5, 'Canis-lupus', 5, 4); insert into mammals values (6, 'Canis-latrans', 5, 4); -- This query shows you the basic results. It only -- returns the id columns. further queries build on this base one. -- (you could this of this query as Order Chordata :-) ) with recursive heir(id, rank, parentid) as ( select m.id, 0, m.parentid from mammals m where taxon = 'Canis' union all select m.id, m.rank, m.parentid from heir inner join mammals m on m.parentid = heir.id ) select * from heir; Results: id | rank | parentid +--+-- 4 |0 |3 5 |5 |4 6 |5 |4 (3 rows) -- This looks up the columns for a more meaningful result: with recursive heir(id, rank, parentid) as ( select m.id, 0, m.parentid from mammals m where taxon = 'Canidae' union all select m.id, m.rank, m.parentid from heir inner join mammals m on m.parentid = heir.id ) select m.taxon, r.descr from heir inner join mammals m on m.id = heir.id inner join rank r on heir.rank=r.id Results: taxon | descr ---+- Canis | Genera Canis-lupus | Species Canis-latrans | Species - -- This, finally, groups and counts, like your original question with recursive heir(id, rank, parentid) as ( select m.id, 0, m.parentid from mammals m where taxon = 'Canidae' union all select m.id, m.rank, m.parentid from heir inner join mammals m on m.parentid = heir.id ) select r.id, r.descr, count(*) from heir inner join mammals m on m.id = heir.id inner join rank r on heir.rank=r.id group by r.id, r.descr order by r.id Results: id | descr | count +-+--- 4 | Genera | 1 5 | Species | 2 -- 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] Selectively Importing Data
Hello This should work: \copy taxon (descr) from ; This is true if your sheet has only one column, so you should delete that column. However if you happen to have another table with a foreign key on taxonid you will probably screw up the references. But from your description I take it, that this is not the case. Bye Charles > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Raymond O'Donnell > Sent: Samstag, 31. Oktober 2015 12:42 > To: David Blomstrom ; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Selectively Importing Data > > On 31/10/2015 00:24, David Blomstrom wrote: > > First consider the following table: > > > > create table taxon ( > > taxonid serial, > > descr text > > ); > > > > As I understand it, "serial" means that column will automatically > > populate with a numerical key. > > > > If I want to fill the field 'descr' with a list of scientific names > > stored in a spreadsheet, then how would I proceed? > > > > I have a spreadsheet that has about a dozen columns. I copied the field > > with scientific names into a new spreadsheet. Then I created an empty > > field on the left. So I have a spreadsheet with two columns. I saved it > > as a CSV file. > > > > When I try to import it with pgAdmin III, I get the error message > > > > WARNING: null value in column "taxonid" violates non-null constraint > > > > How can I import that single column into the second column in this > > table? Thanks. > > Serial columns will only get populated if there's no value (or no NULL > either) inserted into that column. "Serial" [1] is not a real data type; > it's just a handy shorthand for creating a sequence, setting a DEFAULT > of nextval([sequence name]) on the column, and making that column NOT NULL. > > At a guess, I think that - by creating the blank column in the > spreadsheet and then importing from it - you were actually inserting > NULL into the taxonid column, hence violating the NOT NULL constraint. > > You need to find some way of excluding the taxonid column from the > import, so that the DEFAULT mechanism will populate it for you. I don't > know how you do that with pgAdmin; I know it can be done with COPY from > the command-line. You could try posting to the pgAdmin mailing list [2] > about it. > > I hope that helps, > > Ray. > > > [1] > http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL > > [2] http://www.pgadmin.org/support/list.php > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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] Selectively Importing Data
On 31/10/2015 00:24, David Blomstrom wrote: > First consider the following table: > > create table taxon ( > taxonid serial, > descr text > ); > > As I understand it, "serial" means that column will automatically > populate with a numerical key. > > If I want to fill the field 'descr' with a list of scientific names > stored in a spreadsheet, then how would I proceed? > > I have a spreadsheet that has about a dozen columns. I copied the field > with scientific names into a new spreadsheet. Then I created an empty > field on the left. So I have a spreadsheet with two columns. I saved it > as a CSV file. > > When I try to import it with pgAdmin III, I get the error message > > WARNING: null value in column "taxonid" violates non-null constraint > > How can I import that single column into the second column in this > table? Thanks. Serial columns will only get populated if there's no value (or no NULL either) inserted into that column. "Serial" [1] is not a real data type; it's just a handy shorthand for creating a sequence, setting a DEFAULT of nextval([sequence name]) on the column, and making that column NOT NULL. At a guess, I think that - by creating the blank column in the spreadsheet and then importing from it - you were actually inserting NULL into the taxonid column, hence violating the NOT NULL constraint. You need to find some way of excluding the taxonid column from the import, so that the DEFAULT mechanism will populate it for you. I don't know how you do that with pgAdmin; I know it can be done with COPY from the command-line. You could try posting to the pgAdmin mailing list [2] about it. I hope that helps, Ray. [1] http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL [2] http://www.pgadmin.org/support/list.php -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
