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 > ---++---+

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] 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 > &

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

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

Re: [SQL] Mac Address

r a IP. (arp -a ip) Btw.: please no HTML in eMails. HTH, 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."(

Re: [SQL] Substitute a Character

egexp_replace('A98526', '^.', '0'); regexp_replace 098526 (1 row) http://www.postgresql.org/docs/8.1/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP Andreas -- Really, I'm not out to destroy Microsoft.

Re: [SQL] [GENERAL] How to split a table?

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? Andreas -- Really, I'm not out to destroy

Re: [SQL] [GENERAL] Any documatation about porting from Oracle to PostgreSQL

Felix Zhang <[EMAIL PROTECTED]> schrieb: > Hi all, > > I'm a newbie of PostgreSQL. I'm searching materials about porting from Oracle > to PostgreSQL. > Anyone can share with me some good documatations? http://techdocs.postgresql.org/#convertfrom Andreas --

Re: [SQL] drop a check

est=# alter table bla drop CONSTRAINT bla_id_check; ALTER TABLE test=# \d bla Table "public.bla" Column | Type | Modifiers +-+--- id | integer | HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely uninte

Re: [SQL] drop a check

ivan marchesini <[EMAIL PROTECTED]> schrieb: > . > or it depend on the fact I'm using postgres 7.4.13 > many thanks... Maybe, the information-schema can be different in different versions. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a

Re: [SQL] select into

html#AEN54014 > Regards, Richard, the plain SQL "INSERT INTO " is an other thing as the original problem... He is in a PL/pgsql - function and he should rewrite this to use EXECUTE ... INTO Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintent

Re: [SQL] select into

. > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > " SELECT INTO is not currently supported within EXECUTE." Thats right. The solution with 8.1 or later is: EXECUTE INTO var, and _not_ SELECT INTO var. Andre

Re: [SQL] consistent random order

fm_please> For information about cursor 18:25 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-cursors.html 18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-declare.html With a CURSOR, you get one result-set and can walk through this

Re: [SQL] Changing point for commas and commas for point

#x27;) > > Is there any way to make this happens ? I think, you can use the 'G' as a group separator for this: test=*# select to_char(12345.67, '999G990D00'); to_char - 12.345,67 (1 row) Andreas -- Really, I'm n

Re: [SQL] [ADMIN] Deadlock on transaction

Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> schrieb: > I mean really deadlock. Other transactions can't access the database until the > main transaction is complete. A question: That's not true. > > PostgreSQL doesn't permit multiple transactions concurrently ? Why not? Show us a complete

Re: [SQL] sub-limiting a query

rg/blog/index.php?/archives/7-Window-function-presentation.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."(u

Re: [SQL] Inc

gt;END; > >$$ LANGUAGE plpgsql; > >CREATE TRIGGER my_counter_trig BEFORE UPDATE ON my_table > >FOR EACH ROW EXECUTE PROCEDURE my_autoinc(); Your task is only to adapt this to your table-design. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a compl

Re: [SQL] Inc

HERE your_pkey = ; > > not do what you need? I think, he is searching for a TRIGGER and he has got the solution. 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] how to use a date range in a join

2007-03-21 00:00:00 2007-03-22 00:00:00 (10 rows) If not, sorry, then please define your problem. 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 recompil

Re: [SQL] ordering by multiple columns

mething like this: order by a.column1, case when c.value != 19 then b.column2 else c.column2 end; *untested*, but the way is to use a case when then ... else ... end; - construct. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side eff

Re: [SQL] union with count?

ithout grouping the not-aggregated columns. Expand your 'group by' with word and word_position. 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 recompi

Re: [SQL] plpgsql function question

ion ... returns int as $$ ... return 1; end; $$ language plpgsql; > >Q) How will I passe the table structure as as parameter As i said, for instance as simple text and EXECUTE this. > 2) Do I need to create a type? No. Andreas -- Really, I'm not out to

Re: [SQL] show index from [table]

et information about the indices > which have been created on a table? Yes, of corse. Please start psql with the -E - option. Now you can see the underlying sql-statement for commands like \di. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unin

Re: [SQL] joining a table whose name is stored in the primary record

