Re: [SQL] selecting rows tagged with "a" but not "b"

[Andreas Gaab]

Hi Darrell,

SELECT DISTINCT articleID FROM tags WHERE tag = "a" 
EXCEPT
SELECT DISTINCT articleID FROM tags WHERE tag = "b";

Regards,

Andreas

-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von 8q5tmky...@sneakemail.com
Gesendet: Montag, 1. Februar 2010 14:32
An: pgsql-sql@postgresql.org
Betreff: [SQL] selecting rows tagged with "a" but not "b"

Hi,

I have a two tables:

article
articleID, name, content

tags
articleID, tag

I want to find all articles that are tagged with "a" but not "b"

how do I do this?

what I'd like to do is:


select articleID from tags where tag="a"
SUBTRACT
select articleID from tags where tab="b"


how do I do this in real SQL?

thanks

Darrell

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

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

[SQL] select points of polygons

[Andreas Gaab]

Hi there,


I have polygons with 5 points (left, center, right, top, bottom)

Now I would like to select an individual point out of the polygon. Are there 
any functions to provide this in an readable manner other than:

e.g. SELECT replace(split_part(p::text,',(',1),'((','(')::point as point FROM (
SELECT 
'((0.001329116037,0.007391900417),(0.001371765621,0.1608393682),(0.1502391498,0.00733744679),(0.001500387404,-0.1468751078),(-0.1472653422,0.007425591447))'::polygon
 as p) as poly


è (0.001329116037,0.007391900417)


Thanks
Andreas


___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

[SQL] ALTER TYPE my_enum AS ENUM ADD ('label10')

[Andreas Gaab]

Hi all,

I start working with enumerator types and I am wondering, if my enumerator can 
later be adjusted. E.g. if I would like to add another label.

Is it allowed just to add another entry to pg_enum? How is the enumerator then 
sorted (i.e. enum_last() )?

Is it possible to alter the labels of an enumerator later on? Can I edit 
pg_enum?

Thanks already,
Andreas Gaab

___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de<http://www.scanlab.de>

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

Re: [SQL] string functions and operators

[Andreas Gaab]

Why not using text-function substring:

SELECT split_part(123.456::text,'.',1)::integer;
SELECT split_part(123.456::text,'.',2)::integer;

Regards,
Andreas

-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von Petru Ghita
Gesendet: Dienstag, 23. März 2010 04:53
An: Neil Stlyz; pgsql-sql mailing list
Betreff: Re: [SQL] string functions and operators

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
For the record if you'd like to use regexp:

select substring('201.123' from $$[0-9]*$$);

and

select substring('201.1232' from $$\.([0-9]*)$$);


