Re: [SQL] Find periods for a given... action set?
Dobro Vece, smth like: SELECT a.user_id,a.action_mark,a.action_time,a.action_time- (select a2.action_time from actions a2 where a2.oid= (select a3.oid from actions a3 where a3.user_id=a.user_id and a3.action_mark='BEGIN' and a3.action_time I have a table where there are actinos for some user logged. It's part > of the MPI system of some sort. For every user, action type and time of > the action is logged. There are many action types but the ones which are > of interest to me are BEGIN and END. I need to find the durations for > all the periods between BEGINs and ENDs, for each user. > > Here is what the table looks like: > > CREATE TABLE actions ( > > user_id integer, > > action_mark character varying, > > action_time timestamp > > ) > > There are no PK constraints because those columns are excerpt from a > action_log table, there is a message_id column which is a PK; user_id > and action_mark are FKs to the users and actions tables. Now that I look > at it, in the above table PK would be (user_id, action_time), but there > are no PKs as for now :) > > Some example data: > > > > INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00'); > > INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22'); > > INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01'); > > INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00'); > > INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02'); > > INSERT INTO actions VALUES (1, 'END', '2009-02-02 13:21:01'); > > INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01'); > > INSERT INTO actions VALUES (2, 'END', '2009-02-02 16:11:21'); > > INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31'); > > INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12'); > > INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21'); > > INSERT INTO actions VALUES (2, 'END', '2009-02-02 19:00:01'); > > INSERT INTO actions VALUES (1, 'END', '2009-02-02 19:10:01'); > > INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01'); > > > Now, for each user, i need to find all periods 'enclosed' with BEGIN/END > action_type pairs. If I have a BEGIN and no END, than there is no > period. So, if I have, for some user, BEGIN-END-BEGIN-END-BEGIN, there > are only two periods. > Also, if there are consecutive BEGINS, only the last one counts: > BEGIN-END-BEGIN-BEGIN-BEGIN-END-BEGIN - again, only two periods. > > The results I would like to get look like this: > > user_id action_duration > > 1 01:21:01 > > 1 00:57:40 > > 2 04:09:20 > > 2 00:48:49 > > User_id 3 has just the BEGIN - there is no period there, because I don't > have and endpoint. Similarly, user_id 1 has BEGIN as the last action - > just two periods for user_id 1, because last BEGIN denotes 'period in > progress'. > > Also, user_id 1 has following actions happened, time ordered: BEGIN, > END, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN > canceled second BEGIN and all the actions between second and third BEGIN. > > Now, using some imperative Python, Perl, C, whatever, it's not that > complicated to get what I want, but I would realy like to have it solved > within plain SQL :) > > So, if anyone has any suggestions, I would greatly appreciate them. > Smth > Mike > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] very frustrating feature-bug
Στις Wednesday 17 February 2010 17:27:11 ο/η Tom Lane έγραψε: > silly sad writes: > > may i ask? when this feature will be fixed? > > (now i am using 8.3.9) > > The example works fine for me in 8.4. Same for me, postg...@smadevnew:~> postg...@smadevnew:~> psql psql (8.4.1) Type "help" for help. dynacom=# CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) dynacom-# RETURNS usr AS $$ dynacom$#INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) dynacom$#RETURNING usr.*; dynacom$# $$ LANGUAGE sql SECURITY DEFINER; CREATE FUNCTION dynacom=# dynacom=# > > regards, tom lane > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] join with an array
Στις Wednesday 24 February 2010 15:34:48 ο/η Louis-David Mitterrand έγραψε:
> Here is a test case I built. I want to list all cruises by cruise_type
> but after merging cruise_type that have the same cruise_type_name:
>
> drop table cruise;
> drop table cruise_type;
>
> create table cruise_type (
> id_cruise_type serial primary key,
> cruise_type_name text
> );
>
> create table cruise (
> id_cruise serial,
> id_cruise_type integer references cruise_type,
> cruise_date timestamp default now()
> );
>
> insert into cruise_type (cruise_type_name) values
> ('5 day eastern carribean cruise'),
> ('5 day western carribean cruise'),
> ('5 day eastern carribean cruise'),
> ('5 day western carribean cruise')
> ;
>
> insert into cruise (id_cruise_type) values
> (1),
> (2),
> (3),
> (4),
> (1),
> (2),
> (3),
> (4)
> ;
>
> select array_agg(ct.id_cruise_type),ct.cruise_type_name from cruise_type ct
> join cruise c on (c.id_cruise = any(array_agg)) group by cruise_type_name;
>
You dont specify (in english) what you exactly want to achive, but here is my
shot:
1st, get the cruises by cruise type:
select ct.id_cruise_type,array_agg(c.id_cruise) as "List of Cruises" from
cruise_type ct, cruise c WHERE c.id_cruise_type=ct.id_cruise_type GROUP BY
ct.id_cruise_type ORDER BY ct.id_cruise_type;
id_cruise_type | List of Cruises
+-
1 | {1,5}
2 | {2,6}
3 | {3,7}
4 | {4,8}
(4 rows)
test=#
Then you may pretify this to include the name of each cruise type as well:
select ct.id_cruise_type,ct.cruise_type_name,array_agg(c.id_cruise) as "List of
Cruises" from cruise_type ct, cruise c WHERE c.id_cruise_type=ct.id_cruise_type
GROUP BY ct.id_cruise_type,ct.cruise_type_name ORDER BY ct.id_cruise_type;
id_cruise_type |cruise_type_name| List of Cruises
++-
1 | 5 day eastern carribean cruise | {1,5}
2 | 5 day western carribean cruise | {2,6}
3 | 5 day eastern carribean cruise | {3,7}
4 | 5 day western carribean cruise | {4,8}
(4 rows)
EXERCISE:
Why cant we exclude ct.id_cruise_type from the select clause and group by of
the above query?
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
You could also consider the genealogical approach, e.g.
postg...@dynacom=# \d paintgentypes
Table "public.paintgentypes"
Column | Type| Modifiers
-+---+---
id | integer | not null default
nextval(('public.paintgentypes_id_seq'::text)::regclass)
name| text | not null
parents | integer[] |
Indexes:
"paintgentypes_pkey" PRIMARY KEY, btree (id)
"paintgentypes_name2" UNIQUE, btree (name) WHERE parents IS NULL
"paintgentypes_uk" UNIQUE, btree (name, parents)
"paintgentypes_first" btree (first(parents))
"paintgentypes_last" btree (last(parents))
"paintgentypes_level" btree (level(parents))
"paintgentypes_name" btree (name)
"paintgentypes_parents" gin (parents gin__int_ops)
The indexes are based on the contrib/intarray package.
It is very fast to do any operation on this tree.
Also it is very fast to search for the parent of any node, or the
children of any node, or the whole subtree of any node, or the depth of any
node in the tree.
The parents of any node to the root, i.e. the path of any node to the root are
depicted as
parents[0] : immediate parent
parents[1] : immediate parent of the above parent
.
parents[n] : root of the tree
Στις Tuesday 06 April 2010 20:33:18 ο/η Lee Hachadoorian έγραψε:
> Please point me to another listserv or forum if this question is more
> appropriately addressed elsewhere.
>
> I am trying to come up with a structure to store employment data by NAICS
> (North American Industrial Classification System). The data uses a
> hierarchical encoding scheme ranging between 2 and 5 digits. That is, each
> 2-digit code includes all industries beginning with the same two digits. 61
> includes 611 which includes 6111, 6112, 6113, etc. A portion of the
> hierarchy is shown after the sig.
>
> A standard way to store hierarchical data is the adjacency list model, where
> each node's parent appears as an attribute (table column). So 6111 would
> list 611 as its parent. Since NAICS uses a hierarchical encoding scheme, the
> node's name is the same as the node's id, and the parent can always be
> derived from the node's id. Storing the parent id separately would seem to
> violate a normal form (because of the redundancy).
>
> One way to store this data would be to store at the most granular level
> (5-digit NAICS) and then aggregate up if I wanted employment at the 4-, 3-,
> or 2-digit level. The problem is that because of nondisclosure rules, the
> data is sometimes censored at the more specific level. I might, for example,
> have data for 6114, but not 61141, 61142, 61143. For a different branch of
> the tree, I might have data at the 5-digit level while for yet another
> branch I might have data only to the 3-digit level (not 4 or 5). I think
> that means I have to store all data at multiple levels, even if some of the
> higher-level data could be reconstructed from other, lower-level data.
>
> Specifically I'd like to know if this should be a single table or should
> there be a separate table for each level of the hierarchy (four in all)? If
> one table, should the digits be broken into separate columns? Should parent
> ids be stored in each node?
>
> More generally, what questions should I be asking to help decide what
> structure makes the most sense? Are there any websites, forums, or books
> that cover this kind of problem?
>
> Regards,
> --Lee
>
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Στις Wednesday 07 April 2010 10:53:00 ο/η silly sad έγραψε:
> On 04/07/10 11:00, Achilleas Mantzios wrote:
>
> > Column | Type| Modifiers
> > -+---+---
> > id | integer | not null default
> > nextval(('public.paintgentypes_id_seq'::text)::regclass)
> > name| text | not null
> > parents | integer[] |
>
> > The parents of any node to the root, i.e. the path of any node to the root
> > are depicted as
> > parents[0] : immediate parent
> > parents[1] : immediate parent of the above parent
> > .
> > parents[n] : root of the tree
>
> what this schema gives?
>
> (1) the parent branch in one select.
1st the number of selects has nothing to do with speed
2nd as you will see below, the number of select is always 1, for any basic tree
operation.
> what else?
> nothing.
>
No, you are wrong.
1) find immediate father
parents[0] (O(1) complexity)
2) find root
parents[level(parents)] (O(1) complexity)
3) insert a node under a father
O(1) complexity
4) find all immediate children of a father node: (e.g. 2)
SELECT * from paintgentypes where parents[1] =2; (caution: NON indexed select)
or
SELECT * from paintgentypes where itoar(2) ~ parents and level(parents)=(level
of node 2 )+1;
5) find all children and grandchildren of a father node: (e.g. 2)
SELECT * from paintgentypes where itoar(2) ~ parents and level(parents)<=(level
of node 2 )+2;
6) find whole subtree of a node (e.g. 2)
SELECT * from paintgentypes where itoar(2) ~ parents;
In PostgreSQL, the above model i think is superior to nested trees in every
apsect.
This is due to the excellent intarray module.
PS
Excuse me for the typo in the previous mail.
Arrays in postgresql are 1-based.
> compare it to a nested-tree
>
> id | integer | NOT NULL
> name| text | not null
> parent | integer |
> l | numeric
> r | numeric
>
> (1) parent branch in one select
> (2) child subtree in one select
> (it makes a sence!)
>
>
>
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Στις Wednesday 07 April 2010 11:26:29 ο/η Sergey Konoplev έγραψε: > On 6 April 2010 21:33, Lee Hachadoorian wrote: > > More generally, what questions should I be asking to help decide what > > structure makes the most sense? Are there any websites, forums, or books > > that cover this kind of problem? > > Haven't you thought about ltree contrib? From the description of > ltree: "This module implements a data type ltree for representing > labels of data stored in a hierarchical tree-like structure". > > http://www.postgresql.org/docs/8.4/interactive/ltree.html Thats definitely worth checking out. Personally i didn't follow this apprach cause it seemd a little bit more restricting than doing it my way. However, for this case especially, it looks like it solves a photograph of the original problem of this therad. Besides, the authors of this fine contrib module are the same known PostgreSQL contributors of tsearch2, postgresql FTS, intarray (which i heavily use for the tree representation), etc... > > > -- > Sergey Konoplev > > Blog: http://gray-hemp.blogspot.com / > Linkedin: http://ru.linkedin.com/in/grayhemp / > JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: > Achilleas Mantzios wrote: > > You could also consider the genealogical approach, e.g. > > > > > > The parents of any node to the root, i.e. the path of any node to the root > > are depicted as > > parents[0] : immediate parent > > parents[1] : immediate parent of the above parent > > > What I have more than one parent? Then it is no longer neither a tree, nor a hierarchical structure, but rather a graph. This a totally different problem. > > regards, > Yeb Havinga > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: > Achilleas Mantzios wrote: > > Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: > > > >> Achilleas Mantzios wrote: > >> > >>> You could also consider the genealogical approach, e.g. > >>> > >>> > >>> The parents of any node to the root, i.e. the path of any node to the > >>> root are depicted as > >>> parents[0] : immediate parent > >>> parents[1] : immediate parent of the above parent > >>> > >>> > >> What I have more than one parent? > >> > > > > Then it is no longer neither a tree, nor a hierarchical structure, but > > rather a graph. > > This a totally different problem. > > > My question was actually an attempt to point at the inability of what > you call the 'genealogical approach' database design to store > information of more than one parent. Are you suggesting that we should change our definition of trees ADT, just because it does not fit the mere detail that humans have two parents? Or are you just suggesting that the "genealogical" term is inaccurate? Take a look here: www.tetilab.com/roberto/pgsql/postgres-trees.pdf > > regards, > Yeb Havinga > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Στις Thursday 08 April 2010 17:59:01 ο/η Rob Sargent έγραψε: > The "parent" node in a genealogy is the mother-father tuple, so given > that as a singularity it still fits a tree. No, because the child and parent node would be of different schema. > > On 04/08/2010 12:56 AM, Achilleas Mantzios wrote: > > Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: > >> Achilleas Mantzios wrote: > >>> Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: > >>> > >>>> Achilleas Mantzios wrote: > >>>> > >>>>> You could also consider the genealogical approach, e.g. > >>>>> > >>>>> > >>>>> The parents of any node to the root, i.e. the path of any node to the > >>>>> root are depicted as > >>>>> parents[0] : immediate parent > >>>>> parents[1] : immediate parent of the above parent > >>>>> > >>>>> > >>>> What I have more than one parent? > >>>> > >>> > >>> Then it is no longer neither a tree, nor a hierarchical structure, but > >>> rather a graph. > >>> This a totally different problem. > >>> > >> My question was actually an attempt to point at the inability of what > >> you call the 'genealogical approach' database design to store > >> information of more than one parent. > > > > > > Are you suggesting that we should change our definition of trees ADT, just > > because it does not > > fit the mere detail that humans have two parents? > > Or are you just suggesting that the "genealogical" term is inaccurate? > > > > Take a look here: www.tetilab.com/roberto/pgsql/postgres-trees.pdf > > > >> > >> regards, > >> Yeb Havinga > >> > >> > > > > > > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] PostgreSQL ontop of FreeBSD jails, maybe there is still hope!
It seems there are certain projects running at the moment that will eventually make possible to run PostgreSQL on FreeBSD's jail (virtual server on plain iron speed). Pre jail resource limits: http://freebsdfoundation.blogspot.com/2010/07/resource-containers-project.html Further generalization improvements: http://freebsdfoundation.blogspot.com/2010/06/update-on-jail-based-virtualization.html "Further, the project includes generalization of the virtual network stack framework, factoring out common code. This will provide an infrastructure and will ease virtualization of further subsystems like SYSV/Posix IPC with minimal overhead. All further virtualized subsystems will immediately benefit from shared debugging facilities, an essential feature for early adopters of the new technology." By solving the SYSV/IPC problems, PostgreSQL will be able to be run on jails, even easier and more robustly than it is the case now. Lets hope those guys will deliver smth good. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL ontop of FreeBSD jails, maybe there is still hope!
Στις Wednesday 21 July 2010 10:57:45 ο/η Dave Page έγραψε: > On Wed, Jul 21, 2010 at 7:47 AM, Achilleas Mantzios > wrote: > > It seems there are certain projects running at the moment that will > > eventually make possible > > to run PostgreSQL on FreeBSD's jail (virtual server on plain iron speed). > > We've been doing that across the project infrastructure for 10 years > or more. The only issue we run into is that we need to use a unique > port in each jail as shared memory isn't entirely isolated between > jails. Thats a pretty known workaround, still inconvenient and annoying for massive scale virtual environments. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise Postgres Company > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL array, recursion and more
Στις Wednesday 17 November 2010 15:22:34 ο/η Ferruccio Zamuner έγραψε:
>
> /* How to get access to its items then?
> */
select (mesh_split('A01.378.610.250.300.792.380')::text[])[1];
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Deleting entries from multiple tables
Here is my take on how to regard parent tables in one-to-many FK relationships. If lets say we have a situation when we model e.g. mail messages and its attachments, then we might want to use ON DELETE CASCADE since there is absolutely no reason for an attachment to exist when the main message is gone. But, when we model e.g. machinery in a car and we have an attribute : maker of parts modeled as a parent table and the child parts table pointing to this maker table, then this parent table is not of high significance and we should not put ON DELETE CASCADE. In this way, we protect the database of deleting vital data. Note that under the parts table, other tables might point to, which would include history, plan maintenace, and other very vital data. So, you are enouraged to put ON DELETE CASCADE ONLY when you are confident that the child table's rows have absolutely no meaning without the parent table row. Otherwise, leave the default behaviour. Στις Tuesday 30 November 2010 10:19:04 ο/η Dusan Misic έγραψε: > To be precise, if you used defaults, you can't delete any row in table A > that has rows referencing to it in tables B and C. > > On Tue, Nov 30, 2010 at 6:59 AM, manidegr8 wrote: > > > > > i am trying to run a query but its not working may be due to constraint > > conflicts > > > > i have table A, B and C > > B and C have a foreign key linked with A's primary key > > > > so i want to delete an entry from A > > for that i hav to delete child records first > > > > can u design a query which wont conflict the constraints..? > > > > Thanks... > > Regards. > > -- > > View this message in context: > > http://postgresql.1045698.n5.nabble.com/Deleting-entries-from-multiple-tables-tp3285654p3285654.html > > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Full Text search differences from 8.3 to 8.4.6
Hello,
i am currently evaluating the migration cost from 8.3 -> 9.x and in order to do
that,
i think i must have a confident view of the situation of my app with 8.4.6 as
an intermediate stage.
I noticed a few minor incompatibilities, but also a change in behaviour in full
text search:
Table "public.vmanews"
Column | Type | Modifiers
-+--+-
id | integer | not null default
nextval(('public.vmanews_id_seq'::text)::regclass)
newid | integer | not null
date| date | not null default now()
vslid | integer | not null
cat | text | not null
appname | text | not null
header | text | not null
content | text | not null
idxfti | tsvector |
Indexes:
"vmanews_pkey" PRIMARY KEY, btree (id)
"vmanews_vslid_newid" UNIQUE, btree (vslid, newid)
"vmanews_idxfti_idx" gist (idxfti)
"vmanews_vslid" btree (vslid)
Foreign-key constraints:
"$1" FOREIGN KEY (vslid) REFERENCES vessels(id)
Triggers:
vmanews_tsvectorupdate BEFORE INSERT OR UPDATE ON vmanews FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('idxfti', 'pg_catalog.english',
'header', 'content')
in 8.3 :
postg...@dynacom=# SELECT idxfti from vmanews where id=28717;
idxfti
---
'8':25 'ad':12,34 'job':9,30 'pms':62 'vma':61 'attn':3 'fuel':20 'hour':27
'kind':43 'main':1,16 'note':6 'pump':21 'sinc':28 'check':18,47 'engin':2,17
'everi':24 'first':53 'pleas':5 'shock':22,50 'absorb':23,51 'report':55
'result':58 'vessel':14,38 'vma-pm':60 'inspect':57 'request':44 'respect':49
'deliveri':36 'opportun':54 'overlook':41 'tech.dept':64 'tks/brgds':63
'000running':26 'master/cheng':4
(1 row)
(here is the important command)
postg...@dynacom=# SELECT idxfti @@ to_tsquery('overlooked') from vmanews where
id=28717;
?column?
--
t
(1 row)
while in 8.4.6 :
SELECT idxfti from vmanews where id=28717;
idxfti
---
'000running':26 '8':25 'absorb':23,51 'ad':12,34 'attn':3 'check':18,47
'deliveri':36 'engin':2,17 'everi':24 'first':53 'fuel':20 'hour':27
'inspect':57 'job':9,30 'kind':43 'main':1,16 'master/cheng':4 'note':6
'opportun':54 'overlook':41 'pleas':5 'pms':62 'pump':21 'report':55
'request':44 'respect':49 'result':58 'shock':22,50 'sinc':28 'tech.dept':64
'tks/brgds':63 'vessel':14,38 'vma':61 'vma-pm':60
(1 row)
and (the important command in 8.4.6)
dynacom=# SELECT idxfti @@ to_tsquery('overlooked') from vmanews where id=28717;
?column?
--
f
(1 row)
So, searching for "overlooked" which the original word in the text, in 8.3
idxfti @@ to_tsquery('overlooked') returns true
while in 8.4.6 idxfti @@ to_tsquery('overlooked') returns false.
Is there anything i am missing?
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import
Στις Tuesday 21 December 2010 13:36:58 ο/η Pavel Stehule έγραψε: > Hello > > 2010/12/21 Viktor Bojović : > > Hi, > > > > can anyone recommend me a windows and linux free tools for importing data > > into postgre. > > Source files are CSV or excel. > Openoffice can do this job, populating a table from an excel data source, but every time i do it (which is not often) it is like reinventing the wheel again. The way to do it, IIRC, is to "create" a database based on your existing excel data source. After that you can copy data from this "excel-powered" database to your normal pgsql database, fairly easily. It is easy actually, just not so straight forward as most people would want. > PostgreSQL can read a CVS files via a COPY statement. You can use a > \copy metacommand too from psql > > Regards > > Pavel Stehule > > > Thanx in advance > > -- > > --- > > Viktor Bojović > > --- > > Wherever I go, Murphy goes with me > > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL gotcha
Στις Tuesday 15 February 2011 01:29:25 ο/η Dean Gibson (DB Administrator) έγραψε: > As you all know, when you specify an alias for a table or column name, > the keyword "AS" is usually optional. I personally like to always > include the "AS" keyword, if for no other reason than readability, and I > presume that always using "AS" might catch some syntactic errors as well. > > However, recently I ran across a statement where the optionality of the > "AS" keyword caused me some grief: > > SELECT ... FROM aaa LEFFT JOIN bbb WHERE ... > > Note the (unintentional) misspelling of the keyword "LEFT". PostgreSQL > took "LEFFT" to be an alias for table "aaa" and silently accepted the > statement; naturally I did not get the results I expected !! > > If PostgreSQL had an option to always require the "AS", that would have > caught this error. Is there any interest in PostgreSQL having any > "pedantic"-type options, like many C/C++ compilers do? > Actually PostgreSQL seems to endorse the usage of "AS" more than the SQL standard requires. http://www.postgresql.org/docs/8.3/interactive/sql-select.html "The AS Key Word In the SQL standard, the optional key word AS is just noise and can be omitted without affecting the meaning. The PostgreSQL parser requires this key word when renaming output columns because the type extensibility features lead to parsing ambiguities without it. AS is optional in FROM items, however. " -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Retrieve the column values of a record without knowing the names
Στις Wednesday 16 February 2011 18:18:19 ο/η arthur_info έγραψε: > > Hello, > > I've got the following function and I want to access the fields values of my > record by index. The problem is that my select is retrieving each record > line with all values and not each one of each row on my view... How can I > solve this problem? > Well by your function, it seems that it does not do what you want. You want to access the values of one row without knowing the names, but what you do is create an array over the values of a *known* column. Also where aluno = ''3043'' does not make any sense either. Anyways, you might want to look at database metada as found in the information_schema. For a start you might want to look in information_schema.columns > Thanks in advance. > > > CREATE FUNCTION fc_teste_tce(aluno integer) RETURNS character varying AS > $BODY$ > DECLARE > reg record; > BEGIN > for reg in execute 'SELECT ARRAY (SELECT vw_teste_tce FROM > estagio.vw_teste_tce where aluno = ''3043'' LIMIT 20) AS campos' loop > for j in 1..array_upper(reg.campos,1) loop > raise notice 'Field Value: %',reg.campos[j]; > end loop; > end loop; > return 'ok'; > END; > $BODY$ > > LANGUAGE plpgsql VOLATILE; > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387935p3387935.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] array dimensions, pg_catalog.pg_attribute and the \d command
Hello, just a question regarding multidimensional arrays. When i create an array, its number of dimensions is recorded in pg_catalog.pg_attribute (attndims), but they are not enforced upon insertion, nor is this presented by \d command. (i didn't see any attndims column showing up in the logs) Any reasons why this is so? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] array dimensions, pg_catalog.pg_attribute and the \d command
On Παρ 03 Φεβ 2012 22:21:49 Dmitriy Igrishin wrote: > Hey Achilleas, > > 2012/2/3 Achilleas Mantzios > > > Hello, > > just a question regarding multidimensional arrays. > > When i create an array, its number of dimensions is recorded in > > pg_catalog.pg_attribute (attndims), but they are not enforced upon > > insertion, > > nor is this presented by \d command. (i didn't see any attndims column > > showing > > up in the logs) > > > > Any reasons why this is so? > > According to the documentation > "The current implementation does not enforce the declared number > of dimensions either. Arrays of a particular element type are all > considered to be of the same type, regardless of size or number > of dimensions. So, declaring the array size or number of dimensions > in CREATE TABLE is simply documentation; it does not affect run-time > behavior." > See > http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-DECLARATION Thanx Dmitry. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Insane behaviour in 8.3.3
Hello,one remote user reported a problem and i was surprised to witness the
following behaviour.
It is on postgresql 8.3.3
dynacom=# BEGIN;
BEGIN
dynacom=#
dynacom=#
dynacom=# insert into xadmin(appname,apptbl_tmp,gao,id,comment)
dynacom-#
values('PMS','overhaul_report_tmp','INSERT',nextval('overhaul_report_tmp_pkid_seq'),'
zzz ');
INSERT 0 1
dynacom=#
dynacom=# insert into
items_tmp(id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,
dynacom(#
classpostponed,classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,xid,classaa)
dynacom-# select
id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,classpostponed,
dynacom-#
classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,currval('xadmin_xid_seq'),
dynacom-# classaa from items where id=1261319;
INSERT 0 1
dynacom=# -- in the above 'xadmin_xid_seq' has taken a new value in the first
insert
dynacom=# SELECT currval('xadmin_xid_seq');
currval
-
61972
(1 row)
dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=61972;
id
-
1261319
(1 row)
dynacom=# -- ok this is how it should be
dynacom=# SELECT id from items_tmp WHERE id=1261319 AND
xid=currval('xadmin_xid_seq');
id
(0 rows)
dynacom=# -- THIS IS INSANE
This code has run fine (the last SELECT returns exactly one row) for 5,409,779
total transactions thus far, in 70
different postgresql slave installations (mixture of 8.3.3 and 8.3.13) (we are
a shipping company),
until i got this error report from a user yesterday.
What could be causing this? How could i further investigate this? Unfortunately
the remote installations are neither
physically accessible nor by TCP/IP accesible (comms are done via UUCP and
administration via minicom, and the costs are
just huge 5 USD/min for 33Kbits/sec). So, i would exhaust all posibilities
before deciding to ship a new postgresql version there,
and remotely upgrade, physically travel to the ship or even trying to do a
backup/initdb/restore in the existing version.
Any help would be really really appreciated.
Also, as you might have understood, upgrading, although generally a good idea,
does not apply so easily in our case.
Some information about the schema :
dynacom=# \d xadmin
Table "public.xadmin"
Column |Type | Modifiers
+-+--
xid| bigint | not null default
nextval(('xadmin_xid_seq'::text)::regclass)
appname| text| not null
apptbl_tmp | text| not null
gao| character varying(40) | not null
id | integer | not null
comment| text|
state | text| not null default 'NPY'::text
arcedon| timestamp without time zone | default now()
Indexes:
"xa_pk" PRIMARY KEY, btree (xid)
"xa_appname_idx" btree (appname)
"xa_appname_state_idx" btree (appname, state)
"xa_state_idx" btree (state)
dynacom=# \d items_tmp
Table "public.items_tmp"
Column | Type | Modifiers
-++---
id | integer| not null
vslwhid | integer|
serialno| character varying(40) |
rh | integer|
lastinspdate| date |
classused | integer|
classaa | text |
classsurvey | character varying(100) |
classsurveydate | date |
classduedate| date |
classpostponed | date |
classcomment| text |
defid | integer|
machtypecount | integer|
totalrh | integer|
comment | character varying(200) |
attachments | text[] |
lastrepdate | date |
pmsstate| character varying(200) |
xid | bigint | not null
Indexes:
"it_tmp_pk" PRIMARY KEY, btree (id, xid)
Foreign-key constraints:
"items_tmp_xid_fkey" FOREIGN KEY (xid) REFERENCES xadmin(xid)
-
Achilleas Mantzios
IT DEPT
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Παρ 15 Ιουν 2012 09:34:16 Richard Huxton wrote:
> On 14/06/12 09:39, Achilleas Mantzios wrote:
> > dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=61972;
> >
> > id
> >
> > -
> >
> > 1261319
> >
> > (1 row)
> > dynacom=# -- ok this is how it should be
> > dynacom=# SELECT id from items_tmp WHERE id=1261319 AND
> > xid=currval('xadmin_xid_seq');
> >
> > id
> >
> >
> > (0 rows)
> > dynacom=# -- THIS IS INSANE
>
> Perhaps just do an EXPLAIN ANALYSE on both of those. If for some reason
> one is using the index and the other isn't then it could be down to a
> corrupted index. Seems unlikely though.
Hello Richard,
I had the same thought, and did the EPXLAIN ANALYZE and it gave results which
looked pretty much
like the below (unfortunately i didn't keep the original exact output, cause i
was in a hurry to solve the problem):
dynacom=# EXPLAIN ANALYZE SELECT id from items_tmp where id=1261319 AND
xid=62035;
QUERY PLAN
-
Index Scan using it_tmp_pk on items_tmp (cost=0.00..8.28 rows=1 width=4)
(actual time=0.017..0.018 rows=1 loops=1)
Index Cond: ((id = 1261319) AND (xid = 62035))
Total runtime: 0.042 ms
(3 rows)
dynacom=#
dynacom=# EXPLAIN ANALYZE SELECT id from items_tmp where id=1261319 AND
xid=currval('xadmin_xid_seq');
QUERY PLAN
Bitmap Heap Scan on items_tmp (cost=4.53..120.32 rows=1 width=4) (actual
time=58.212..58.212 rows=1 loops=1)
Recheck Cond: (id = 1261319)
Filter: (xid = currval('xadmin_xid_seq'::regclass))
-> Bitmap Index Scan on it_tmp_pk (cost=0.00..4.53 rows=37 width=0)
(actual time=0.021..0.021 rows=39 loops=1)
Index Cond: (id = 1261319)
Total runtime: 58.235 ms
(6 rows)
dynacom=#
After that, i tried to REINDEX items_tmp, which succeeded, and also made the
last select return correctly one row.
Being suspicious of the general condition of the database,I then tried to
REINDEX DATABASE the whole db, which failed
at some point because of corrupted data, but i didn't indicate which table had
the corruption. I then wrote a script to
make more verbose what table was being reindexed at any time and this time i
got no errors. I also re-issued the batch
REINDEX DATABASE command again with no errors. So it was indeed an index/data
corruption problem.
Thanx to Richard and Adrian
-
Achilleas Mantzios
IT DEPT
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Παρ 15 Ιουν 2012 10:28:20 Scott Marlowe wrote: > You do realize you're missing four years of bug fixes right? On Πεμ 14 Ιουν 2012 11:39:35 Achilleas Mantzios wrote: > Unfortunately the remote installations are neither physically accessible > nor by TCP/IP accesible (comms are done via UUCP and administration via > minicom, and the costs are just huge 5 USD/min for 33Kbits/sec). So, i > would exhaust all posibilities before deciding to ship a new postgresql > version there, and remotely upgrade, physically travel to the ship or even > trying to do a backup/initdb/restore in the existing version. Any help > would be really really appreciated. > > Also, as you might have understood, upgrading, although generally a good > idea, does not apply so easily in our case. And i forgot to mention, minicom term emulation quality sucks, even giving simple shell commands is a PITA, upgrading the whole fleet would mean bast case scenario minimum 21K USD for the whole fleet + suspension of all other activities for two months. If physical travel was involved, the cost would be increased at even higher levels. - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Παρ 15 Ιουν 2012 12:28:21 Samuel Gendler wrote: > On Fri, Jun 15, 2012 at 1:24 AM, Achilleas Mantzios < > > ach...@matrix.gatewaynet.com> wrote: > > And i forgot to mention, minicom term emulation quality sucks, even > > giving simple > > shell commands is a PITA, upgrading the whole fleet would mean bast case > > scenario > > minimum 21K USD for the whole fleet + suspension of all other activities > > for two months. > > If physical travel was involved, the cost would be increased at even > > higher levels. > > And what is the cost of data corruption on large numbers of systems? And > how much to fix that, especially if multiple systems fail at the same time? > Some things aren't free. $21K in exchange for NOT having had to keep > systems up to date for 4 years seems like a decent trade. After 12 years of using PostgreSQL in this environment, I can assure you that things are not so scary. We have multiple plans of action in case a slave installation totally gets damaged, plus PostgreSQL has been a real beast of reliability. Also here you neglet the cost of actual migration, test plans, which adds to the whole picture. Moving from 7.4 to 8.3 back in 2008, was not easy at all (tsearch, intarray, loads of black magic, etc..). You could no just send a tech guy to the ship to perform the migration, he would have to know what he is actually doing and why when executing the 100 lines script line by line. Some time in the future, we will commence another round of migration, (at any point in time we need to support all current working versions) but we will have to feel a substantial reason to do so. - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Παρ 15 Ιουν 2012 12:29:38 Samuel Gendler wrote: > On Fri, Jun 15, 2012 at 2:28 AM, Samuel Gendler > > wrote: > > On Fri, Jun 15, 2012 at 1:24 AM, Achilleas Mantzios < > > > > ach...@matrix.gatewaynet.com> wrote: > >> And i forgot to mention, minicom term emulation quality sucks, even > >> giving simple > >> shell commands is a PITA, upgrading the whole fleet would mean bast case > >> scenario > >> minimum 21K USD for the whole fleet + suspension of all other activities > >> for two months. > >> If physical travel was involved, the cost would be increased at even > >> higher levels. > > > > And what is the cost of data corruption on large numbers of systems? And > > how much to fix that, especially if multiple systems fail at the same > > time? > > > > Some things aren't free. $21K in exchange for NOT having had to keep > > > > systems up to date for 4 years seems like a decent trade. > > Just call up an oracle sales rep and get a price quote for a single > baseline system. Put that next to the postgresql upgrade cost for your > whole fleet. :) I know, I have used this argument sometimes successfully, sometimes not. The problem with oracle is not the price. The problem is that it just cannot do what postgresql does, or what we have managed to do ourselves with postgresql. Our replication system is unique, built in-house, and no commercial alternative existed, exists, or (most probably) will exist. - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Παρ 15 Ιουν 2012 18:03:26 Scott Marlowe wrote: > Not talking about going to something after 8.3.19, just updating to > the latest 8.3 version. On most systems it's a simple: > > sudo apt-get upgrade > > or similar and sit back and watch. Thanx, unfortunately we dont have TCP/IP connectivity to (most of) the ships, and AFAIK apt-get does not yet work over advanced UUCP/minicom/kermit or other equivalent hich-tech dial up connection. just joking :) > > On Fri, Jun 15, 2012 at 2:24 AM, Achilleas Mantzios > > wrote: > > On Παρ 15 Ιουν 2012 10:28:20 Scott Marlowe wrote: > >> You do realize you're missing four years of bug fixes right? > > > > On Πεμ 14 Ιουν 2012 11:39:35 Achilleas Mantzios wrote: > >> Unfortunately the remote installations are neither physically accessible > >> nor by TCP/IP accesible (comms are done via UUCP and administration via > >> minicom, and the costs are just huge 5 USD/min for 33Kbits/sec). So, i > >> would exhaust all posibilities before deciding to ship a new postgresql > >> version there, and remotely upgrade, physically travel to the ship or > >> even trying to do a backup/initdb/restore in the existing version. Any > >> help would be really really appreciated. > >> > >> Also, as you might have understood, upgrading, although generally a good > >> idea, does not apply so easily in our case. > > > > And i forgot to mention, minicom term emulation quality sucks, even > > giving simple shell commands is a PITA, upgrading the whole fleet would > > mean bast case scenario minimum 21K USD for the whole fleet + suspension > > of all other activities for two months. If physical travel was involved, > > the cost would be increased at even higher levels. > > > > > > - > > Achilleas Mantzios > > IT DEPT - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
Thanx, well said, and especially after this incident we should very well consider seriously an upgrade. On Παρ 15 Ιουν 2012 19:59:05 Scott Marlowe wrote: > Well, I'd see about finding a way to upgrade to 8.3.19. 8.3.3 has > know data eating bugs. > > On Fri, Jun 15, 2012 at 9:32 AM, Achilleas Mantzios > > wrote: > > On Παρ 15 Ιουν 2012 18:03:26 Scott Marlowe wrote: > >> Not talking about going to something after 8.3.19, just updating to > >> the latest 8.3 version. On most systems it's a simple: > >> > >> sudo apt-get upgrade > >> > >> or similar and sit back and watch. > > > > Thanx, unfortunately we dont have TCP/IP connectivity to (most of) the > > ships, and AFAIK apt-get does not yet work over advanced > > UUCP/minicom/kermit or other equivalent hich-tech dial up connection. > > just joking :) > > > >> On Fri, Jun 15, 2012 at 2:24 AM, Achilleas Mantzios > >> > >> wrote: > >> > On Παρ 15 Ιουν 2012 10:28:20 Scott Marlowe wrote: > >> >> You do realize you're missing four years of bug fixes right? > >> > > >> > On Πεμ 14 Ιουν 2012 11:39:35 Achilleas Mantzios wrote: > >> >> Unfortunately the remote installations are neither physically > >> >> accessible nor by TCP/IP accesible (comms are done via UUCP and > >> >> administration via minicom, and the costs are just huge 5 USD/min > >> >> for 33Kbits/sec). So, i would exhaust all posibilities before > >> >> deciding to ship a new postgresql version there, and remotely > >> >> upgrade, physically travel to the ship or even trying to do a > >> >> backup/initdb/restore in the existing version. Any help would be > >> >> really really appreciated. > >> >> > >> >> Also, as you might have understood, upgrading, although generally a > >> >> good idea, does not apply so easily in our case. > >> > > >> > And i forgot to mention, minicom term emulation quality sucks, even > >> > giving simple shell commands is a PITA, upgrading the whole fleet > >> > would mean bast case scenario minimum 21K USD for the whole fleet + > >> > suspension of all other activities for two months. If physical travel > >> > was involved, the cost would be increased at even higher levels. > >> > > >> > > >> > - > >> > Achilleas Mantzios > >> > IT DEPT > > > > - > > Achilleas Mantzios > > IT DEPT > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Σαβ 16 Ιουν 2012 03:22:16 you wrote: > On Fri, Jun 15, 2012 at 3:06 AM, Achilleas Mantzios < > > ach...@matrix.gatewaynet.com> wrote: > > On Παρ 15 Ιουν 2012 12:29:38 Samuel Gendler wrote: > > > On Fri, Jun 15, 2012 at 2:28 AM, Samuel Gendler > > > > > > wrote: > > > > On Fri, Jun 15, 2012 at 1:24 AM, Achilleas Mantzios < > > > > > > > > ach...@matrix.gatewaynet.com> wrote: > > > >> And i forgot to mention, minicom term emulation quality sucks, even > > > >> giving simple > > > >> shell commands is a PITA, upgrading the whole fleet would mean bast > > > > case > > > > > >> scenario > > > >> minimum 21K USD for the whole fleet + suspension of all other > > > > activities > > > > > >> for two months. > > > >> If physical travel was involved, the cost would be increased at even > > > >> higher levels. > > > > > > > > And what is the cost of data corruption on large numbers of systems? > > > > And > > > > > > how much to fix that, especially if multiple systems fail at the same > > > > time? > > > > > > > > Some things aren't free. $21K in exchange for NOT having had to keep > > > > > > > > systems up to date for 4 years seems like a decent trade. > > > > > > Just call up an oracle sales rep and get a price quote for a single > > > baseline system. Put that next to the postgresql upgrade cost for your > > > whole fleet. > > : > > :) I know, I have used this argument sometimes successfully, sometimes > > :not. > > > > The problem with oracle is not the price. The problem is that it just > > cannot do > > what postgresql does, or what we have managed to do ourselves with > > postgresql. > > Our replication system is unique, built in-house, and no commercial > > alternative existed, > > exists, or (most probably) will exist. > > Just to be clear, I wasn't, in any way, sugggesting you actually use > Oracle. I was merely suggesting that if someone is up in arms about > upgrading multiple systems for a cost of $21K, you might show them the > price of a single oracle license as a point of comparison. > It's ok, you were pretty clear, I was just trying to enhance/enrich our position with more arguments ;) > > - > > Achilleas Mantzios > > IT DEPT > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
We had another corruption incident on the very same machine, this time in the jboss subsystem (a "jar cvf" produced corrupted .jar). IMHO this means faulty RAM/disk. If that is true, then i guess HW sanity checks are even more important than SW upgrades. On Δευ 18 Ιουν 2012 11:36:14 you wrote: > Thanx, well said, and especially after this incident we should very well > consider seriously an upgrade. > > On Παρ 15 Ιουν 2012 19:59:05 Scott Marlowe wrote: > > Well, I'd see about finding a way to upgrade to 8.3.19. 8.3.3 has > > know data eating bugs. > > > > On Fri, Jun 15, 2012 at 9:32 AM, Achilleas Mantzios > > > > wrote: > > > On Παρ 15 Ιουν 2012 18:03:26 Scott Marlowe wrote: > > >> Not talking about going to something after 8.3.19, just updating to > > >> the latest 8.3 version. On most systems it's a simple: > > >> > > >> sudo apt-get upgrade > > >> > > >> or similar and sit back and watch. > > > > > > Thanx, unfortunately we dont have TCP/IP connectivity to (most of) the > > > ships, and AFAIK apt-get does not yet work over advanced > > > UUCP/minicom/kermit or other equivalent hich-tech dial up connection. > > > just joking :) > > > > > >> On Fri, Jun 15, 2012 at 2:24 AM, Achilleas Mantzios > > >> > > >> wrote: > > >> > On Παρ 15 Ιουν 2012 10:28:20 Scott Marlowe wrote: > > >> >> You do realize you're missing four years of bug fixes right? > > >> > > > >> > On Πεμ 14 Ιουν 2012 11:39:35 Achilleas Mantzios wrote: > > >> >> Unfortunately the remote installations are neither physically > > >> >> accessible nor by TCP/IP accesible (comms are done via UUCP and > > >> >> administration via minicom, and the costs are just huge 5 USD/min > > >> >> for 33Kbits/sec). So, i would exhaust all posibilities before > > >> >> deciding to ship a new postgresql version there, and remotely > > >> >> upgrade, physically travel to the ship or even trying to do a > > >> >> backup/initdb/restore in the existing version. Any help would be > > >> >> really really appreciated. > > >> >> > > >> >> Also, as you might have understood, upgrading, although generally a > > >> >> good idea, does not apply so easily in our case. > > >> > > > >> > And i forgot to mention, minicom term emulation quality sucks, even > > >> > giving simple shell commands is a PITA, upgrading the whole fleet > > >> > would mean bast case scenario minimum 21K USD for the whole fleet + > > >> > suspension of all other activities for two months. If physical > > >> > travel was involved, the cost would be increased at even higher > > >> > levels. > > >> > > > >> > > > >> > - > > >> > Achilleas Mantzios > > >> > IT DEPT > > > > > > - > > > Achilleas Mantzios > > > IT DEPT > > > > > > -- > > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-sql > > - > Achilleas Mantzios > IT DEPT - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Τετ 20 Ιουν 2012 07:08:09 Craig Ringer wrote: > On 06/19/2012 05:17 PM, Achilleas Mantzios wrote: > > We had another corruption incident on the very same machine, this time in > > the jboss subsystem (a "jar cvf" produced corrupted .jar). IMHO this > > means faulty RAM/disk. > > If that is true, then i guess HW sanity checks are even more important > > than SW upgrades. > > ... and a lot more difficult :S > > Log monitoring is often the most imporant part - monitoring for NMIs and > other hardware notifications, checking the kernel log for odd issues or > reports of unexpected segfaults from userspace programs, etc. > That's right, we have written a whole framework for this, but there are always cases which escape our attention. > -- > Craig Ringer - Achilleas Mantzios IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] find sudstring on text (data type) field failure
Gia sou Panteli, are you suggesting that the results are incinsistent and unpredictable with every run? pls present a case demontsrating the problem. On Τετ 07 Νοε 2012 10:36:34 pantelis vlachos wrote: I was trying to find a substring on a text (data type) column like 'cat foo dog ...'. I use the query below SELECT id FROM table WHERE name LIKE '% foo %'; Sometimes the query return with nTuples=0 but there are matching rows. On retry, the query return with expected results. Any ideas; (postgres ver 9.2, libpq - C Library) - Achilleas Mantzios IT DEPT
[SQL] bug in 9.2.2 ? subquery accepts wrong column name : upd
dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY
sst.setid ORDER BY character_length(vsl.name))) as arragg
FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY
sst.setid) as qry
WHERE array_length(qry.arragg,1)>1 AND qry.setid IN (SELECT setid from
sis_oper_cons) ORDER BY qry.setid,array_length(qry.arragg,1);
setid | arragg
---+--
54 | {EQUZZZ,SAMZZZ}
55 | {"ZZZR","ZZZTRAVEL"}
81 | {"ZZZ SISTER","ZZZ DUMMY II"}
(3 rows)
however, there is not column setid in sis_oper_cons,
dynacom=# SELECT setid from sis_oper_cons;
ERROR: column "setid" does not exist
LINE 1: SELECT setid from sis_oper_cons;
^
9.2.2 Postgresql treats qry.setid IN (SELECT setid from sis_oper_cons) as true.
However, making subquery look like (SELECT soc.setid from sis_oper_cons soc),
as in
dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY sst.setid
ORDER BY character_length(vsl.name))) as arragg FROM sissets sst, vessels vsl
WHERE vsl.id=sst.vslid ORDER BY sst.setid) as qry WHERE
array_length(qry.arragg,1)>1 AND qry.setid NOT IN (SELECT soc.setid from
sis_oper_cons soc) ORDER BY qry.setid,array_length(qry.arragg,1);
ERROR: column soc.setid does not exist
LINE 1: ...gth(qry.arragg,1)>1 AND qry.setid NOT IN (SELECT soc.setid ...
^
dynacom=#
postgresql corerctly identifies and throws the error.
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bug in 9.2.2 ? subquery accepts wrong column name : upd
Thanx Tom. On Πεμ 14 ÎÎ±Ï 2013 12:17:46 Tom Lane wrote: > Achilleas Mantzios writes: > > dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM > > (select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY > > sst.setid ORDER BY character_length(vsl.name))) as arragg > > FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY > > sst.setid) as qry > > WHERE array_length(qry.arragg,1)>1 AND qry.setid IN (SELECT setid > > from sis_oper_cons) ORDER BY qry.setid,array_length(qry.arragg,1); > > [ works ] > > > however, there is not column setid in sis_oper_cons, > > If not, that's a perfectly legal outer reference to qry.setid. > > Probably not one of SQL's better design features, since it confuses > people regularly; but it's required by spec to work like that. > > regards, tom lane > > > - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correct implementation of 1:n relationship with n>0?
On Ôñé 30 Áðñ 2013 16:39:05 Wolfgang Keller wrote: > It hit me today that a 1:n relationship can't be implemented just by a > single foreign key constraint if n>0. I must have been sleeping very > deeply not to notice this. > > E.g. if there is a table "list" and another table "list_item" and the > relationship can be described as "every list has at least one > list_item" (and every list_item can only be part of one list, but > this is trivial). > > A "correct" solution would require (at least?): > > 1. A foreign key pointing from each list_item to its list > > 2. Another foreign key pointing from each list to one of its list_item. > But this must be a list_item that itself points to the same list, so > just a simple foreign key constraint doesn't do it. > > 3. When a list has more than one list_item, and you want to delete the > list_item that its list points to, you have to "re-point" the foreign > key constraint on the list first. Do I need to use stored proceures > then for all insert, update, delete actions? > > (4. Anything else that I've not seen?) > > Is there a "straight" (and tested) solution for this in PostgreSQL, that > someone has already implemented and that can be re-used? > > No, I definitely don't want to get into programming PL/PgSQL myself. > especially if the solution has to warrant data integrity under all > circumstances. Such as concurrent update, insert, delete etc. > I think your best bet is a trigger. use RAISE EXCEPTION to indicate an erroneous situation so as to make the transaction abort. (there is nothing wrong in getting your hands dirty with pl/pgsql btw) > TIA, > > Sincerely, > > Wolfgang > > > - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correct implementation of 1:n relationship with n>0?
On Ôñé 30 Áðñ 2013 20:25:42 Wolfgang Keller wrote: > > (there is nothing wrong in getting your hands dirty with pl/pgsql btw) > > The point is that I would have expected that problem to be solved > within the past four decades since relational databases have been > invented. Or at least in the past two decades since PostgreSQL has been > developed. > Then what about n>1, n>2, n>k where k an arbitrarily large positive integer? isn't it the same problem class actually? Is there any serious database vendor who provides out of the box support for 1:n, n>0 ? Or is it an "unusual" user requirement in the first place. Ever thought why not so many people have asked for this? Anyway, IMHO, the need for more programming/programmers increases with the years, engineering becomes more complex, you can't expect serious contgrol in anything without getting under the hood. > >;-> > > After all, this should be really an ultra-classic-standard FAQ item. > > I'm definitely not the first database user in the world who needs to > have a 1:n relationship with n>0 and integrity enforced by the > database. > > Sincerely, > > Wolfgang > > > - Achilleas Mantzios IT DEV IT DEPT -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correct implementation of 1:n relationship with n>0?
On Ôñé 30 Áðñ 2013 20:25:42 Wolfgang Keller wrote: > > (there is nothing wrong in getting your hands dirty with pl/pgsql btw) > > The point is that I would have expected that problem to be solved > within the past four decades since relational databases have been > invented. Or at least in the past two decades since PostgreSQL has been > developed. > Then what about n>1, n>2, n>k where k an arbitrarily large positive integer? isn't it the same problem class actually? Is there any serious database vendor who provides out of the box support for 1:n, n>0 ? Or is it an "unusual" user requirement in the first place. Ever thought why not so many people have asked for this? Anyway, IMHO, the need for more programming/programmers increases with the years, engineering becomes more complex, you can't expect serious contgrol in anything without getting under the hood. > >;-> > > After all, this should be really an ultra-classic-standard FAQ item. > > I'm definitely not the first database user in the world who needs to > have a 1:n relationship with n>0 and integrity enforced by the > database. > > Sincerely, > > Wolfgang > > > - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UNNEST result order vs Array data
On 20/06/2013 13:45, Vik Fearing wrote: On 06/20/2013 12:40 PM, gmb wrote: Hi all I just want to confirm something regarding UNNEST function used with arrays. I cannot see that it is specifically mentioned in the documentation , but maybe because it is obvious. Is the order of the result guaranteed to be the order of the array Order is never guaranteed unless you specify an ORDER BY clause. I really think that the order should exactly reflect the order in the array. Think of ARRAY as Relation + ORDER. Relation + Order gives an ARRAY, ARRAY should give Relation *and* ORDER in return. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] i have table
Στις Τρίτη 03 Οκτώβριος 2006 13:47, ο/η Penchalaiah P. έγραψε:
> Hi ...
>
> I have one table with 12 fields..
>
>
>
> CREATE TABLE addition_alteration_memo
>
> (
>
> addition_alteration_memo int8 NOT NULL DEFAULT
> nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclas
> s),
>
> cda_no varchar(7) NOT NULL,
>
> week numeric,
>
> sheet_no numeric,
>
> serial_no numeric,
>
> date date,
>
> dr_no varchar,
>
> amount numeric,
>
> memo_no varchar,
>
> memo_date date,
>
> no_instalments numeric,
>
> instalment_rate numeric)
>
>
>
> now I want to add one more field in this table.. but that field has to
> come next to cda_no.. I mean as a 3rd field.. If I am adding that field
> it is coming last field ...
>
> may I know how it is possible to that table...
>
2 notes:
1) Why do you feel that this presentational reordering is important. Normally
it should not be important, otherwise some engineering flaw is hanging
around.
2) If you are sure it is important, one way to do this, is dump, change the
order of the field in the .sql and restore.
>
>
> Thanks & Regards
>
> Penchal reddy | Software Engineer
>
> Infinite Computer Solutions | Exciting Times...Infinite Possibilities...
>
>
> SEI-CMMI level 5 | ISO 9001:2000
>
> IT SERVICES | BPO
>
>
> Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities |
> Retail & Distribution | Government
>
>
> Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No
> +91-9980012376|www.infics.com
>
> Information transmitted by this e-mail is proprietary to Infinite
> Computer Solutions and/ or its Customers and is intended for use only by
> the individual or entity to which it is addressed, and may contain
> information that is privileged, confidential or exempt from disclosure
> under applicable law. If you are not the intended recipient or it
> appears that this mail has been forwarded to you without proper
> authority, you are notified that any use or dissemination of this
> information in any manner is strictly prohibited. In such cases, please
> notify us immediately at [EMAIL PROTECTED] and delete this mail from
> your records.
>
>
>
>
>
> Information transmitted by this e-mail is proprietary to Infinite Computer
> Solutions and / or its Customers and is intended for use only by the
> individual or the entity to which it is addressed, and may contain
> information that is privileged, confidential or exempt from disclosure
> under applicable law. If you are not the intended recipient or it appears
> that this mail has been forwarded to you without proper authority, you are
> notified that any use or dissemination of this information in any manner is
> strictly prohibited. In such cases, please notify us immediately at
> [EMAIL PROTECTED] and delete this email from your records.
--
Achilleas Mantzios
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] i have table
Στις Τετάρτη 04 Οκτώβριος 2006 18:37, ο/η Erik Jones έγραψε: > Aaron Bono wrote: > > On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> > > wrote: > > > > There is one non-SQL related reason that I like to be able to order > > columns, at least the way they are displayed whenever the table is > > described: human comprehension. For example, I like to group all > > keys > > in a table before data, that includes primary as well as foreign > > keys. > > So, say I'm building on to an existing application and I need to do > > an ALTER TABLE on an existing table to add a foreign key to an existing > > table. I'd like that key to be listed with the other keys, but presently > > that's not possible in a simple way and, to be honest, I usually just go > > without as the process you've described below is too prone to user > > (human) error when dealing with live, sensitive data for me to want to > > mess with it. > > > > > > Ah, but it is possible... if you use views. > > > > I recommend you build views and query off them. Then you can control > > the order the columns will appear. > > Which would be great if I didn't have (many) thousands of lines of code > that already use the tables. Besides, this is no where near a 'make or > break' thing. It's just a matter of aesthetic preference. Alright, you could play with something like: UPDATE pg_attribute SET attnum = where attrelid= and attname=''; but do some research of possible bad side effects. -- Achilleas Mantzios ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] i have table
Στις Πέμπτη 05 Οκτώβριος 2006 16:31, ο/η Tom Lane έγραψε: > Achilleas Mantzios <[EMAIL PROTECTED]> writes: > > Alright, you could play with something like: > > > > UPDATE pg_attribute SET attnum = where > > attrelid= and attname=''; > > That's guaranteed to break his table, because the physical storage > of the rows won't have changed. (To name only the most obvious > problem...) Ooops Thanx for the info, altho i warned the guy to search before act. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Achilleas Mantzios ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] How to split a table?
Στις Τρίτη 17 Οκτώβριος 2006 11:34, ο/η Andreas Kretschmer έγραψε: > Felix Zhang <[EMAIL PROTECTED]> schrieb: > > Hi, > > > > I want to split a table to 2 small tables. The 1st one contains 60% > > records which are randomly selected from the source table. > > How to do it? > > Why do you want to do this? In any case, you could write a program in perl and insert into the second table rows from the first table, using a techique of generarting random double precision numbers in a predetermined range (lets say 0 to 2^31-1), and then converting this number to your "range" like this: let your number be r. let your table's cardinality be N. then let R=(N*r/(2^31))+1 select a source row from your table like: select * from srctable order by pk offset , and then insert this row to your desttable. > > > Andreas -- Achilleas Mantzios ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: Re: [SQL] [GENERAL] How to split a table?
I forgot to mention that you would have to maintain a counter of each inserted row and stop when you reach 60% of N (where N the cardinality of your source table). -- Προωθημένο Μήνυμα -- Subject: Re: [SQL] [GENERAL] How to split a table? Date: Τρίτη 17 Οκτώβριος 2006 12:09 From: Achilleas Mantzios <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Στις Τρίτη 17 Οκτώβριος 2006 11:34, ο/η Andreas Kretschmer έγραψε: > Felix Zhang <[EMAIL PROTECTED]> schrieb: > > Hi, > > > > I want to split a table to 2 small tables. The 1st one contains 60% > > records which are randomly selected from the source table. > > How to do it? > > Why do you want to do this? In any case, you could write a program in perl and insert into the second table rows from the first table, using a techique of generarting random double precision numbers in a predetermined range (lets say 0 to 2^31-1), and then converting this number to your "range" like this: let your number be r. let your table's cardinality be N. then let R=(N*r/(2^31))+1 select a source row from your table like: select * from srctable order by pk offset , and then insert this row to your desttable. > Andreas -- Achilleas Mantzios --- -- Achilleas Mantzios ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] delete on cascade
Στις Δευτέρα 23 Οκτώβριος 2006 11:49, ο/η Luca Ferrari έγραψε: > Hi all, > I guess this is an already asked question, but I didn't found an answer, so > apologize me. Imagine I've got two tables: > skill(id,description) // primary key => id > family(id,description)// primary key => id > and I want to associate skills to families: > ass_sf(id_skill,id_family)// appropriate foreign keys > > Tables are already created and the database is running. Now I'd like to > implement a delete cascade, thus when I delete a skill also its association > with the family must be deleted. I gues I should have declared the skill > table as follows: > CREATE TABLE skill > ( > id varchar(20) on delete cascade, > description varchar(50), > primary key(id) > ); > > right? The problem is: how can I alter the table to add the column not right!!! ON DELETE CASCADE is specified in FOREIGN KEY contsraints. So that would be in ass_sf table. If you find ALTER TABLE ass_sf command hard to run, then drob your ass_sf table and define it like CREATE TABLE ass_sf ( . skill_id INT REFERENCES skill(id) ON DELETE CASCADE, familly_id INT REFERENCES family(id) ON DELETE CASCADE ); The above causes cascade deletes not only on skills but on families also. > constraint now, without redeclaring the table? > Thanks very much for helping me. > Luca > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not >match -- Achilleas Mantzios ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] delete on cascade
Στις Δευτέρα 23 Οκτώβριος 2006 12:50, ο/η Luca Ferrari έγραψε: > On Monday 23 October 2006 11:00 Achilleas Mantzios's cat, walking on the > > keyboard, wrote: > > not right!!! > > ON DELETE CASCADE is specified in FOREIGN KEY contsraints. > > So that would be in ass_sf table. > > If you find ALTER TABLE ass_sf command hard to run, then drob your > > ass_sf table and define it like > > Sorry, but this would define that when I delete a row on ass_sf also the > skin and the family will be deleted, right? Not right. This would specify quite the opposite. > I would like to get the > opposite behaviour: when I delete a skill also its association on ass_sf > will be deleted. Is this possible? Absolutely, and this is done in the way i just described. In any case I would recommend reading the docs. > > Thanks, > Luca > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Achilleas Mantzios ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Numbers
Στις Παρασκευή 24 Νοέμβριος 2006 15:07, ο/η Ezequias Rodrigues da Rocha έγραψε: > Hi list, > > Does anybody have numbers of PostgreSQL in action ? > > Numbers like the biggest insert in mileseconds, what do you mean here? LOCK TABLE in ACCESS EXCLUSIVE MODE; and the insert will take forever. > the larger database etc ? > > > Regards... -- Achilleas Mantzios ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query a select that returns....
Στις Τρίτη 19 Δεκέμβριος 2006 16:01, ο/η Carlos Santos έγραψε: > Hi! > I need to query a select that returns all the fields of an specific primary > key, but I don't have the single column's name that is constrained as > primary key. How can I do that? > Something like: > SELECT * FROM myTable WHERE myTable.pkey = 'foo'; First by SELECT pgc.conkey from pg_class pgcl,pg_constraint pgc where pgcl.relname='your table name' and pgcl.oid=pgc.conrelid and pgc.contype='p'; you get the attribute numbers of the primary key. Then you have to lookup pg_attribute to find the column names. In the simplified case where the primary key is consisted of only one attribute (column), then SELECT pgat.attname from pg_class pgcl,pg_constraint pgc,pg_attribute pgat where pgcl.relname='your table name' and pgcl.oid=pgc.conrelid and pgc.contype='p' and pgat.attrelid=pgcl.oid and attnum=pgc.conkey[1]; should give you the attribute name of the primary key. Then you build your query from your program accordingly. > > Thanks > > Carlos Henrique Iazzetti Santos > Compels Inform�tica > Santa Rita do Sapuca� - MG > www.compels.net > > > > > > > ___ > O Yahoo! est� de cara nova. Venha conferir! > http://br.yahoo.com -- Achilleas Mantzios ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Intersection of two date interval
Στις Παρασκευή 26 Ιανουάριος 2007 11:50, ο/η A. Kretschmer έγραψε:
> am Fri, dem 26.01.2007, um 11:39:03 +0200 mailte Suha Onay folgendes:
> > Hi,
> >
> > How can i find the number of days in the intersection of 2 date interval?
> > For example:
> > 1st interval: (10.01.2007, 20.01.2007)
> > 2nd interval: (13.01.2007, 21.01.2007)
> > The intersection dates are: 13,14,15,16,17,18,19, 20
> > The result is: 8
> >
> > How can i find the result, 8 in an sql query without using CASE
> > statements?
>
> Some time ago i wrote a function for this, i hope it's helpfull for you:
> (not realy tested, be careful!)
>
>
>
> create type start_end as (t1 timestamptz, t2 timestamptz);
>
> create or replace function time_intersect (timestamptz, timestamptz,
> timestamptz, timestamptz) returns start_end as $$ declare
> _s1 alias for $1;
> _e1 alias for $2;
> _s2 alias for $3;
> _e2 alias for $4;
> _start timestamptz;
> _endtimestamptz;
> _return start_end;
> begin
>
> if _s1 < _s2 then
> _start := _s2;
> else
> _start := _s1;
> end if;
>
> if _e1 < _e2 then
> _end := _e1;
> else
> _end := _e2;
> end if;
>
> if _start < _end then
> _return.t1 := _start;
> _return.t2 := _end;
> else
> _return.t1 := NULL;
> _return.t2 := NULL;
> end if;
>
> return _return;
> end
> $$ language plpgsql;
>
>
Suha,
the function is the number of days in the
maximum of the two start dates , untill , minimum of the two end dates
interval.
But in postgresql (7.4.15 at least) there is no MIN(date,date),MAX(date,date)
functions. So someone has to write them, so you cant avoid some logic there.
Whats your problem with "CASE" statements?
what you are basically looking for is smth like the above implementation
from Andreas.
> test=# set datestyle=german;
> SET
> test=*# select time_intersect('10.01.2007'::date, '20.01.2007'::date,
> '13.01.2007'::date, '21.01.2007'::date); time_intersect
> ---
> ("13.01.2007 00:00:00 CET","20.01.2007 00:00:00 CET")
> (1 row)
>
>
> test=*# select *, t2-t1 from time_intersect('10.01.2007'::date,
> '20.01.2007'::date, '13.01.2007'::date, '21.01.2007'::date); t1
> | t2| ?column?
> -+-+--
> 13.01.2007 00:00:00 CET | 20.01.2007 00:00:00 CET | @ 7 days
> (1 row)
>
>
>
>
> Andreas
--
Achilleas Mantzios
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Seeking quick way to clone a row, but give it a new pk.
Στις Πέμπτη 08 Φεβρουάριος 2007 09:19, ο/η Bryce Nesbitt έγραψε: > Philip Hallstrom wrote: > >> I need to create some nearly identical copies of rows in a complicated > >> table. > >> > >> Is there a handy syntax that would let me copy a existing row, but get a > >> new primary key for the copy? I'd then go in an edit the 1 or 2 > >> additional columns that differ. The duplicate would be in the same > >> table as the original. > >> > >> This would save me a bunch of typing. Can it be done? > > > > INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123; > > > > Or something close to that... I suspect if you changed the '*' to the > > columns you wanted you could also work in the other columns you want > > to change as well... > > But that will violate the unique primary key constraint: > > insert into xx_plan_rule select * from xx_plan_rule where rule_id=9; > ERROR: duplicate key violates unique constraint "xx_plan_rule_pkey" If you have that requirement often i would recommend writing a program taking the tablename,id as args, read DB metadata and act accordingly. -- Achilleas Mantzios ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] can someone explain confusing array indexing nomenclature
Στις Τετάρτη 14 Φεβρουάριος 2007 21:31, ο/η chrisj έγραψε:
> given the following table:
>
> protocal2=> select * from sal_emp ;
> name | pay_by_quarter | schedule
> ---+---+---
> Bill | {1,1,1,1} |
> {{meeting,lunch},{training,presentation}}
> Carol | {2,25000,25000,25000} |
> {{breakfast,consulting},{meeting,lunch}}
> (2 rows)
>
> why do the following two queries yield different results??
>
> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> schedule
> --
> lunch
> (1 row)
>
> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> schedule
> ---
> {{meeting,lunch}}
> (1 row)
The [n:m] notation denotes a slice of the array (not element).
So schedule[1][2] is the Array element on 2nd col of 1st row,
while schedule[1:1][2] could mean
the second row of the subarray schedule[1:1][1:2].
So these two are foundamentally different things.
In my 7.4 even if you gave
SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
you would still get {{meeting,lunch}} as a result.
(Right or wrong is another story).
Anyway the first time you query for a "text",
the second time you query for a "text[]", so you should expect
different results.
--
Achilleas Mantzios
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] can someone explain confusing array indexing nomenclature
Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
> Thanks Achilleas,
>
> I see what you are saying, but if we consider just the index "[2]" for a
> moment,
> it means something different depending upon the context (in one case it
> means "2" and in the other case it means "1:2") and the context is
> determined by the format of indexes on other dimensions.
>
> I believe I understandbut incredibly confusing.
>
Now that i think about it again, i speculate that the [2] is discarded.
> - chris
>
> Achilleas Mantzios wrote:
> > Ξ£ΟΞΉΟ Ξ�Ξ΅ΟΞ¬ΟΟΞ· 14 ΦΡβΟΞΏΟ
Ξ¬ΟΞΉΞΏΟ 2007 21:31, ΞΏ/Ξ· chrisj
ΞΞ³ΟΞ±ΟΞ΅:
> >> given the following table:
> >>
> >> protocal2=> select * from sal_emp ;
> >> name | pay_by_quarter | schedule
> >> ---+---+
> >>--- Bill | {1,1,1,1} |
> >> {{meeting,lunch},{training,presentation}}
> >> Carol | {2,25000,25000,25000} |
> >> {{breakfast,consulting},{meeting,lunch}}
> >> (2 rows)
> >>
> >> why do the following two queries yield different results??
> >>
> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> >> schedule
> >> --
> >> lunch
> >> (1 row)
> >>
> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> >> schedule
> >> ---
> >> {{meeting,lunch}}
> >> (1 row)
> >
> > The [n:m] notation denotes a slice of the array (not element).
> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> > while schedule[1:1][2] could mean
> > the second row of the subarray schedule[1:1][1:2].
> > So these two are foundamentally different things.
> > In my 7.4 even if you gave
> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> > you would still get {{meeting,lunch}} as a result.
> > (Right or wrong is another story).
> > Anyway the first time you query for a "text",
> > the second time you query for a "text[]", so you should expect
> > different results.
> > --
> > Achilleas Mantzios
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] can someone explain confusing array indexing nomenclature
Στις Παρασκευή 16 Φεβρουάριος 2007 20:35, ο/η chrisj έγραψε:
> I am quite sure the [2] is not discarded, easy enough to test but I don't
> have access to PG at the moment.
Well it should, since
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
text
-
{{meeting}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
text
---
{{meeting,lunch}}
(1 row)
dynacom=#
>
> Achilleas Mantzios wrote:
> > Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
> >> Thanks Achilleas,
> >>
> >> I see what you are saying, but if we consider just the index "[2]" for a
> >> moment,
> >> it means something different depending upon the context (in one case it
> >> means "2" and in the other case it means "1:2") and the context is
> >> determined by the format of indexes on other dimensions.
> >>
> >> I believe I understandbut incredibly confusing.
> >
> > Now that i think about it again, i speculate that the [2] is discarded.
> >
> >> - chris
> >>
> >> Achilleas Mantzios wrote:
> >> > Ξ£ΟΞΉΟ Ξ�Ξ΅ΟΞ¬ΟΟΞ· 14 ΦΡβΟΞΏΟ
Ξ¬ΟΞΉΞΏΟ 2007 21:31, ΞΏ/Ξ·
> >>
> >> chrisj
> >
> > ΞΞ³ΟΞ±ΟΞ΅:
> >> >> given the following table:
> >> >>
> >> >> protocal2=> select * from sal_emp ;
> >> >> name | pay_by_quarter | schedule
> >>
> >> ---+---+
> >>
> >> >>--- Bill | {1,1,1,1} |
> >> >> {{meeting,lunch},{training,presentation}}
> >> >> Carol | {2,25000,25000,25000} |
> >> >> {{breakfast,consulting},{meeting,lunch}}
> >> >> (2 rows)
> >> >>
> >> >> why do the following two queries yield different results??
> >> >>
> >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> >> >> schedule
> >> >> --
> >> >> lunch
> >> >> (1 row)
> >> >>
> >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> >> >> schedule
> >> >> ---
> >> >> {{meeting,lunch}}
> >> >> (1 row)
> >> >
> >> > The [n:m] notation denotes a slice of the array (not element).
> >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> >> > while schedule[1:1][2] could mean
> >> > the second row of the subarray schedule[1:1][1:2].
> >> > So these two are foundamentally different things.
> >> > In my 7.4 even if you gave
> >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> >> > you would still get {{meeting,lunch}} as a result.
> >> > (Right or wrong is another story).
> >> > Anyway the first time you query for a "text",
> >> > the second time you query for a "text[]", so you should expect
> >> > different results.
> >> > --
> >> > Achilleas Mantzios
> >> >
> >> > ---(end of
> >>
> >> broadcast)---
> >>
> >> > TIP 3: Have you checked our extensive FAQ?
> >> >
> >> >http://www.postgresql.org/docs/faq
> >
> > --
> > Achilleas Mantzios
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] reindex database
Στις Τετάρτη 14 Μάρτιος 2007 14:44, ο/η Sabin Coanda έγραψε: > Hi, > > It would be so nice to use REINDEX DATABASE command without name, for the > current database. > Now the command is useless when a database schema script refers just > itself, and the database name is not establish. I have to use REINDEX TABLE > name instead searching all the database tables. > > What is your opinion ? /contrib/reindexdb/reindexdb -a reindexes everything. Also your script would do a #!/bin/csh set mydb=`psql -q -t -c "SELECT current_database()" | tr -d " \n"` /contrib/reindexdb/reindexdb -d $mydb > > Regards, > Sabin > > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- Achilleas Mantzios ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] We all are looped on Internet: request + transport = invariant
Just some thoughts on the matter: Dmitry, you should acknowledge the fact that in databses you have two kinds of hierarchies: a) The hierarchy denoted by the referential constraints between tables which constitute a Directed Graph (DG), with tables as nodes and FKeys as arrows. b) Hierarchies applying to data, e.g. create table ppl ( id serial PRIMARY KEY, name text not null, parentid integer REFERENCES ppl(id) ); (with GOD himself having parentid = null :). I think that the second type is of interest at the application level. (For system applications however, such as DB mirroring the first type of table hierarchy is important as well). Speaking of postgresql there is a fine package by the GiST/tsearch creators, contrib/ltree whose main task is to provide the primitives (functions/operators) for tree manipulation. Dont know if i am irrelevant in this thread, but i hope i helped a little bit. -- Achilleas Mantzios ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] query to select a linked list
Στις Τετάρτη 09 Μάιος 2007 15:55, ο/η Louis-David Mitterrand έγραψε:
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> --
> id_forum | integer| not null default
> nextval('forum_id_forum_seq'::regclass) id_parent| integer|
> subject | text | not null
> message | text |
>
> Each message a unique id_forum and an id_parent pointing to the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a thread?
>
Your question is about storing hierarchies in postgresql.
One way is to use the genealogical approach, where we store for
any node the path to its root.
I have used this technique to store description of tanker vessels machinery
(over 1M items) and the plan maintenance on them, and the performance is very
good, while the representation is highly intuitive and flexible,
unlike some wierd approcahes i have hit on.
When i did a small research on the complexity/index usage on various
operations (UPDATE, INSERT, DELETE, SELECT), the performance
was at least as good as the "nested pair" approch that many seemed to
promote.
You add a column "parents" (rather than just the parentid) as an integer[].
For every node you store the path to the root node starting from the most
immediate ancestor.
Then you just make an index on this column using the intarray contrib package.
Then you can easily query for nodes under a specific node, or for nodes just
one level below a specific node, nodes with no descendents (leaf nodes)
etc...
Of course you could do smth simpler, but in the long run,
representing data in the correct way will certainly pay off.
> Thanks,
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--
Achilleas Mantzios
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] yet another simple SQL question
Στις Τρίτη 26 Ιούνιος 2007 09:40, ο/η John Summerfield έγραψε: > Joshua wrote: > > Ok, > > > > You guys must be getting sick of these newbie questions, but I can't > > resist since I am learning a lot from these email > > I'm not fond of people using meaningless subjects, or of people > simultaneously posting the same message to other lists. If one chooses a > meaningless subject, I mostly ignore the question. The subject is of clasical unix flavor, since it points back to those wild YACC years of our youth, so i think most unix grown ups kind of like subjects like that. > > Subjects such as yours don't cut the mustard. Try to summarise your > problem; if I'm interested in the problem then I will read it and > (maybe) help. > > When I find it's cross-posted, I'm likely to change my mind. Why do you think that criticizing was better than staying silent? Anyway, Joshua already took some very enlightening answers enuf to get him going. -- Achilleas Mantzios ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Where clause
Στις Τρίτη 26 Ιούνιος 2007 12:44, ο/η Michael Landin Hostbaek έγραψε: > A. Kretschmer (andreas.kretschmer) writes: > > *untested* > > > > select cid, sum(case when click = true then 1 else 0 end), sum(case when > > view = true then 1 else 0 end) from ... > > Thanks, but I need the DISTINCT contactid - I don't want the same > contactid counted twice. > Something like SELECT distinct cid,(select count(distinct t1.contactid) from tracking t1 where t1.view and t1.cid=tglob.cid) as countviews,(select count(distinct t2.contactid) from tracking t2 where t2.click and t2.cid=tglob.cid) as countclicks from tracking tglob; ? > Mike > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Achilleas Mantzios ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Having the sum of two queries
Στις Δευτέρα 16 Ιούλιος 2007 14:06, ο/η Dani Castaños έγραψε: > Hi all! > > I want to do something like this: > > SELECT status, COUNT( status ) AS total > FROM users > GROUP BY status > > UNION > > SELECT status, COUNT(status) AS total > FROM imported_users > GROUP BY status > > > And have the result of both added. > > I've tried something like > > SELECT tot.status, COUNT(total) > FROM( QUERY A UNION QUERY B ) AS tot > GROUP BY tot.status > > But it doesn't works. It doesn't add the total columns with the same > status... It should also be UNION ALL, just in case some status'es are common in the 2 subqueries. > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Achilleas Mantzios ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] strange result for select * from table where id=random()*900
Στις Παρασκευή 27 Ιούλιος 2007 10:48, ο/η Stefan Zweig έγραψε: > hi list, > > this is my query: > > select > * > from _g2977 > where > id=floor(random() * (900)); > > in table _g2977 i have id(s) from 1 up to 900. i just want to select one > random dataset by the above query but i does not work. > > actually i get sometime zero, sometimes one, sometimes two and sometimes > even three results back from the above query although i thought it should > give only one random dataset from the table. > > what is wrong? A lot of things seem wierd: a) Why do you want such a query for? b) Is "id" a UNIQUE KEY? If yes, you should get ONE or ZERO rows from the above query. If not, you should get a number of rows according to the number of rows in the table with this specific "id" value. c) why returning (as you put it in the first place) sometimes 1,2,3 rows is different than returning a "random" dataset? If "id" was a PRIMARY KEY, maybe it would have sense to do smth like select * from table where id between floor(random() * 900) and floor(random() * 900); (note this might return zero rows as well). Note also that by random, we could very well mean the empty set, so there is no problem with the above. But, i feel from your text, that something is highly misunderstood, especially the way you set the question. > > i appreciate any help. > > thanks in advance, stefan > _ > In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! > Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114 > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Achilleas Mantzios ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ISO time zone format
Στις Τετάρτη 05 Σεπτέμβριος 2007 10:30, ο/η Sabin Coanda έγραψε: > Hi there, > > I'd like to format a time stamp with time zone as ISO pattern (e.g. > 1999-01-08 04:05:06 -8:00) > > I found the patterns for all the timestamp parts, from "Data Type > Formatting Functions" Postgresql documentation, excepting the numeric ISO > time zone. > > Please suggest the appropiate pattern for that. Have you tried the default PgSQL behaviour, without setting DateStyle, or doing any explicit formating? Maybe its exactly what you want. What does select now(); from within psql, output in your system? > > TIA, > Sabin > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match -- Achilleas Mantzios ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] show value of backslashes in string array argument
If you do SELECT "colVarchar","colVarcharArray"[1] FROM test; you will see that you get identical values. -- Achilleas Mantzios ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] show value of backslashes in string array argument
Στις Δευτέρα 12 Νοέμβριος 2007 11:51, ο/η Sabin Coanda έγραψε:
> I fond another collateral problem, because there are the different
> convention to describe a varchar array item which contains backslashes,
> when standard_conforming_strings = 'on'
>
> For instance, to get a string composed by just one character backslash I
> can use any of the two forms:
>
> SELECT ARRAY[ '\' ]::varchar[];
>
> or
>
> SELECT '{\\}'::varchar[];
>
> On the other hand, standard_conforming_strings = 'on' let me use varchar
> items with '\' format. So the first format seems to be aware of
> standard_conforming_strings = 'on', but the second is not.
>
> My problem is that the java driver build arrays using the second format,
> but the driver seems to be aware of standard_conforming_strings = 'on'.
> This make inconsistence using the statement parameters, because to get the
> same thing in the database I have to initialize a varchar parameter with a
> string of one backslashes, but a varchar array item has to be initialized
> with a string of two backslashes.
>
> Sabin
I would recommend:
a) Move to Unix
b) Subscribe to [EMAIL PROTECTED]
>
>
>
> ---(end of broadcast)-------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Achilleas Mantzios
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Calling functions from within pl/pgsql
I noticed that in pl/pgsql simply result := funcname(func,args,...); just works. Should i rely on it or take it as a coincidence? :) -- Achilleas Mantzios ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Calling functions from within pl/pgsql
Στις Tuesday 04 December 2007 14:59:43 ο/η Pavel Stehule έγραψε: > On 04/12/2007, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > > I noticed that in pl/pgsql simply > > result := funcname(func,args,...); > > just works. > > Should i rely on it or take it as a coincidence? :) > > You found assignment :). Sure, you can use it. > http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPG >SQL-STATEMENTS-ASSIGNMENT Sure, i missed the "As explained above, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine. The expression must yield a single value." part. Thanx. > > Regards > Pavel Stehule > > > -- > > Achilleas Mantzios > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend -- Achilleas Mantzios ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Update PK Violation
Στις Tuesday 15 January 2008 23:03:49 ο/η Franklin Haut έγραψε: > Hi all, > > i have a problem with one update sentence sql. > A simple way i use: foodb=# update temp set num = num*1000 where num >= 5; foodb=# insert into temp values (5, 'NOT'); foodb=# update temp set num = 1 + num/1000 where num >= 6; -- Achilleas Mantzios ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] dbmirror - migration to 8.3 from 7.4
Hi, we have been running our own heavily modified/enhanced version of dbmirror, running on 7.4 for some years, and now it is the time to upgrade to 8.3. We have called our approach "Conditional row grained + FK dependency oriented lazy replication", that is, any FK dependencies of a row are tranfered only when needed, and each remote slave has a subset of the master DB. This is applied to a uucp network of postgresql installations that communicate over satelite dialup connections. That is why we cannot follow any officially supported replication platform. Now back to my issue, In the code, i do some SELECTs from the pg_catalog.pg_index, pg_catalog.pg_constraint c,pg_catalog.pg_class, pg_catalog.pg_attribute and i would like to have your opinion on wether some semantics have changed or added to the new pg_catalog tables. The way i find the primary key of a table is: SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID; i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption Should i include any of them (e.g. indisvalid) in the where clause above? The way i find the FK of a table is: SELECT c.confrelid,c.conkey,c.confkey,f.relname FROM pg_catalog.pg_constraint c,pg_catalog.pg_class f WHERE c.contype = 'f' AND c.confrelid = f.oid AND c.conrelid= TABLEOID; I noticed that some columns have been added to pg_constraint: conpfeqop,conppeqop,conffeqop Should i change something to the above query? Finally, the way i find the name of a column is: SELECT attname FROM pg_attribute WHERE attrelid=TABLEOID and attnum=ATTNUM; Also, i had to change any int2vector code, since now int2vector are implemented like varlenas, and also i had to add PG_DETOAST_DATUM on any array Datum. The code seems to work however i'd like your comments if i miss something. Thanks a lot. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dbmirror - migration to 8.3 from 7.4
Στις Wednesday 07 May 2008 14:49:31 ο/η Claus Guttesen έγραψε: > > we have been running our own heavily modified/enhanced version of dbmirror, > > running on 7.4 for some years, > > and now it is the time to upgrade to 8.3. > > > > The way i find the primary key of a table is: > >SELECT indkey FROM pg_index WHERE indisprimary='t' AND > > indrelid=TABLEOID; > > i noticed that some columns have been added to pg_index : indisvalid, > > indcheckxmin, indisready,indoption > > Should i include any of them (e.g. indisvalid) in the where clause above? > > Do you use oid? We had an issue where old code relied on oid and when > we tested 8.2 we had issues with oid being used but not available. The > fix was easy and only required a id-column of type serial. > No, we dont use OID as primary keys. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] order of rows in update
Hi, is there an (implicit) way to make a multirow update execute on some rows prior to other rows? It is needed in a case where a trigger is defined on the table as FOR EACH ROW, and it is mandatory that the trigger is run for some certain rows before it is run on the rest of the rows. Is there anything reliable to achieve this without making poor assumptions of the future versions, or should i just "SELECT ... ORDER BY ..." and then perform individual UPDATEs? Thanx -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order of rows in update
Στις Tuesday 02 September 2008 17:24:05 ο/η Richard Broersma έγραψε: > On Tue, Sep 2, 2008 at 2:58 AM, Achilleas Mantzios > <[EMAIL PROTECTED]> wrote: > > is there an (implicit) way to make a multirow update execute on some rows > > prior to other rows? > > It is needed in a case where a trigger is defined on the table as FOR EACH > > ROW, and it is mandatory > > that the trigger is run for some certain rows before it is run on the rest > > of the rows. > > > > Is there anything reliable to achieve this without making poor assumptions > > of the future > > versions, or should i just "SELECT ... ORDER BY ..." and then perform > > individual UPDATEs? > > The only way that I know how to do this is to create a named cursor of > the rows that you want to update, and then for each record call > > UPDATE ... FROM ... WHERE CURRENT OF cursorname; aha Thanks. > > > But why are you even having this problem to begin with? What you are > describing sounds like a database normalization problem. > I am using my version of DB mirror to do some kind of "Conditional row grained + FK dependency oriented lazy replication". (The logic behind it is the cost of comms, because the slaves are servers in vessels in all 7 seas, where communication is done via uucp connections over satellite dilaup, and the costs are really high, so the aim was to minimize the costs. Regarding high costs, It was so in 2003/2004 when we started designing/deploying this system and the prices are still high today.) I have divided my tables into the following categories: 1) never replicated 2) unconditionally replicated to all slaves 3) explicitly conditionally replicated to a *certain* slave and only to this slave, based on the value of one column (smth like "vslid", where vessels denote my slaves) 4) implicitly replicated to slaves, that is they are replicated to some slave *only* if they act as a parent (foreign) table in FK constraint of some child table which is either case 3) or case 4) So what i basically do is a depth first search of the Graph, denoted by the FK constraints. For simplicitly, at some point, in the code i have to find out if some parent table has to be part of the graph search. If that table belongs to case 3), i simply skip this "node", knowing it will be replicated because it is defined as such. The problem arises when a table has a FK to itself, then i have to make sure that some rows will be mirrored before other rows. I could rectify the code to deal correctly with cases like that, but it would add disproportinal complexity in comparison to the problem it solves. Thats why i want to force some rows to be updated before other rows, so that the dbmirror trigger will be called first. > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order of rows in update
Στις Wednesday 03 September 2008 15:28:04 ο/η Richard Broersma έγραψε: > On Tue, Sep 2, 2008 at 11:56 PM, Achilleas Mantzios > <[EMAIL PROTECTED]> wrote: > > I am using my version of DB mirror to do some kind of "Conditional row > > grained + FK dependency oriented lazy replication". > > (The logic behind it is the cost of comms, because the slaves are servers > > in vessels in all 7 seas, where communication is done > > via uucp connections over satellite dilaup, and the costs are really high, > > so the aim was to minimize the costs. > > Regarding high costs, It was so in 2003/2004 when we started > > designing/deploying this system and the prices are still high > > today.) > > If you are ever in LA, you should come to a LAPUG meeting. One of our > members also replicates over long distances. He is replicating from > California to China using a highly configured slony in combination > with other software. His software analyzes the possible routes he has > to find the routes with the best latency. He has configures a > revolving Master-Slave replication. Thanx for your help. I live in Greece, but it would be nice some time to visit America/LA :) > > Perhaps you guys to share notes? > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Στις Friday 17 October 2008 15:11:10 ο/η Gerardo Herzig έγραψε: > Richard Huxton wrote: > > Gerardo Herzig wrote: > >> But it does a doble count(*) that i must avoid. > >> I cant refer to the 'first' count like > >> select case when (select count(*) from test where id=$1 ) AS total > >> > 0 then total > >> else -1 > >> end; > > > > SELECT > > CASE WHEN total >0 THEN total ELSE -1 END AS new_total > > FROM ( > > SELECT count(*) AS total FROM test WHERE id=$1 > > ) AS raw_total > > > Pavel, Richard, you got it dudes! I have to say, that kinda 'reference > before assingment' of "total" doesnt look logical to me. > Then, both you and your boss need some SQL courses :) (no offense) > Thanks again!! > Gerardo > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] array variables
Στις Thursday 13 November 2008 11:13:30 ο/η Marcin Krawczyk έγραψε: > I know I can do one column like this : > > a := ARRAY(SELECT id FROM idx); > > but how about more than one ? > Because if I try this : > > a := ARRAY(SELECT id, p FROM idx); > > I get > ERROR: subquery must return only one column > SQL state: 42601 how about smth like: SELECT ARRAY[ARRAY(SELECT id FROM idx),ARRAY(SELECT p FROM idx)]; you will get 2 rows with "select count(*) from idx" columns each. you can access it like SELECT (ARRAY[ARRAY(SELECT id FROM idx),ARRAY(SELECT p FROM idx)])[i][j]; 1<=i<=2 1<=j<=select count(*) from idx > > regards > mk > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] array index access outside range
Hello list,
Let's suppose that we have an inteher array "parents"
select parents from machdefs where defid=888;
{1,2,3}
Currently accessing parents[0] returns a null, which is reasonable,
and i am thinking of relying on this assumption.
The question is, will it be safe to do that?
Is there any thoughts/needs/discussions of changing this behaviour in a future
version
thus breaking my program?
Thanx a lot
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Array iterator
Στις Tuesday 27 January 2009 14:40:29 ο/η Raj Mathur έγραψε:
> select regexp_split_to_array('string with tokens', '[^A-Za-z0-9]');
>
maybe
select regexp_split_to_table('string with tokens', '[^A-Za-z0-9]');
would help?
--
Achilleas Mantzios
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] alter table on a large db
Στις Thursday 19 March 2009 09:57:11 ο/η Zdravko Balorda έγραψε: > > Hi, > > I need to make some ALTER TABLEs. It takes about 30min to copy > this quite large databse, bat several ours to run a bunch of ALTER > TABLE statements. > Is there any way to make it faster? I wonder what could possibly alter > table be doing all this time. Which version of postgresql do you use? Also you might consider editing the SQL dump to alter the DDL statements prior to loading it in your db. batch editing sed, awk, perl, could help you on that... > > Regards, Zdravko. > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast bool/int
Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > Hi, > I need a casting operator from boolean to integer, > tu put in ALTER TABLE statment after USING. > How about CASE WHEN 't' THEN 1 ELSE 0 END > Any ideas? Thanks. > > Zdravko > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast bool/int
Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > Hi, > I need a casting operator from boolean to integer, > tu put in ALTER TABLE statment after USING. > Sorry in the above email i meant smth like CASE WHEN column='t' THEN 1 ELSE 0 END however SELECT 't'::boolean::int; int4 -- 1 (1 row) and SELECT 'f'::boolean::int; int4 -- 0 (1 row) work in 8.3 > Any ideas? Thanks. > > Zdravko > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres entering zombie state once a week in production evnvironment
Στις Tuesday 14 April 2009 09:25:54 ο/η Bryce Nesbitt έγραψε: > We have a medium scale installation of Postgres 8.3 that is freezing > about once a week. I'm looking for any hints on how to diagnose the > situation, as nothing is logged. > > The system is matched pair of Sunfire servers, running Debian Etch with > a 2.6.18-6-amd64 kernel, PostgreSQL 8.3.4, and DRBD 8.0.13. > > During a failed state, pg_stat_activity will show hundreds of statements > pending. query_start will show the statements arriving at a normal rate > (a few per second), but clearly they never complete. The bulk of these > statement are a simple select that starts each web session, a statement > that generally completes in tenths of milliseconds. Restarting postgres > restores normal operation, at the loss of all chance of figuring out > what was wrong. > > postgresql.conf has customized: > log_destination = 'syslog' > log_min_error_statement = error > log_min_duration_statement = 5000 > > Where can I go from here? I'm turning on log_checkpoints now, though > all I see is "postgres[14412]: [4-1] LOG: checkpoint starting: time" > with no actual time printed. > > Did you check for locks? SELECT * from pg_locks; SELECT l.locktype,c.relname,l.pid,l.mode from pg_locks l,pg_class c where l.relation=c.oid ; Were you able to issue other select commands during the problem? Were you able to start psql and do some simple command? Have you ruled out the possibility of the application being locked for other reasons? (limits, network failure,etc) -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Array: comparing first N elements?
Στις Tuesday 12 May 2009 11:05:28 ο/η David Garamond έγραψε: > I have a "materialized path" tree table like this (simplified): > > CREATE TABLE product ( > id SERIAL PRIMARY KEY, > parents INT[] NOT NULL, > name TEXT NOT NULL, > UNIQUE (parents, name) > ); > CREATE INDEX name ON product(name); > > Previously I use TEXT column for parents, but arrays look interesting and > convenient so I'm considering migrating to arrays. However, how do I rewrite > this using arrays? > Hi, I have used *exactly* the same scheme to model all PMS data in out fleet comprising of 1.5 million rows, for some 6 years now. You may find it in literature as genealogical tree representation. > SELECT * FROM product > WHERE parents LIKE '0001/0010/%'; Node 0001/0010 will have an id lets call it "parid". If you model your path in parents[] (we also use the same column name!) starting from the immediate father at parents[1] and going up to the root at parents[#parents] then what you actually want is to find all nodes for which parents[1]=parid you would want to look at the intarray contrib package for index suppor and many other goodies, also you might want to write fucntions first(parents), last(parents) and then have an index on those as well. This way searching for the direct children of a node is very fast. If on the other hand you want to find all children of parid, regardless of level, then you would do that with: intset(parid) ~ parents For the above to be efficient you should create an index on parents. Prefer method "gin" with opclass "gin__int_ops" Well thats how i implemented trees in postgresql anyway. > > In other words, testing against the first N elements in an array. > > Regards, > Dave > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