out_name2 := my_name; return next; end loop; end; $$ language plpgsql; And now a test: test=*# select * from zip_foo(); out_id | out_name | out_name2 +--+--- 1 | 10001| value 12 2 | 1| value 99 (2 rows) HTH, And

Re: [SQL] Counting all rows

A "select count(*) from foo;" enforces a seq. scan. Solutions for this are discussed here: http://www.varlena.com/GeneralBits/120.php Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus To

Re: [SQL] Format intervall as hours/minutes etc

Andreas Joseph Krogh <[EMAIL PROTECTED]> schrieb: > Hi all. Any hint on how to format this interval as number of hour/seconds etc? > select age('2007-09-22 17:00'::timestamp, '2000-02-20 18:00'::timestamp); > age > --

Re: [SQL] What SQL is running against my DB?

> application. Is there a way to turn on logging in the server so that obtain > this information? Yes, of course. You can set in your postgresql.conf: log_statement = 'all' to log all statements. Read this file for more details, it is well documented. Andreas -- Really, I&#

Re: [PERFORM] [SQL] two queryes in a single tablescan

ill > synchronize the scans. works this right across different transactions? I mean, for instance, TX a insert rows and TX b insert other rows and both clients (with different transactions) starts a seq-scan? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely

Re: [SQL] Removing whitespace using regexp_replace

or regexp_replace() you need an extra parameter 'g' like below: test=*# select 'x' || regexp_replace(regexp_replace(' \t\tfoo bar ', '^[ \t]+','','g'),'[ \t]+$','','g') || 'x

Re: [SQL] Removing whitespace using regexp_replace

Thomas Kellerer <[EMAIL PROTECTED]> schrieb: > Andreas Kretschmer wrote on 28.10.2007 12:42: > >>I have a column with the datatype "text" that may contain leading > >>whitespace (tabs, spaces newlines, ...) and I would like to remove them > >>all

Re: [SQL] execute system command from storage procedure

Sabin Coanda <[EMAIL PROTECTED]> schrieb: > Hi there, > > Is it possible to execute a system command from a function ? (e.g. bash ) Yes, of course, but you need an untrusted language like pl/perlU oder plsh. http://plsh.projects.postgresql.org/ Andreas -- Really, I'

Re: [SQL] INSERT INTO relational tables

osgres/sql an abstraction-layer that allows me to insert as if the > information was stored in one table? (Something like a VIEW that > provides INSERT, UPDATE, ??? and automatically inserts the referenced > ID.) You can create such an abstraction layer. Andreas (from germany...) -- Really, I

Re: [SQL] Concatenation through SQL

1 row) * comma-aggregate, see http://www.zigo.dhs.org/postgresql/#comma_aggregate 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

Re: [SQL] merge timestamps to intervals

--- 2008-05-01 10:00:00 | 2008-05-01 10:00:01 | UP 2008-05-01 10:00:02 | 2008-05-01 10:00:02 | DOWN 2008-05-01 10:00:03 | 2008-05-01 10:00:04 | UP 2008-05-01 10:00:05 | 2008-05-01 10:00:06 | DOWN (4 Zeilen) Is this okay for you? Andreas -- Really, I'm not out to destroy Micro

Re: [SQL] Results with leading zero