On 23/03/2010 4:42, Petru Ghita wrote:
>
> select 0.341*pow(10,length(0.341::text)-2);
>
> 2 is a constat that stands for the '0.' part of the string
> representing the decimal part of the number.
>
>
> Petru Ghita
>
> On 23/03/2010 3:16, Neil Stlyz wrote:
>> This is good, however, I need
> only the numbers to the right of the
>
>> decimal point
>
>
>
>> so if my number if 17.2
>
>
>
>> I would need one query that would return 17   (your function
> will
>
>> do that)
>
>
>
>> and the second query would return:   2
>
>
>
>> not 0.2
>
>
>
>> just 2
>
>
>
>> Does that make sense?
>
>
>
>
> --
>
>
>
>
>
>
>
>
*From:* Petru Ghita 
>> *To:* Neil Stlyz
> ; pgsql-sql mailing list
>
>>  *Sent:* Mon, March 22, 2010
> 8:08:30 PM
>
>> *Subject:* Re: [SQL] string functions and operators
>
>
>
>> For numeric data types use:
>
>
>
>> http://www.postgresql.org/docs/8.4/static/functions-math.html
>
>
>
>> You could then use|floor|(dp or numeric)|| for example:
>
>
>
>> postgres=# select floor(71.912); floor --- 71 (1 row)
>
>
>
>> postgres=# select 71.912-floor(71.912); ?column? --
>> 0.912
>
>
>
>
>
>> But as you might have negative numbers in there I guess you
> should
>
>> abs() the values like in:
>
>
>
>> postgres=# select abs(71.912)-floor(abs(71.912)); ?column?
>
>> -- 0.912
>
>
>
>
>
>> postgres=# select abs(-171.912)-floor(abs(-171.912)); ?column?
>
>> -- 0.912 (1 row)
>
>
>
>
>
>
>
>
>
>
>
>
>
>> On 23/03/2010 2:50, Petru Ghita wrote:
>
>>> That field of yours... what type is it? Is it TEXT? is it a
>
>>> numeric type? If it's TEXT, why don't you make it say...
>
>>> NUMERIC(/10/, /6///)?
>
>
>
>>>
>
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
On 23/03/2010 2:20, Neil Stlyz wrote:
 Hello,
>
>
>
>
>
>
>
 I have a dilema and I was hoping someone here may offer
>
 guidance
>
>
>
 or assistance. I bet this is a very simple question for
>
>> someone
>
>
>
 out there but I am having problems coming up with a
> solution.
>
>>> Here
>
>
>
 it is...
>
>
>
>
>
>
>
 suppose I have a field with the following values:
>
>
>
>
>
>
>
 77.1 77.2 134.1 134.2 134.3 5.1 5.2
>
>
>
>
>
>
>
 I need two seperate SELECT queries. One would return the
>
>>> following
>
>
>
 values (everything left of the decimal point)
>
>
>
>
>
>
>
 77 77 134 134 5 5
>
>
>
>
>
>
>
 The second query would return all of the values to the
> right
>
 of
>
>
>
 the decimal point:
>
>
>
>
>
>
>
 1 2 1 2 3 1 2
>
>
>
>
>
>
>
>
>
>
>
 Now, I have been using the following information
> (although
>
 very
>
>
>
 Greek) to try to solve this problem:
>
>
>
>
>
>
>
>
>
>
>
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
And I have been playing around with the syntax of the following:
>
>
>
>
>
>
 substring('112.5' from '%#"___.#"_' for '#')
>
>
>
>
>
>
>
 but the aforementioned is not quite working out... can
> someone
>
>
>
 please show me a string function that will produce the
> desired
>
>
>
 results?
>
>
>
>
>
>
>
 Thanks! ~n
>
>
>
>
>
>
>
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
 
iEYEARECAAYFAkuoOw4ACgkQt6IL6XzynQTnowCgyBRLh7iaJR4sC5Rs2zGgXxXh
vC8An1Yvruvz0IdFF86dN5bQUIESmv8m
=TUxh
-END PGP SIGNATURE-


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

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

[SQL] WAL-files restore and nextval('PK')

[Andreas Gaab]

Dear all,

after an WAL-restore of our Postgres DB, we observe seemingly wrong values of 
our sequences.

We have two postgres server (8.4) with pgpool in replication mode.

Recently we tested our restore procedure and played our WAL-files into the 
second server after an old file-system backup was restored.
Accidently, we aborted the starting server and had to restart it and therefore 
started WAL-replay again.

Now we observe, that the newly restored server has higher values in his 
sequences as the other server.

Could it be that during restart of the WAL-file restore SELECT nextval('PK') ; 
commands were executed again, whereas the data rows were already restored and 
thus leading to higher sequence numbers on the newly restored server?

Best regards for any comments!

Andreas


___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

Re: [SQL] is there a distinct function for comma lists ?

[Andreas Gaab]

Hi,

For the problem 1 perhaps something like

select distinct unnest(ARRAY[ 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ])

Regards,
Andreas

-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von Andreas
Gesendet: Dienstag, 7. September 2010 13:52
An: pgsql-sql@postgresql.org
Betreff: [SQL] is there a distinct function for comma lists ?

  Hi,
is there a distinct function for comma separated lists ?

I sometimes need to update tables where I got a set of IDs, like:

update mytable
set someattribute = 42
where mytable.id in
(  1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... )

So there are double entries in the list but in this case its just 
overhead but no problem.

But for calculated values this would not allways be desirable.

update mytable
set someattribute = someattribute + 1
where mytable.id in
(  1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... )

How could I get a distinct list? Those lists can have 2000-3000 IDs 
sometimes.

One solution was as follows but perhaps there is something more elegant?

update mytable
set someattribute = someattribute + 1
where mytable.id in
  ( select distinct id from mytable where id in (  1, 2, 3, 5, 7, 11, 3, 
6, 13, 13, 3, 11 ... ) )


And as bonus ... is there a way to find IDs that are in the list but not 
in the table without creating a temporary table and use a join?

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

-- 
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] Overlapping Ranges- Query Alternative

[Andreas Gaab]

Hi,

the following works:

