Re: [SQL]

esql.org/docs/9.2/static/ddl-schemas.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) K

[SQL] monthly statistics

Hi, I need to show a moving statistic of states of objects for every month since beginning of 2013. There are tables like objects ( id integer, name text ); state ( id integer, state text ); 10=A, 20=B ... 60=F history ( object_id integer, state_id, ts timestamp ); Every event that

Re: [SQL] Select clause in JOIN statement

tomer.status = b.status_id WHERE b.status_id > 0 But you can JOIN on SELECTs selecting arbitrary stuff.   -- Andreas Joseph Krogh       mob: +47 909 56 963 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no Public key: http://home.officenet.no/~andreak/public_key.asc  

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

Re: [SQL] Primary DB stuck becuase of unavailable standby (synchronized streaming) - please help

arameter that I can use, to enable recovering from hanging on > until someone of us re-establish connection to secondary DB? Use asynchronous mode. There is no way for 'only a little bit synchronized mode'. We are not in the mysql-world ... Andreas -- Really, I'm

Re: [SQL] How to split an array-column?

rray_col) from table Venky Kandaswamy Principal Engineer, Adchemy Inc. 925-200-7124 From: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org] on behalf of Andreas [maps...@gmx.net] Sent: Monday, March 18, 2013 12

[SQL] How to split an array-column?

Hi, I've got a table to import from csv that has an array-column like: import ( id, array_col, ... ) Those arrays look like ( 42, ";4941;4931;4932", ... ) They can have 0 or any number of elements separated by ; So I'd need a result like this: 42, 4941 42, 4931 42, 4932 How would I get this?

[SQL] I need to fill up a sparse table in an view

Hi, I need to fill up a sparse table in an view. The table holds some numbers relating months and objects. The month is an integer in the format MM. To make it more convenient to manage this table I decidet to let a value be good till the next entry. E.g. if there is an entry in january and

Re: [SQL] How to reject overlapping timespans?

Am 17.02.2013 19:20, schrieb Andreas Kretschmer: Andreas hat am 17. Februar 2013 um 18:02 geschrieben: I need to store data that has a valid timespan with start and enddate. objects ( id, name, ... ) object_data ( object_id referencs objects(id), startdate, enddate, ... ) nothing special

Re: [SQL] How to reject overlapping timespans?

Andreas hat am 17. Februar 2013 um 18:02 geschrieben: > Hi, > > I need to store data that has a valid timespan with start and enddate. > > objects ( id, name, ... ) > object_data ( object_id referencs objects(id), startdate, enddate, ... ) > > nothing special, yet > &

[SQL] How to reject overlapping timespans?

Hi, I need to store data that has a valid timespan with start and enddate. objects ( id, name, ... ) object_data ( object_id referencs objects(id), startdate, enddate, ... ) nothing special, yet How can I have PG reject a data record where the new start- or enddate lies between the start- or

[SQL] How to access multicolumn function results?

Hi I've got functions that return a TABLE. If I call it with constant parameters like: SELECT * FROM some_fct( 42 ); I get a table with column names as the result as intended. When I rather call it with the parameter coming from another table I get a set of records where I don't know how to a

Re: [SQL] Re: [SQL] need some magic with generate_series()

xpression with the series from Filip and left join to the table you need to report on. Sent from my smartphone - Reply message - From: "Andreas" To: "Filip Rembiałkowski" Cc: "jan zimmek" , Subject: [SQL] need some magic with generate_series() Date: Tue

Re: [SQL] need some magic with generate_series()

der by 1, 2; Am 23.01.2013 01:08, schrieb Alexander Gataric: I would create a common table expression with the series from Filip and left join to the table you need to report on. - Reply message - From: "Andreas" To: "Filip Rembiałkowski" Cc: "jan zimmek&qu

Re: [SQL] need some magic with generate_series()

0101'::date, '1 month'::interval) m; On Tue, Jan 22, 2013 at 3:49 PM, jan zimmek wrote: hi andreas, this might give you an idea how to generate series of dates (or other datatypes): select g, (current_date + (g||' month')::interval)::date from generate_series(1,12) g;

[SQL] need some magic with generate_series()

Hi I need a series of month numbers like 201212, 201301 MM to join other sources against it. I've got a table that describes projects: projects ( id INT, project TEXT, startdate DATE ) and some others that log events events( project_id INT, createdate DATE, ...) to show some statistics I