Am Mon, 16 Jun 2008 11:48:01 +1000 schrieb novice <[EMAIL PROTECTED]>: You are watching for the lpad() function ... > I have a table > > CREATE TABLE problem ( > problem_id integer, > solution_count integer > ); > > INSERT INTO problem VALUES (1001, 4); > INSERT INTO problem VALUES (1012

Re: [SQL] using calculated column in where-clause

Patrick Scharrenberg <[EMAIL PROTECTED]> schrieb: > Something like this > select a, b , a*b as c from ta where c=2; > > But postgresql complains, that column "c" does not exist. > > Do I have to repeat the calculation (which might be even more complex yes.

Re: [SQL] Check a column value not in Array.

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi everybody, I was reading the mails concerning the subject "Check a column value not in Array" and made a quick test. This is the test table: test=# select * from values; id | item - +- 1 | a

[SQL] INSERT or UPDATE

INSERT or must i program something with PHP for example? Thanks 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] Select function with set return type

> cannot accept a set Try 'select * from my_function();' 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

Re: [SQL] EXECUTE query INTO problem

NULL at character X Wild guess: one or both parameters contains nothing, NULL. If you concat a string with NULL, the result is NULL. And you can't execute a NULL-command. > >I've also tried declaring cod as row, but the error is the same. > >Anybody can help me? P

Re: [SQL] adding "order by" to a "group by" query

from http://www.zigo.dhs.org/postgresql/#comma_aggregate) Hope that helps. 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 --e

Re: [SQL] adding "order by" to a "group by" query

| a, b, c > >> (1 row) > >> > >> Time: 0.554 ms > >> test=*# select i, comma(t) from (select distinct i,t from foo order by t > >> desc) bar group by i; > > > > Thanks Andreas, that would be good solution. > > > > (still curious

Re: [SQL] Seeking for the fore-part of the key

=40.65..1779.81 rows=1595 width=13) (actual time=0.021..0.021 rows=0 loops=1) Filter: (lower(w) ~~ 'foo%'::text) -> Bitmap Index Scan on idx_words_w (cost=0.00..40.25 rows=1595 width=0) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: ((lower(w) ~>=~ 'f

Re: [SQL] counts of groupings by date year-month

ar_month" > doesn't exist in temp table. > > Is it possible to get counts by year_month? change count(year_month) to count(1), untested. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [SQL] [PHP]

inbox. More than messages <http://www.microsoft.com/windows/windowslive/> -- ------ Netzmeister St.Pauli St.Pauli - Hamburg - Germany Andreas Wenk Lutterothstr 82 20255 Hamburg Tel.: +49 (0) 40 319 43 54 http://www.netzmeister-st-pauli.de mailto:a.w...@netz

Re: [SQL] how to set the value to the column

set id = nextval('foo_seq'); UPDATE 5 Zeit: 0,379 ms test=*# select * from foo; name | id --+ a| 1 b| 2 c| 3 d| 4 e| 5 (5 Zeilen) Zeit: 0,241 ms test=*# Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintent

AW: [HACKERS] ask for help !!! (emergency case)

s not show any problem but it doen't have data in table > t1 and table t2 Version 7.1 will give you an error if you don't create an appropriate insert and update rule for the view. Insert and update rules are not yet automatically created for views. Andreas

[SQL] Referencing named attribute in where clause doesn't work with 7.1.2?

; ERROR: Attribute 'title_text_value' not found Issuing the same query without the where-clause does work tho, but return tupples with null in them which I don't want. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Referencing named attribute in where clause doesn't workwith 7.1.2?

Thomas Good wrote: > > On Wed, 8 Aug 2001, Andreas Joseph Krogh wrote: > > > Hi, this is my first post to this list so please... > > I have problems getting this query to work, any ideas? > > > > select article.title_text_key, > > (selec

Re: [SQL] RE: Referencing named attribute in where clause doesn't work with7.1.2?

N on_text.lang_id = code.id AND code_group.description = 'lang' AND code.code_key = 'lang.NO') ON article.title_text_key = on_text.text_key WHERE on_text.text_value IS NOT NULL; And now it works! Thank you for helping me out. -- Andreas Joseph Krogh <[EMAIL PROTECTED]>

Re: [SQL] Trigger Problem

es are sql,c,internal and > the created procedure languages. > > NOTE: > > I have studied in documenatation that only plpgsql > functions are written to be used in triggers. You need to issue: $ createlang plpgsql to create the plpgsql language on your database. -- Andreas Josep

Re: [SQL] graphical interface - admin

h Berkus Take a look at TOra - Toolkit For Oracle: http://www.globecom.se/tora/ It has excellent PostgreSQL support. -- Andreas Joseph Krogh (Senior Software Developer) <[EMAIL PROTECTED]> "Everything should be made as simple as possible, but not simpler" - Albert Einst

Re: [SQL] graphical interface - admin

On Monday 01 July 2002 13:29, you wrote: > On Mon, 1 Jul 2002, Andreas Joseph Krogh wrote: > > Take a look at TOra - Toolkit For Oracle: http://www.globecom.se/tora/ > > > > It has excellent PostgreSQL support. > > I tried 1.2.4 but didn't found how to configur

Re: [SQL] graphical interface - admin

On Monday 01 July 2002 15:00, Oleg Bartunov wrote: > On Mon, 1 Jul 2002, Andreas Joseph Krogh wrote: > > On Monday 01 July 2002 13:29, you wrote: > > > On Mon, 1 Jul 2002, Andreas Joseph Krogh wrote: > > > > Take a look at TOra - Toolkit For Oracle: >

Re: [SQL] Editor for pgsql

ight now(on Mandrake-8.1 with KDE-3.0.2 ant qt-3.0.4) with the following options to configure: ./configure --without-oracle --without-kde make su -c "make install" This compiles and installes just fine to /usr/local/bin with PostgreSQL support. -- Andreas Joseph Krogh (Senior Software

Re: [SQL] Stripping white-space in SELECT statments

7; terminate its strings proparly and some random byte gets in the query. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] 7.3 schemas

Hi! I've just started to look at 7.3, and have created a schema with the "CREATE SCHEMA" command. How do I use/connect to theese schemas? I also want to use the schemas form JDBC, how is the connect-URL sopposed to be? Any links to docs on the new schema support are appreciate

[SQL] Problems with to_char(created, 'WW')

36.84+02 | 42 2002-10-15 15:21:26.59+02 | 42 2002-10-20 12:14:05.203+02 | 42 2002-10-20 20:19:44.309+02 | 42 2002-10-21 14:23:31.425+02 | 42 2002-10-22 12:12:31.63+02 | 43 2002-10-23 14:00:18.478+02 | 43 (25 rows) Now - my question is, why is monday 21. in week 42, but tuesday 22. in we

[SQL] importing a 7.2 db with contrib/tsearch to 7.3

n the table, the following error occures: e4u=> CREATE INDEX t_idx ON on_article_searchable USING gist (content); ERROR: data type txtidx has no default operator class for access method "gist" You must specify an operator class for the index or define a default operator

Re: [SQL] Need Postgresql Help

w to create stored procedure in postgresql? > 2)How can i use it from perl script with parameters. ? > 3)How to return resultset from that Stored Procedure ? You need 7.3 to do 3). Infor on stored procedures: $INSTALLDIR/doc/html/plpgsql.html -- Andreas Joseph Krogh <[EM

Re: Upgrade question - was Re: [SQL] join and dynamic view

1-5 > > over > > > christmas in fact. > > > > Will I need to dump/restore the database for this upgrade? As allways when version-upgrading(and not patch-level), you need to dump/restore as the binary on-disk format changes. - -- Andreas Joseph Krogh <[EMAIL PROTECTED]&

[SQL] index on to_char(created, 'YYYY') doesn't work

lt of that funtion? Do anyone have an example of such a function? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is, inexplicably, a moron. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNA

Re: [SQL] index on to_char(created, 'YYYY') doesn't work

-- But it failes with: ERROR: DefineIndex: index function must be marked isImmutable Now the question is how do I mark an index function isImmutable? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone wh

Re: [SQL] index on to_char(created, 'YYYY') doesn't work

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 15 January 2003 16:12, you wrote: > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Wednesday 15 January 2003 11:37, you wrote: > &

Re: [SQL] index on to_char(created, 'YYYY') doesn't work

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote: > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Wednesday 15 January 2003 16:12, you wrote

Re: [SQL] index on to_char(created, 'YYYY') doesn't work

achment. Can anyone explain to me how to reed the output from ANALYZE. It seems most of the time is spent sorting and grouping. Are there any ways to optimize this? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is

[SQL] Backup of multiple tables

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I usually backup my database with pg_backup without the -t option. But now I need to only backup certain tables(say tab1 and tab2), is this possible with pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success. - --

Re: [SQL] Backup of multiple tables

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 19 September 2003 17:38, Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > I usually backup my database with pg_backup without the -t option. But > > now I need to only backup certain tables(say ta

Re: [SQL] Inserting data in a table using sub-selects]

iption, '') > FROM table1 t1) > WHERE id NOT IN ( > SELECT id > FROM table1); > > With Oracle there is a slick way to do a partial outer join that allowed > you to do this without creating a complete list of table1.id in the last > subquery, but I dunno if