Create temp table ranges (Groups int, ColumnA int);
Insert into ranges Values(2,45);
Insert into ranges Values(3,15);
Insert into ranges Values(4,25);
Insert into ranges Values(5,35);

Select Groups,
Case when ColumnA between 0 and 19 then 0
 when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)
 when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)
 when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)
 when ColumnA>=50 then generate_series(20,50,10) end MinRange
from ranges;


--or even only

Select Groups,
CASE WHEN ColumnA < 20 then 0 ELSE
generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange
from ranges;


Best, Andreas

Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alternative

I have the following problem:

Create temp table ranges (Groups int, ColumnA int);
Insert into ranges
Values(2,45);

Select Groups,
Case when ColumnA between 0 and 19 then 0
 when ColumnA >=20 then 20
 when ColumnA >=30 then 30
 when ColumnA>=40 then 40
 when ColumnA>=50 then 50 end MinRange
from ranges

Results:
Groups minrange
2;20

What I want Is : One column can fall into multiple ranges.  For example 45 >20, 
30, and 40 so I want the following results
2;20
2;30
2;40

I know I could do a union with each range but is there any way to bring back 
all ranges in one query?  I need to bring back the values in one column so 
having separate columns for each range is not an option.

Thank you in advance for any help

Pam Ozer

[SQL] aggregation of setof

[Andreas Gaab]

Hi all,

I would like to write a query, which aggregates the results of 
regexp_matches(). The problem is that regexp_matches returnes setof text[] as 
documented even if I discard the global flag 
(http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
 ). Thus resulting in an error when I try to aggregate the result:

"
SELECT array_accum(
regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
)
---
ERROR:  set-valued function called in context that cannot accept a set
** Fehler **
ERROR: set-valued function called in context that cannot accept a set
SQL Status:0A000
"

Can I convert a 'setof text[]' to a 'text[]'?

Alternatively I could use a sub-select, but I am curious if there are other 
solutions around.

Regards,
Andreas

___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

Re: [SQL] aggregation of setof

[Andreas Gaab]

Functions apparently cannot take setof arguments.

Postgres 8.4:

CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
  RETURNS anyarray AS
$BODY$
SELECT $1 LIMIT 1;
$BODY$
  LANGUAGE 'sql' STABLE;

-->
ERROR:  functions cannot accept set arguments



Von: Viktor Bojović [mailto:viktor.bojo...@gmail.com]
Gesendet: Samstag, 29. Januar 2011 09:28
An: Andreas Gaab
Betreff: Re: [SQL] aggregation of setof

i have never used that type but maybe you can try this;
-create function which returns text[], and takse setof text as argument (if 
possible)
-reach every text[] in set of text[] using array index
-return values using "return next" for each text in text[] which is in set of 
text[]

On Fri, Jan 28, 2011 at 12:42 PM, Andreas Gaab 
mailto:a.g...@scanlab.de>> wrote:
Hi all,

I would like to write a query, which aggregates the results of 
regexp_matches(). The problem is that regexp_matches returnes setof text[] as 
documented even if I discard the global flag 
(http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
 ). Thus resulting in an error when I try to aggregate the result:

“
SELECT array_accum(
regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
)
---
ERROR:  set-valued function called in context that cannot accept a set
** Fehler **
ERROR: set-valued function called in context that cannot accept a set
SQL Status:0A000
“

Can I convert a ‚setof text[]‘ to a ‚text[]‘?

Alternatively I could use a sub-select, but I am curious if there are other 
solutions around.

Regards,
Andreas

___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung • R & D

Siemensstr. 2a • 82178 Puchheim • Germany
Tel. +49 (89) 800 746-513 • Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de • www.scanlab.de<http://www.scanlab.de>

Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___




--
---
Viktor Bojović
---
Wherever I go, Murphy goes with me

Re: [SQL] aggregation of setof

[Andreas Gaab]

Hi,

Could not the function regexp_matches(text, text) be defined to only return a 
text[] not a setof text[]?

Because that is, what it actually does, or? For every input, it returns only 
one output row. The function regexp_matches(text,text,text) in contrast really 
can return multiple rows while only receiving one row.


Regards,
Andreas


-Ursprüngliche Nachricht-
Von: Pavel Stehule [mailto:pavel.steh...@gmail.com] 
Gesendet: Montag, 31. Januar 2011 10:24
An: Andreas Gaab
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] aggregation of setof

Hello

use a array constructor instead

SELECT ARRAY(SELECT ...)

Regards

Pavel Stehule