Re: [SQL] Help with a select statement design

1 as id, price1 as price union all select id2, price2 union all select id3, ... you got it? Andreas -- 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 basic question

geschrieben: > Hello Andreas, > > apologize for the misunderstanding. Hope to clarify now. For each ID I > want a min and max for each SIDE. I have about 160K records like this: > > label | id | distance | side > ---++---+

Fwd: Re: [SQL] sql basic question

sorry, only a private replay and not to the list -- Ursprüngliche Nachricht -- Von: Andreas Kretschmer An: Antonio Parrotta Datum: 28. Dezember 2012 um 15:19 Betreff: Re: [SQL] sql basic question Hi, your question was: "What I want to achieve is a result table with min an

[SQL] rolling statistic probaply a window function?

I have a rather big log table that collects events for objects and the change of their states. Say an object can have state 0-9. Every now and then an event happens that gets logged as: ( event TIMESTAMP, object_id INTEGER, state_id INTEGER ) Now I need to get a history of the last 12 weeks tha

Re: [SQL] checking the gaps in intervals

Andreas Kretschmer wrote: > Anton Gavazuk wrote: > > > Hi dear community, > > > > Have probably quite simple task but cannot find the solution, > > > > Imagine the table A with 2 columns start and end, data type is date > > > > start

Re: [SQL] checking the gaps in intervals

nking about a solution with DATERANGE (PostgreSQL 9.2)... Are start and end including or excluding? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile pe

[SQL] Need help with a special JOIN

Hi, asume I've got 2 tables objects ( id int, name text ) attributes ( object_id int, value int ) attributes has a default entry with object_id = 0 and some other where another value should be used. e.g. objects ( 1, 'A' ), ( 2, 'B' ), ( 3, 'C' ) attributes ( 0, 42

Re: [SQL] Reuse temporary calculation results in an SQL update query

lation_2 as calc2 > from foo > ) > update foo >set startTime = my_calc.calc1, >stopTime = my_calc.calc2, >duration = my_calc.calc2 - calc1 > where foo.pk = my_calc.pk; > > http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING Yeah, with a WITH - CTE, cool ;-) Andreas -- 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] Reuse temporary calculation results in an SQL update query

ion 1' > WHERE foo; The Duration - field is superfluous ... As far as i know there is no way to re-use the result. Regards, Andreas -- 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] matching a timestamp field

-09-22'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Time: 0,156 ms test=!# rollback; ROLLBACK Time: 0,079 ms test=# select now()::date = '2012-09-22'::date; ?column? -- t

Re: [SQL] [GENERAL] Indexing question

e f2>, instead there should be two entries in the index and ). > > we have a few use cases for the above, perhaps we need to alter the > schema somehow to accommodate the above, 2 separate indexes? One on f1 and one on f2? Andreas -- Really, I'm not out to destroy Microsoft

Re: [SQL] can this be done with a check expression?

27;); INSERT 0 1 Time: 0,313 ms test=*# insert into foo values ('name1', '[5,15)'); ERROR: conflicting key value violates exclusion constraint "foo_name_id_range_excl" DETAIL: Key (name, id_range)=(name1, [5,15)) conflicts with existing key (name, id_range)=(name1, [1,9)).

[SQL] join against a function-result fails

Hi, I have a table with user ids and names. Another table describes some rights of those users and still another one describes who inherits rights from who. A function all_rights ( user_id ) calculates all rights of a user recursively and gives back a table with all userright_ids this user di

Re: [SQL] Prevent double entries ... no simple unique index

Am 12.07.2012 07:14, schrieb Andreas Kretschmer: Marc Mamin wrote: A partial index would do the same, but requires less space: create unique index on log(state) WHERE state IN (0,1); OK, nice :) What if I have those states in a 3rd table? So I can see a state-history of when a state

Re: [SQL] Prevent double entries ... no simple unique index

27; - value - but no more. > > Hi, > > A partial index would do the same, but requires less space: > > create unique index on log(state) WHERE state IN (0,1); Right! ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional si

Re: [SQL] Prevent double entries ... no simple unique index

Andreas Kretschmer wrote: > Andreas wrote: > > > Hi, > > > > I've got a log-table that records events regarding other objects. > > Those events have a state that shows the progress of further work on > > this event. > > They can be open, accept

Re: [SQL] Prevent double entries ... no simple unique index