[SQL] Problem with LATIN1 characters from Perl-DBI

Linux system. I first had the problem printing out LATIN1 chars to stdout too, but solved that by using the pragma use encoding 'ISO-8859-1'; I've tried: $dbh->do("set CLIENT_ENCODING TO 'ISO-8859-1'") or die("Couldn't set encoding to ISO-8859-1

Re: [SQL] Problem with LATIN1 characters from Perl-DBI

rinting it out to stdout, the 'use encoding' pragma took care of the conversion, but that didn't work for inserting the contents of $plain_text into the database. So I must convert it to latin1 using the from_utf8 su

[SQL] Making NULL entries appear first when ORDER BY ASC

I would like to make all entries where start_time IS NULL apear *before* all the others. Any idea how to achieve this? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.

Re: [SQL] Making NULL entries appear first when ORDER BY ASC

r > date. Otherwise change the order in the ORDER BY clause. Thanks! This si, IMO, the cleanest solution as it doesn't involve any COALESCE. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer

[SQL] Very low performance on table with only 298 rows

arturl(id) ON DELETE CASCADE Any hints on how I can "fix" this lousy performance? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +---

Re: [SQL] Very low performance on table with only 298 rows

On Thursday 14 April 2005 13:28, Richard Huxton wrote: > Andreas Joseph Krogh wrote: > > nbeweb=> EXPLAIN select count(*) from onp_web_index; > > QUERY PLAN > > - >