2011/1/31 Andreas Gaab :
> Functions apparently cannot take setof arguments.
>
>
>
> Postgres 8.4:
>
>
>
> CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
>
>   RETURNS anyarray AS
>
> $BODY$
>
> SELECT $1 LIMIT 1;
>
> $BODY$
>
>   LANGUAGE 'sql' STABLE;
>
>
>
> à
>
> ERROR:  functions cannot accept set arguments
>
>
>
>
>
>
>
> Von: Viktor Bojović [mailto:viktor.bojo...@gmail.com]
> Gesendet: Samstag, 29. Januar 2011 09:28
> An: Andreas Gaab
> Betreff: Re: [SQL] aggregation of setof
>
>
>
> i have never used that type but maybe you can try this;
>
> -create function which returns text[], and takse setof text as argument (if
> possible)
>
> -reach every text[] in set of text[] using array index
>
> -return values using "return next" for each text in text[] which is in set
> of text[]
>
>
>
> On Fri, Jan 28, 2011 at 12:42 PM, Andreas Gaab  wrote:
>
> Hi all,
>
>
>
> I would like to write a query, which aggregates the results of
> regexp_matches(). The problem is that regexp_matches returnes setof text[]
> as documented even if I discard the global flag
> (http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> ). Thus resulting in an error when I try to aggregate the result:
>
>
>
> “
>
> SELECT array_accum(
>
> regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
>
> )
>
> ---
>
> ERROR:  set-valued function called in context that cannot accept a set
>
> ** Fehler **
>
> ERROR: set-valued function called in context that cannot accept a set
>
> SQL Status:0A000
>
> “
>
>
>
> Can I convert a ‚setof text[]‘ to a ‚text[]‘?
>
>
>
> Alternatively I could use a sub-select, but I am curious if there are other
> solutions around.
>
>
>
> Regards,
>
> Andreas
>
>
>
> ___
>
>
>
> SCANLAB AG
>
> Dr. Andreas Simon Gaab
>
> Entwicklung • R & D
>
>
>
> Siemensstr. 2a • 82178 Puchheim • Germany
>
> Tel. +49 (89) 800 746-513 • Fax +49 (89) 800 746-199
>
> mailto:a.g...@scanlab.de • www.scanlab.de
>
>
>
> Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351
>
> Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
>
> Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
>
> ___
>
>
>
>
> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me

-- 
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] UNIQUE on everything except primary key

[Andreas Gaab]

Did you check check constraints?
http://www.postgresql.org/docs/8.4/static/ddl-constraints.html

Best
Andreas

-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von gvim
Gesendet: Donnerstag, 3. Februar 2011 16:44
An: pgsql sql
Betreff: [SQL] UNIQUE on everything except primary key

I have a table with 11 columns and want to eliminate duplication on INSERTs. 
What's the best method? The obvious solution would be to create a UNIQUE index 
on everything except the primary key - 'id' - but that's a 10-column index and 
doesn't sound very efficient. Am I missing something more obvious?

gvim

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

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

[SQL] replace_matches does not return {null}

[Andreas Gaab]

Hi,

I tried to order a text-column only by parts of the entries. Therefore I used 
regexp_matches(), but unfortunately I am loosing rows.

SELECT regexp_matches('abc','[0-9]+'),  regexp_matches('123','[0-9]+');

Does not return "{null}, {123}" but no result at all.

Is this behavior expected? How can I work around?

I am running postgres 8.4


Andreas


___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

[SQL] unnesting of array of different size explodes memory

[Andreas Gaab]

Hi,

I need to sum up the elements of two arrays. Most arrays were of the same size, 
thus I just unnested the arrays in one table and grouped the results in a loop 
for every row. When two arrays of different size (1200 and 1300) were 
processed, the memory usage exploded and the query was killed by the kernel.

As I now understand, the following query leads to 12 results, not just 4 (or 
3...):

SELECT unnest(ARRAY[1,2,3]), unnest(ARRAY[4,5,6,7]);

Why could postgres use as much memory till the kernel complained when unnesting 
1200 and 1300 elements resulting in 1.6e6 rows. Are there settings to prevent 
this such as "work_mem"?

Regards,
Andreas

___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

Besuchen Sie uns auf der / Meet us at
LASER World of PHOTONICS 2011
Munich, Germany
May 23 - 26, 2011
Hall C2, Booth 461

Re: [SQL] crosstab help

[Andreas Gaab]

Hi,