Andreas wrote: > Hi, > > I've got a log-table that records events regarding other objects. > Those events have a state that shows the progress of further work on > this event. > They can be open, accepted or rejected. > > I don't want to be able to insert add

[SQL] Prevent double entries ... no simple unique index

Hi, I've got a log-table that records events regarding other objects. Those events have a state that shows the progress of further work on this event. They can be open, accepted or rejected. I don't want to be able to insert addition events regarding an object X as long there is an open or a

[SQL] How to solve the old bool attributes vs pivoting issue?

Hi I do keep a table of objects ... let's say companies. I need to collect flags that express yes / no / don't know. TRUE / FALSE / NULL would do. Solution 1: I have a boolean column for every flag within the companies-table. Whenever I need an additional flag I'll add another column. This

Re: [SQL] How to limit access only to certain records?

Andreas wrote: > Hi, > > is there a way to limit access for some users only to certain records? > > e.g. there is a customer table and there are account-managers. > Could I limit account-manager #1 so that he only can access customers > only acording to a flag? Yea,

[SQL] How to limit access only to certain records?

, account_manager_id ). Could I let the database control that account-manager #1 can only see customers who are assigned to him in the cu_am-relation? For now I do this in the front-end but this is easily circumvented for anyone who has a clue and uses some other client like psql. Regards Andreas -- Sent

[SQL] Is there a similarity-function that minds national charsets?

Hi, Is there a similarity-function that minds national charsets? Over here we've got some special cases that screw up the results on similarity(). Our characters: ä, ö, ü, ß could as well be written as: ae, oe, ue, ss e.g. select similarity ( 'Müller', 'Mueller' ) results to: 0.363636 In

Re: [SQL] master/detail

te | price | lead | ?column? +-++---+--+-- 4 | 1 | 2012-05-15 |22 | 12 | 10 3 | 1 | 2012-05-10 |12 | 15 | -3 2 | 1 | 2012-05-05 |15 | 10 |5 1 | 1 | 2012-05-01 |10 | | (4 rows) Rega

[SQL] Select every first/last record of a partition?

Hi, suppose a table that has records with some ID and a timestamp. id,ts 3,2012/01/03 5,2012/01/05 7,2012/01/07 3,2012/02/03 3,2012/01/05 5,2012/03/01 7,2012/04/04 to fetch every last row of those IDs I do: select id, ts from ( select id, ts, row_number(

Re: [SQL] Need help in grouping records

Am 20.05.2012 05:04, schrieb Jasen Betts: On 2012-05-19, Andreas wrote: Hi, I'm trying to fight against double entries in tables. I got as far as I can find similar records with trigram string matching. If I do this with a table compared to itself I get something like this: id_a, id_b 3

[SQL] Need help in grouping records

Hi, I'm trying to fight against double entries in tables. I got as far as I can find similar records with trigram string matching. If I do this with a table compared to itself I get something like this: id_a, id_b 3, 5 3, 7 5, 3 5, 7 7, 3 7, 5 11, 13 13, 11 so the records with t

Re: [SQL] How change col name during query to use it in where clause

Marcel Ruff hat am 4. Mai 2012 um 12:25 geschrieben: > Hi, > > is an alias name not usable in the where clause? Exactly. Andreas -- 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?

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

Re: [SQL] generic crosstab ?

Am 25.04.2012 00:04, schrieb Joe Conway: On 04/24/2012 02:42 PM, David Johnston wrote: You must specify the output record structure: SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name colN_type]* ) Whether this relates to the “materialization node” message you are receiving I ha

Re: [SQL] generic crosstab ?

Am 24.04.2012 22:08, schrieb Samuel Gendler: On Tue, Apr 24, 2012 at 1:01 PM, Andreas <mailto:maps...@gmx.net>> wrote: Hi, is there a generic solution to dump the result of a query as a crosstab, when I can't know how many columns I will need? E.g. I get somet

[SQL] generic crosstab ?

Hi, is there a generic solution to dump the result of a query as a crosstab, when I can't know how many columns I will need? E.g. I get something like this: id, x 1, a 1, b 1, c 2, l 2, m and I'd like to see it as: id, x1, x2, x3, . xn 1, a, b, c,null, null 2,

[SQL] How to group by similarity?

Hi, I'm trying to get an idea about pg_trgrm. I created a GIST index on a text column in a table. Now I can filter the table with similarity(). How would I group the table so that it shows groups that have similarity () > x ? Lets say the table looks like this: id, txt 1, aa1 2, bb1 3,