[SQL] row-attribute in EXPLAIN-output doesn't match count(*)

s (3 rows) nbeweb=> select count(*) from onp_web_index; count --- 298 (1 row) Is this normal, shall I just overlook the "rows=160057" output from EXPLAIN, or is something wrong? What does the line "DETAIL: 15

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

On Thursday 14 April 2005 19:12, Richard Huxton wrote: > Andreas Joseph Krogh wrote: > > Hi everybody, I have table which inly has 298 rows in it, but EXPLAIN > > ANALYZE shows that PG thinks it has as much as 160057 rows AFAICT from > > the EXPLAIN-output. > > >

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

On Thursday 14 April 2005 19:33, Andrew Sullivan wrote: > On Thu, Apr 14, 2005 at 07:21:38PM +0200, Andreas Joseph Krogh wrote: > > So, what you're suggesting is that a restart of the webapp should make > > vacuum able to delete those dead rows? > > Yes, but that'll

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

at's probably off topic for > this list.) I don't see this behaviour under 8.0.0, and it's presumably fixed: http://archives.postgresql.org/pgsql-jdbc/2004-06/msg00018.php Is it safe to issue a "kill -2 ", will the connection-pool reconnect? -- Andreas Joseph

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

On Thursday 14 April 2005 22:44, Andrew Sullivan wrote: > On Thu, Apr 14, 2005 at 09:57:03PM +0200, Andreas Joseph Krogh wrote: > > Is it safe to issue a "kill -2 ", will the > > connection-pool reconnect? > > The kill -2 will definitely work to shut the connectio

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

On Friday 15 April 2005 18:22, Markus Schaber wrote: > Hi, Andreas, > > Andreas Joseph Krogh schrieb: > >>>So, what you're suggesting is that a restart of the webapp should make > >>>vacuum able to delete those dead rows? > >> > >>Yes, but t

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

On Friday 15 April 2005 18:34, Markus Schaber wrote: > Hi, Andreas, > > Andreas Joseph Krogh schrieb: > >>Could you try the latest postgresql-8.0-311.jdbcX.jar? The current > >>versions should solve the idle in transaction problem, the previous ones > >>ten

[SQL] echo/printf function in plpgsql

Hi all! Is there a way of echo'ing a string(like "raise notice 'this is id%', id") from plpgsql? I want to echo/print it to STDOUT 'cause the notice-mechanism produces too much noise IMH. -- Andreas ---(end of broadcast)---