the return type of the crosstab must be defined correctly, according to the 
number of expected columns.

Try following (untested):

select * from crosstab(
'select item_number::text as row_name, to_char(week_of,''MM-DD-YY'')::date as 
bucket, planned_qoh::integer as buckvalue from 
xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of_1 date, week_of_2 date, week_of_3 date)

Regards,
Andreas



-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von John Fabiani
Gesendet: Freitag, 24. Februar 2012 09:11
An: pgsql-sql@postgresql.org
Betreff: [SQL] crosstab help

I have a simple table
item_number  week_of   planned_qoh
--  --   --
5   2012-02-05   30
5   2012-02-12   40
5   2012-02-19   50


where
item_number text
week_of date
planned_qoh integer

I have a function that returns the table as above:

chromasun._chromasun_totals(now()::date)

I want to see

5   2012-02-05   2012-02-122012-02-19
30  40 50

This is what I have tried (although, I have tired many others)

select * from crosstab('select item_number::text as row_name,
to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue from 
xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of date,  planned_qoh integer)

I get
ERROR:  return and sql tuple descriptions are incompatible

What am I doing wrong?

Johnf

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

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

[Andreas Gaab]

As far as I know you must define the numbers (and types) of columns and column 
headers individually for each query or define some custom function...

Andreas

-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von John Fabiani
Gesendet: Freitag, 24. Februar 2012 09:39
An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] crosstab help

That worked!  However, I need the actual date to be the column heading?   And 
of course the dates change depending on the date passed to the function:
xchromasun._chromasun_totals(now()::date)

So how do I get the actual dates as the column header?
johnf
On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> Hi,
> 
> the return type of the crosstab must be defined correctly, according 
> to the number of expected columns.
> 
> Try following (untested):
> 
> select * from crosstab(
> 'select item_number::text as row_name, 
> to_char(week_of,''MM-DD-YY'')::date
> as bucket, planned_qoh::integer as buckvalue from
> xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of_1 date, week_of_2 date, week_of_3 date)
> 
> Regards,
> Andreas
> 
> 
> 
> -Ursprüngliche Nachricht-
> Von: pgsql-sql-ow...@postgresql.org 
> [mailto:pgsql-sql-ow...@postgresql.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> An: pgsql-sql@postgresql.org
> Betreff: [SQL] crosstab help
> 
> I have a simple table
> item_number  week_of   planned_qoh
> --  --   --
> 5 2012-02-05   30
> 5 2012-02-12   40
> 5 2012-02-19   50
> 
> 
> where
> item_number text
> week_of date
> planned_qoh integer
> 
> I have a function that returns the table as above:
> 
> chromasun._chromasun_totals(now()::date)
> 
> I want to see
> 
> 5   2012-02-05   2012-02-122012-02-19
> 30  40 50
> 
> This is what I have tried (although, I have tired many others)
> 
> select * from crosstab('select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as 
> buckvalue from xchromasun._chromasun_totals(now()::date)') as 
> ct(item_number text, week_of date,  planned_qoh integer)
> 
> I get
> ERROR:  return and sql tuple descriptions are incompatible
> 
> What am I doing wrong?
> 
> Johnf
> 
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make 
> changes to your subscription: 
> http://www.postgresql.org/mailpref/pgsql-sql

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

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

[SQL] Running mean filtering using Window Functions?

[Andreas Gaab]

Hi,

can I realize a running mean filter using window functions?

What I can think of is:

SELECT avg(random) OVER (PARTITION BY floor(time_s / 60) ORDER BY floor(time_s 
/ 60))
FROM (
SELECT generate_series(0,600,20) time_s, random()
) as data;

which averages all measurements in one minute, but not every line with its 
following two lines.

Any suggestions?

Best regards
Andreas

___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

Re: [SQL] Advice with an insert query

[Andreas Gaab]

INSERT INTO table1 (fld1, fld2, fl3)
VALUES (SELECT value1, fldx, fldy FROM table2);

should work,

Andreas


Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von JORGE MALDONADO
Gesendet: Freitag, 7. Juni 2013 15:59
An: pgsql-sql@postgresql.org
Betreff: [SQL] Advice with an insert query

I need to insert records into a table where one value is fixed and 2 values 
come from a SELECT query, something like the following example:

INSERT INTO table1 fld1, fld2, fl3
VALUES value1, (SELECT fldx, fldy FROM table2)

Is this valid?

Respectfully,
Jorge Maldonado