Re: [SQL] Simple way to get missing number

Raj Mathur (राज माथुर) wrote: > > Nice one, but curious about how would this perform if the numbers in > question extended into 7 figures or more? TIAS (Try It And See) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintention

Re: [SQL] Simple way to get missing number

code ] | test=# select * from emi_lu ; | i | --- | 1 | 2 | 3 | 5 | 6 | 8 | 9 | (7 rows) | | Time: 0,246 ms | test=*# select * from generate_Series(1,10) s left join emi_lu on | (s=emi_lu.i) where i is null; | s | i | +--- | 4 | | 7 | | 10 | | (3 rows) ` this? Andreas -- R

Re: [SQL] getting the OS user name

John Fabiani wrote: > Hi, > In my app it is possible to login as one name and use a different name to > login to postgres. > > Is it possible to get the actual OS login name using plsql. Since you can login to an remote database server: no. Andreas -- Really, I'

[SQL] syntax of joins

hi, is there a disadvantage to write a join as select * froma, b where a.id = b.a_id; over select * froma join b on a.id = b.a_id; -- 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] how to concatenate in PostgreSQL

Rehan Saleem wrote: > hi , > how can we concatinate these lines and execute sql command > >set sql = 'select user,username, firstname ' > set sql += ' lastname, cardno from table1 where userid=' + 5 sql = sql || ' bla fasel'; || is

Re: [SQL] crosstab help

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

Re: [SQL] crosstab help

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

[SQL] How to split up phone numbers?

Hi, is there a way to split up phone numbers? I know that's a tricky topic and it depends on the national phone number format. I'm especially interested in a solution for Germany, Swizerland and Austria. I've got everything in a phone number column that makes hardly sense like: +49432156780 004

Re: [SQL] need help with import

Am 16.02.2012 02:13, schrieb David Johnston: -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Sent: Wednesday, February 15, 2012 8:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] need help with import Hi I get CSV

[SQL] need help with import

Hi I get CSV files to import. Th structure is like this. main part, sub part Could be like this A, a1 A, a2 A, a3 B, b1 B, b2 The database has a table for main_part and one for sub_part. The relation needs to be n:m so there is a relation table that holds ( main_id, sub_id ). The 2 primary key

Re: [SQL] foreign key is it a real key

ain does not appear to > use it as an index (instead of a seq scan it uses a hash join). But the > table > all ready has a FK key contraint on the column. Yes, for large tables you have to create a own index on your fk-column. Andreas -- Really, I'm not out to

[SQL] Is there a way to remove every character but numbers?

Hi, I need to clean up a text column so that it just contains numbers or the "+" sign if it is the first character of the string. Well, it is needed to normalise up a phone number column. So it'd be great if the "+" could be transformed in "00" in the same step. -- Sent via pgsql-sql mailing

Re: [SQL] sql query problem

NULL, > `user_email` varchar(255) NOT NULL, > `user_gender` varchar(255) NOT NULL, > `refrence` varchar(255) NOT NULL, > `join_date` varchar(255) NOT NULL, > `status` tinyint(1) NOT NULL, > `banned` tinyint(1) NOT NULL, > PRIMARY KEY (`user_id`) > ) That

Re: [SQL] ignore unique violation OR check row exists

t.i is null; INSERT 0 4 Time: 0,464 ms test=*# select * from dest; i --- 1 2 3 4 5 6 7 8 9 (9 rows) I think, that's the fastest way. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Lin

Re: [SQL] i want small information regarding postgres

