[SQL] locks problem
Hi list, I've got a locking problem which prevents me from doing some alters on my tables. When I looked into pg_locks I saw a lot of entries (around 1000) with pid being NULL and almost all of them are AccessShare. Can anyone tell me what might those be and how do I get rid of them ?
[SQL] Using regexp_matches in the WHERE clause
Hi, I stumbled over this question on Stackoverflow http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match And my initial reaction was, that this should be possible using regexp_matches. So I tried: SELECT * FROM some_table WHERE regexp_matches(somecol, 'foobar') is not null; However that resulted in: ERROR: argument of WHERE must not return a set Hmm, even though an array is not a set I can partly see what the problem is (although given the really cool array implementation in PostgreSQL I was a bit surprised). So I though, if I convert this to an integer, it should work: SELECT * FROM some_table WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0 but that still results in the same error. But array_length() clearly returns an integer, so why does it still throw this error? I'm using 9.2.1 Regards Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] locks problem
Ok I figured it out. I had a prepared transaction holding the locks. The pg_prepared_xacts was helpful. 27-11-2012 10:27, "Marcin Krawczyk" napisał(a): > Hi list, > > I've got a locking problem which prevents me from doing some alters on my > tables. When I looked into pg_locks I saw a lot of entries (around 1000) > with pid being NULL and almost all of them are AccessShare. Can anyone tell > me what might those be and how do I get rid of them ? >
Re: [SQL] Using regexp_matches in the WHERE clause
Sounds to me like this: http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html > To: [email protected] > From: [email protected] > Subject: [SQL] Using regexp_matches in the WHERE clause > Date: Mon, 26 Nov 2012 13:13:06 +0100 > > Hi, > > I stumbled over this question on Stackoverflow > > http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match > > And my initial reaction was, that this should be possible using > regexp_matches. > > So I tried: > > SELECT * > FROM some_table > WHERE regexp_matches(somecol, 'foobar') is not null; > > However that resulted in: ERROR: argument of WHERE must not return a set > > Hmm, even though an array is not a set I can partly see what the problem is > (although given the really cool array implementation in PostgreSQL I was a > bit surprised). > > > So I though, if I convert this to an integer, it should work: > > SELECT * > FROM some_table > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0 > > but that still results in the same error. > > But array_length() clearly returns an integer, so why does it still throw > this error? > > > I'm using 9.2.1 > > Regards > Thomas > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
> So I tried: > > SELECT * > FROM some_table > WHERE regexp_matches(somecol, 'foobar') is not null; > > However that resulted in: ERROR: argument of WHERE must not return a set > > Hmm, even though an array is not a set I can partly see what the problem is > (although given the really cool array implementation in PostgreSQL I was a bit surprised). > > > So I though, if I convert this to an integer, it should work: > > SELECT * > FROM some_table > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0 > > but that still results in the same error. > > But array_length() clearly returns an integer, so why does it still throw this error? > > > I'm using 9.2.1 > Sounds to me like this: http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html Thanks, but my question is not related to the underlying problem. My question is: why I cannot use regexp_matches() in the WHERE clause, even when the result is clearly an integer value? Regards Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
Hello 2012/11/27 Thomas Kellerer : >> > So I tried: >> > >> > SELECT * >> > FROM some_table >> > WHERE regexp_matches(somecol, 'foobar') is not null; >> > >> > However that resulted in: ERROR: argument of WHERE must not return a >> set >> > >> > Hmm, even though an array is not a set I can partly see what the >> problem is >> > (although given the really cool array implementation in PostgreSQL I >> was a bit surprised). >> > >> > >> > So I though, if I convert this to an integer, it should work: >> > >> > SELECT * >> > FROM some_table >> > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0 >> > >> > but that still results in the same error. >> > >> > But array_length() clearly returns an integer, so why does it still >> throw this error? >> > >> > >> > I'm using 9.2.1 >> > > > >> Sounds to me like this: >> >> >> http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html >> > > Thanks, but my question is not related to the underlying problem. > > My question is: why I cannot use regexp_matches() in the WHERE clause, even > when the result is clearly an integer value? > use a ~ operator instead postgres=# select * from o where a ~ 'e'; a pavel zdenek (2 rows) postgres=# select * from o where a ~ 'k$'; a zdenek (1 row) you can use regexp_matches, but it is not effective probably postgres=# select * from o where exists (select * from regexp_matches(o.a,'ne')); a zdenek (1 row) Regards Pavel Stehule > > Regards > Thomas > > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
Pavel Stehule, 27.11.2012 13:26: My question is: why I cannot use regexp_matches() in the WHERE clause, even when the result is clearly an integer value? use a ~ operator instead So that means, regexp_matches cannot be used as an expression in the WHERE clause? Regards Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
2012/11/27 Thomas Kellerer :
> Pavel Stehule, 27.11.2012 13:26:
>
>>> My question is: why I cannot use regexp_matches() in the WHERE clause,
>>> even
>>> when the result is clearly an integer value?
>>>
>>
>> use a ~ operator instead
>>
>
> So that means, regexp_matches cannot be used as an expression in the WHERE
should not be used - it is designed to return matched values, no for
returning true or false,
you can do some obscure
postgres=# select * from o where array(select
(regexp_matches(a,'ne'))[1]) <> '{}'::text[];
a
zdenek
(1 row)
but it is not recommended.
Regards
Pavel
> clause?
>
>
> Regards
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] output query on many to many relationship into several columns
Dear list, assuming I have the following n:n relationship: t1: t2: intermediary table: t3 How can I create an output like this: -- View this message in context: http://postgresql.1045698.n5.nabble.com/output-query-on-many-to-many-relationship-into-several-columns-tp5733692.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select on many-to-many relationship
Dear list, assuming I have the following n:n relationship: t1: id_project 1 2 t2: id_product 1 2 intermediary table: t3 id_project|id_product 1|1 1|2 2|1 How can I create an output like this: id_project|id_product1|id_product2 1|1|2 2|1|NULL -- View this message in context: http://postgresql.1045698.n5.nabble.com/select-on-many-to-many-relationship-tp5733696.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using regexp_matches in the WHERE clause
On Nov 26, 2012, at 7:13, Thomas Kellerer wrote: > > So I tried: > > SELECT * > FROM some_table > WHERE regexp_matches(somecol, 'foobar') is not null; > > However that resulted in: ERROR: argument of WHERE must not return a set > > Hmm, even though an array is not a set I can partly see what the problem is > (although given the really cool array implementation in PostgreSQL I was a > bit surprised). > regex_matches returns a set because you can supply the "g" option to capture all matches and each separate match returns its own record. Even though only one record is ever returned without the "g" option the function itself is the same and still is defined to return a set. David J. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] select on many-to-many relationship
2012/11/27 ssylla : > assuming I have the following n:n relationship: > > intermediary table: > t3 > id_project|id_product > 1|1 > 1|2 > 2|1 > > How can I create an output like this: > id_project|id_product1|id_product2 > 1|1|2 > 2|1|NULL I'd said the sample is too simplified — not clear which id_product should be picked if there're more then 2 exists. I assumed the ones with smallest IDs. -- this is just a sample source generator WITH t3(id_project, id_product) AS (VALUES (1,1),(1,2),(2,1)) -- this is the query SELECT l.id_project, min(l.id_product) id_product1, min(r.id_product) id_product2 FROM t3 l LEFT JOIN t3 r ON l.id_project=r.id_project AND l.id_product < r.id_product GROUP BY l.id_project; -- Victor Y. Yegorov -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] select on many-to-many relationship
On Tue, Nov 27, 2012 at 2:13 AM, ssylla wrote: > id_project|id_product > 1|1 > 1|2 > 2|1 > > How can I create an output like this: > id_project|id_product1|id_product2 > 1|1|2 > 2|1|NULL You can use the crostab() function from the tablefunc module (http://www.postgresql.org/docs/9.2/static/tablefunc.html). It does exactly what you need. > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/select-on-many-to-many-relationship-tp5733696.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