Re: [SQL] echo/printf function in plpgsql

On Tuesday 19 July 2005 17:18, Richard Huxton wrote: > Andreas Joseph Krogh wrote: > > Hi all! > > > > Is there a way of echo'ing a string(like "raise notice 'this is id%', > > id") from plpgsql? I want to echo/print it to STDOUT 'c

Re: [SQL] echo/printf function in plpgsql

On Tuesday 19 July 2005 22:09, Tony Wasson wrote: > On 7/19/05, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > On Tuesday 19 July 2005 17:18, Richard Huxton wrote: > > > Andreas Joseph Krogh wrote: > > > > Hi all! > > > > > > > > Is

Re: [SQL] echo/printf function in plpgsql

On Wednesday 20 July 2005 01:39, John DeSoi wrote: > On Jul 19, 2005, at 11:58 AM, Andreas Joseph Krogh wrote: > > I see. Can I make the ouput somehow less verbose? It spits out a > > lot of noise > > for each "NOTICE": > > If you just want to output som

[SQL] Help with UNION query

N DELETE CASCADE ON UPDATE CASCADE, primary key(groupname, username) ); -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ Off

Re: [SQL] Help with UNION query

ERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = 'andreak'; But I would very much appreciate if someone manages to rewrite this query so that it doesn't specify ug.username='anderak' twice. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Sof

Re: [SQL] Help with UNION query

On Sunday 04 September 2005 20:08, Andreas Joseph Krogh wrote: > Replying to my self... Again... I managed to "reduse" my query to the following: SELECT gr.groupname FROM onp_group gr, onp_user u WHERE gr.id IN ( SELECT g.id FROM onp_group g, onp_user_group ug WHER

[SQL] Problems with distinct

onp_group g, onp_group g2 WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children WHERE child_id = g2.id) AND g2.id IN(1,2,109,105, 112); The *only* difference is that the distinct-clause changed place... -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Dev

Re: [SQL] Problems with distinct

On Monday 13 February 2006 20:22, Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > Any idea why this works: > > SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2 > > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM o

[SQL] Problem with query on history table

urr.modified as curr_modified, hist.modified as hist_modified, coalesce(hist.etc, curr.etc) as etc FROM curr LEFT OUTER JOIN hist ON(curr.id = hist.curr_id) WHERE ... I'm really stuck here. It seems to me that I need a lot of CASE...WHEN...ELSE.. statements in the

Re: [SQL] Problem with query on history table

new row (all within one transaction). > > There are other ways to do it, but they'll probably be much slower. I > don't think they require a lot of CASE statements though. > > Show us what you were planning on doing and maybe I'll have more ideas. > > On Mon, Feb 27

[SQL] Constraint question

"is_preffered" so that it's only allowed to be set once pr. businessfield_id pr. company so that only one businessfield can be preferred for a company. Does anyone have an idea how to enforce this? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager

Re: [SQL] Constraint question

On Thursday 18 May 2006 18:38, Markus Schaber wrote: > Hi, Andreas, > > Andreas Joseph Krogh wrote: > > create table onp_crm_businessfield_company( > > businessfield_id integer not null references onp_crm_businessfield(id), > > company_id integer not null ref

Re: [SQL] Alternative to Select in table check constraint

constraint is to allow only one active badge > status at a time. > > But now that I think about it, using the authors suggestion (if it actually > worked), how would would it be possible to change the active status from > one badge to another? First set status='I' on the one that

[SQL] Order of "WITH RECURSIVE" output

efore its respective child, or do I have to calculate a "level" column (like in the example above) and specify "ORDER BY LEVEL" to be 100%? I'm using WITH RECURSIVE as sub-queries to build up arrays of parents in each output-row and would like as little overhead as poss

Re: [SQL] Order of "WITH RECURSIVE" output

On Tuesday 07 July 2009 03:55:35 am Tom Lane wrote: > Andreas Joseph Krogh writes: > > If I omit "ORDER BY", is the output *guaranteed* (according to some > > standard) to be ordered > > No. It's not necessary to read any other details to answer that ... >

<    1   2   3   4   5   >