vinodh chowdary wrote: > Hi sir, > > i am using postgres as database for my project. > i want to convert postgres data into excel sheet. > is there any way to do it? > please reply me. This should be possible with ODBC. (But i'm not familiar with it, don'

Re: [SQL] Problem with tables and columns names

testTable" you have to use this name everytime. Within psql you have to quote, for instance select * from "testTable" will work, select from testTable doesn't. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [SQL] Column "..." does not exist (view + union)

o something like: select name from (select name, ctype from ... union select name, ctype from ...) foo order by ctype Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I w

Re: [SQL] conditional FROM

rdid > and card.card2node = node.nodeid > and node.name like 'nodename%' > ; > > Though I just can't order the rows anymore by inet(arp.ip) anymore... > Any hints on my ordering isn't anylonger possible? select * from (insert the query above

Re: [SQL] conditional FROM

e as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... I think, you can't do that, you have to build your query and execute that string. You should use a function to do that. Andreas -- Really, I'm not out to destroy

Re: [SQL] Question on imports with foreign keys

Am 08.12.2011 09:39, schrieb Emre Hasegeli: On Thu, 08 Dec 2011 08:48:51 +0200, Andreas wrote: How is the easiest way to to find the customer.id of the new customers so I can insert the projectinfos? It is easy to select rows not related with another table. One of the following queries

[SQL] Question on imports with foreign keys

Hi, suppose you need to import a csv with standard ciolums like name, adress, phone, ... and some additional text columns that need to be split off into referenced tables. Those lookup-tables will only be needed for a project with limited life time so I create a schema that might be called "

Re: [SQL] Change in 9.1?

r to give you an answer if you provided a query which worked before which now, in 9.1, gives you trouble. -- Andreas Joseph Krogh - mob: +47 909 56 963 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no Public key: http://home.officenet.no/~andreak/public_key.asc  

Re: [SQL] Number timestamped rows

vent_c |2 > 2 |0:4 |event_b |3 > 3 |0:1 |event_a |1 > untested: select user_id, timestamp, event, row_number() over (partition by user_id order by timestamp) as "order" from your_table > How would I do this with an UPDATE statement (e.g.) in pgsql? I think, y

Re: [SQL] Problem with DROP ROLE

You can't do that in THAT way, but you can use dynamic SQL:   test=# select * from drop_role ;    t  foobar (1 row) test=*# do $$ declare r text; begin for r in select t from drop_role loop execute 'drop role ' || quote_ident(r) || ';'; end loop; end; $$language plpgsql;   DO      "Bric

Re: [SQL] Group by on Date

00:00:00 | 2011-10-31 00:00:00 | 42 2011-10-31 00:00:00 | 2011-11-03 00:00:00 | 51 2011-11-03 00:00:00 | 2011-11-06 00:00:00 | 39 (7 rows) it fails for more than 1 year, but i hope, you can modify my sql for your own. Andreas -- Really, I'm not out to destroy Microsoft. That will jus

Re: [SQL] postgres sql help

-- .1.3.1 .1.3.2.5. .1.4.1.7.12 .1.4.11.14.7. .1.4.3.109.1. .1.4.8.66. (6 rows) Time: 0,262 ms test=*# select * from o order by split_part(t,'.',2)::int, split_part(t,'.',3)::int, split_part(t,'.',4)::int; t --- .1.3.1 .1.3.2.5. .1.4.1

[SQL] how to calculate differences of timestamps?

How could I calculate differences of timestamps in a log-table? Table log ( user_id integer, login boolean, ts timestamp ) So login = true would be a login-event and login = false a logout. Is there a way to find the matching login/logout to calculate the difference? Or is there a better tabl

[SQL] Better way to check more than 1 value NOT IN (...)

Hi, I've got to check if 2 values are not in a set that is dynamically calculated by a function. The query looks like select some_id, from . where 10 is not in ( select x from my_function (some_id)) and 20 is not in ( select x from my_function (some_id)) Is there a efficiency

[SQL] Howto build a funtion that selects an id or inserts a value

Hi, I'd like to have a function that looks up an id of an item. In case the item doesn't exist in the table yet it should be inserted and the new id should be returned. From the PG docu, I took the merge_db sample and modified it a bit. This works but I'm wondering if INSERT part could be tune

Re: [SQL] Use select and update together

Am 13.09.2011 07:50, schrieb pasman pasmański: In 8.4 this syntax is not implemented. select * from ( update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning * ) as x wouldn't work even in PG 9.1. So what data structure is coming out of an "update ... returning *" statement? It obvio

[SQL] insert or update within transaction

Hi, http://www.postgresql.org/docs/current/static/sql-update.html has an example where an either an insert or update is done according if a key already exists. The example is about wines. I did it with numbers. drop table if exists tbl; create table tbl ( key int primary key, val int ); insert

Re: [SQL] Clever way to check overlapping time intervals ?

Andreas wrote: > Hi, > is there a clever way to check overlapping time intervals ? > An option named n should be taken from date y to y. > The same name is ok for another interval. > > e.g. table : mytab ( d1 date, d2 date, n text, v text ) > > There should be a constrai

[SQL] Clever way to check overlapping time intervals ?

Hi, is there a clever way to check overlapping time intervals ? An option named n should be taken from date y to y. The same name is ok for another interval. e.g. table : mytab ( d1 date, d2 date, n text, v text ) There should be a constraint to provide no row can have a d1 or d2 within the in

[SQL] How can I inherit constraints?

Hi, how would I let a table inherit the constraints of its parent(s) ? It'd be rather cool not having to repeat every foreign key a table inherited from it's parents. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/

[SQL] need magic to shuffle some numbers

Hi, there is a table that has among others a integer primary key "id" and another integer column "prio" as well as an integer "group_id". I'd like to invert the values of the prio-column for one of the groups. The prio numbers start with 3 and there are 1159 different prios in this group. At

Re: [SQL] Calculate the ratio

Great. This works like I wanted. Thanks!

[SQL] Calculate the ratio

Hi all, This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf). Structure: gid; gridyear; gwcode; area; cf I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and grou

Re: [SQL] How to remove a set of characters in text-columns ?

problem. :) my replace...replace... was a wee bit tedious ;) thanks a lot to you, Charlie and Steve :) regards Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How to remove a set of characters in text-columns ?

