Re: [SQL] Advice with an insert query

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]

[SQL] Running mean filtering using Window Functions?

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 n

Re: [SQL] crosstab help

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

Re: [SQL] crosstab help

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

[SQL] unnesting of array of different size explodes memory

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

[SQL] replace_matches does not return {null}

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

Re: [SQL] UNIQUE on everything except primary key

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

Re: [SQL] aggregation of setof

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

Re: [SQL] aggregation of setof

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

[SQL] aggregation of setof

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 re

Re: [SQL] Overlapping Ranges- Query Alternative

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 Col

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

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

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

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

Re: [SQL] string functions and operators

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 Gese

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

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

[SQL] select points of polygons

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

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

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 G