Hi, how can I remove a set of characters in text-columns ? Say I'd like to remove { } ( ) ' " , ; . : ! Of course I can chain replace ( replace ( replace ( replace ( ... , '' ) and replace the chars one by one against an empty string ''. There might be a more elegant way. Is there ? rega

[SQL] problem with selecting from a function

Hi, I've got a table with a couple of objects. Primary key object_id. There is a function that fetches some values from another table that relate to an object_id. Like fctX ( 7 ) --> set of typX ... ( 14, 'bla' ), ( 17, 'blu' ), ( 21, 'ble' ), ... The result of the function can have 0 or mo

Re: [SQL] Subselects not allowed?

Leif Biberg Kristensen wrote: > On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote: > > I've written a blog post which I hope may be helpful to others in a similar > > situation: > > > > <http://solumslekt.org/blog/?p=321> > > > >

Re: [SQL] Subselects not allowed?

-+-+-+---+--- seq_foo | 99 | 99 |1 | 9223372036854775807 | 1 | 1 | 1 | f | f (1 row) I'm using 9.1Beta, but it works since 9.0, see: http://www.depesz.com/index.php/2009/11/01/waiti

Re: [SQL] need help with some aggregation magic

Am 09.06.2011 18:20, schrieb Richard Broersma: On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote: I have a log-table that stores events of users and projects like this ( user_id integer, project_id integer, ts timestamp, event_type integer ) I need an aggregated list of worktime per user, per

Re: [SQL] need help with some aggregation magic

an user cannot be in more than one project at the time? If so, can't be overlapping, right? Best, Oliveiros - Original Message - From: "Andreas" To: Sent: Thursday, June 09, 2011 2:43 PM Subject: [SQL] need help with some aggregation magic hi, I have a log-table tha

[SQL] need help with some aggregation magic

hi, I have a log-table that stores events of users and projects like this ( user_id integer, project_id integer, ts timestamp, event_type integer ) I need an aggregated list of worktime per user, per project, per day. The users can switch projects during the day so I can't work this out with mi

Re: [SQL] unnest in SELECT

a select statement "select unnest(vector1) as v1, > unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and > vector2 has a length of 4, the result set will have 12 rows with the > data of vector1 repeating 4 times and vector2 repeating 3 times. Ye

Re: [SQL] How to realize ROW_NUMBER() in 8.3?

om t1 left join t2... ... tn > where ... ... > > Just for people using 8.3, this is mimic row_number. Not really... you have to reset the sequence after the select, and i'm not sure about ordering the result. Andreas -- Really, I'm not out to destroy Microsof

[SQL] unnesting of array of different size explodes memory

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-

[SQL] replace_matches does not return {null}

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

Re: [SQL] ARRAY_AGG and COUNT

Great. Thanks. Do you have a suggestion on how to ignore the group id's with 0 as value? I dont want these to be counted. Thanks! 2011/2/17 bricklen > On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen > wrote: > > Hi all! > > > > I have tried the below quer

[SQL] ARRAY_AGG and COUNT

Hi all! I am working on a query to identify which group ids exists within a spatial cell. In this case i have the GREG table which has polygon data and the priogrid_land which have the cell polygon. I want to identify which and how many GREG group ids exists within each of the priogrid_land cells.

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

  1   2   3   4   5   >