[SQL] What does mod - in "mod statement" - stand for?

Hi, Could you please tell me what does mod - in "mod statement" - stand for? "log_statement (string) Controls which SQL statements are logged. Valid values are none, ddl, mod, and all. (...). mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, an

Re: [SQL] SQL stored function inserting and returning data in a row.

> What about > $$ > INSERT INTO ; > select currval('seq_matchmaking_session_id'); > $$ language sql; > > ? Indeed... :-( For some reason, I thought that it was not possible to have to SQL statement in an SQL stored function. By the way, is there any performance difference between pure SQL a

[SQL] SQL stored function inserting and returning data in a row.

Hi, Is there any way to define a SQL stored function that inserts a row in a table and returns the serial generated? CREATE TABLE matchmaking_session ( session_id bigint NOT NULL DEFAULT nextval('seq_matchmaking_session_id'), ... ); CREATE FUNCTION create_matchmaking_sesssion(...) RETURNS

[SQL] Using schema

Hi, I tried to figure out how to select a particular schema before executing statement on its objects without having to specify the schema in these statements (cf. the instruction USE supported in some other RDBMS) PostgreSQL allows modifying dynamically the schema search path: SET search_path

Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

> De : Tom Lane [mailto:[EMAIL PROTECTED] > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I did the following test, removing all the where-clause from the SELECT > statement. Every statement completes immediately, i.e. it doesn't block. > > I

Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : mardi, novembre 27, 2007 23:46 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE > > "Daniel Caune" <[

[SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

Hi, I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR UPDATE in PostgreSQL 8.1. The number of rows returned is actually (n - 1). I'm trying to find whether this is an identified issue with PostgreSQL 8.1 that might have been fixed in a later version such as 8.2; I don't have

[SQL] Erlang & PostgreSQL native driver

Hi, We are using Erlang, PostgreSQL 8.2.5, and the Erlang library pgsql developed by Christian Sunesson (http://jungerl.cvs.sourceforge.net/jungerl/jungerl/lib/pgsql/). The Erlang library pgsql is easy to use and does the job so far, at least for our prototyping phase. However, this library has

Re: [SQL] Calling void functions

> PERFORM works only in plpgsql, Peter wrote a pl/sql-function... > Oups, sorry! I missed the point. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Calling void functions

> I'm informed that the last statement of a function that returns void > cannot be > a SELECT. How else is one supposed to call another function which also > returns void? > PERFORM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[SQL] Sequence vs Serial

Hi, I was wondering when it is better to choose sequence, and when it is better to use serial. The serial type is a sequence with default parameters (http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SE RIAL). Actually, I never use serial - I prefer sequence for some re

Re: [SQL] help with version checking

> I tried to do the following in PostgreSQL: > > DECLARE >v_version VARCHAR; > > BEGIN >SELECT version INTO v_version FROM version WHERE id = 1; > >IF v_version <> ''1.0.0.0'' THEN > RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, > detected version %'', v_version; >

[SQL] DELETE RETURNING

Hi, I have read in the future PostgreSQL 8.2 release note (http://developer.postgresql.org/pgdocs/postgres/release-8-2.html) that DELETE RETURNING will be supported. Tom, could please confirm that the following PL/PGSQL statements will work on PostgreSQL 8.2? INSERT INTO a(x, y) DELETE FRO

Re: [SQL] conversion of numeric field from MSSQL to postgres

> De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de Kenneth Gonsalves > Envoyé : 15 octobre 2006 08:31 > À : pgsql-sql@postgresql.org > Objet : [SQL] conversion of numeric field from MSSQL to postgres > > hi, > > am migrating a database from MSSQL to postgres. How would i migrate >

Re: [SQL] migrating numeric to serial from MSSQL to postgres

> I believe: > IDENTITY(1, 1) just means "Primary Key" in M$SQL > IDENTITY is not obligatory a primary key. It's an auto-incremented column. It might correspond to a PostgreSQL sequence. [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts _ia-iz_3iex.asp] > numeric 18,

Re: [SQL] migrating numeric to serial from MSSQL to postgres

> hi, > > am migrating a database from MSSQL to postgres. How would i migrate > this: > > [Id] [numerc](18, 0) IDENTITY (1, 1) > You might want to create a sequence first, such as with more or less options: CREATE SEQUENCE my_sequence INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1

[SQL] Evaluation of if conditions

Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which

[SQL] SQL92 compliance

Hi, Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92 compliant? PostgreSQL requires this keyword by default when defining alias, which might be good thing. I mean, I would prefer being notified from a syntax error than spending a couple of hours wondering why a client a

Re: [SQL] PostgreSQL server terminated by signal 11

> De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : vendredi, juillet 28, 2006 09:38 > À : Daniel Caune > Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTEC

Re: [SQL] PostgreSQL server terminated by signal 11

> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 19:26 > À : Daniel Caune > Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > &qu

Re: [SQL] PostgreSQL server terminated by signal 11

> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de D'Arcy J.M. Cain > Envoyé : jeudi 27 juillet 2006 19:49 > À : Daniel Caune > Cc : [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql- > [EMAIL PROTECTED] > O

Re: [SQL] PostgreSQL server terminated by signal 11

> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de Tom Lane > Envoyé : jeudi 27 juillet 2006 19:26 > À : Daniel Caune > Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org > Objet : Re: [SQL] PostgreSQL server terminated

Re: [SQL] PostgreSQL server terminated by signal 11

> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 16:06 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[E

Re: [SQL] PostgreSQL server terminated by signal 11

> De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 16:06 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > My Post

[SQL] PostgreSQL server terminated by signal 11

dy dropped the table, inserted data, and tried to create all the indexes.  The server systematically crashed when creating some specific indexes.  The only idea I have for the moment would be to setup another machine with the same database environment.  Other idea(s)?   Thanks     -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613  

[SQL] Trigger, record "old" is not assigned yet

,     -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613  

[SQL] Invalid memory alloc request size

earched for some similar cases in the pgsql-sql archive but nothing really similar. Any idea? Regards, P.S.: I don't have this problem on other tables containing less data. -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)-

[SQL] Local variable and column name conflict

; For instance, Ingres suggests prefixing local variable/parameter with ":" in the query: UPDATE bar SET i = :i; I can continue using the de facto Oracle's naming convention (P_parameter and V_local_variable) anyway. That's not a big deal. Regards, -- Daniel

Re: [SQL] Constraint UNIQUE on a column not case sensitive

> -Message d'origine- > De : Michael Glaesemann [mailto:[EMAIL PROTECTED] > Envoyé : samedi 1 juillet 2006 10:01 > À : Daniel CAUNE > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive > > > On Jul 1, 200

[SQL] Constraint UNIQUE on a column not case sensitive

Hi, I would like to find an efficient solution for adding/implementing a constraint UNIQUE on a VARCHAR column not case sensitive: ALTER TABLE MyTable ADD CONSTRAINT UNQ_MyTable_MyColumn UNIQUE (lower(MyColumn)); -- invalid syntax The idea is to have an index on that column, in a n

[SQL] Documentation Generator for pl/pgsql

?   Thanks,   -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613  

Re: [SQL] keeping last 30 entries of a log table

> >> insert into log (account_id, message) values (1, 'this is a test); > >> delete from log where account_id = 1 and id not in ( select id from log > >>where account_id = 1 order by timestamp desc limit 30); > >> > >> I'm wondering if there is a more performance oriented method of doing > the

Re: [SQL] keeping last 30 entries of a log table

> I need to write a function which inserts a log entry in a log table and > only > keeps the last 30 records. I was thinking of using a subquery similar to > the > following: > > insert into log (account_id, message) values (1, 'this is a test); > delete from log where account_id = 1 and id not i

Re: [SQL] "could not open file" issue

> De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Andrew Sullivan > > On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? >

Re: [SQL] "could not open file" issue

> De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > Daniel Caune wrote: > > > > > De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > > > > > Daniel Caune wrote: > > > > > > > > select count(*) from eventplayerleaveroom; &

Re: [SQL] "could not open file" issue

> De : Alvaro Herrera [mailto:[EMAIL PROTECTED] > > Daniel Caune wrote: > > Hi, > > > > Is there any way to solve the following issue without dropping the > > table? > > > > select count(*) from eventplayerleaveroom; > > ERROR: could not a

[SQL] "could not open file" issue

rver 8.1.3 -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Index on nullable column

> > Is an index on a nullable column useful for retrieving rows having that > > column null? > > Nope, because IS NULL isn't an indexable operator. > > You can make an end-run around that with a partial index, eg > > create index fooi on foo(f1) where f1 is null > > This can be used to sa

[SQL] Index on nullable column

Hi, Is an index on a nullable column useful for retrieving rows having that column null? SELECT PlayerID FROM PlayerLoginSession WHERE EndTime IS NULL; Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast

Re: [SQL] OUT parameter

anks for the correction; I'm reading Daniel Caune's notes > on the docs now. > > -Owen Funny! I started that thread on OUT parameter; that's a kind of circle... :-) -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadca

[SQL] OUT parameter

, b2 OUT int) AS $$ V_b1 int; V_b2 int; BEGIN FOR (...) LOOP V_b1 = (...); V_b2 = (...); END LOOP; b1 = V_b1; b2 = V_b2; END; $$ LANGUAGE PLPGSQL; Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418

Re: [SQL] Custom type

each custom type I create. > > > > Thanks, > > > > > > -- > > Daniel CAUNE > > Ubisoft Online Technology > > (514) 4090 2040 ext. 5418 > > > > Daniel, > > From the \h command in psql: > > rnd=# \h comment > Command: COMMENT &

[SQL] Custom type

Hi, How can I enter description for my custom types? \dT provides information such as schema, name, and description for all the registered types and custom types. I would like to provide a description for each custom type I create. Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 4090

Re: [SQL] Power cut and performance problem

> > For example, the execution of the following query is fast as it used to > > be (gslog_event_id is the primary key on gslog_event): > > > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > > > > while the following query is really slow (several minutes): > > > > select m

Re: [SQL] Power cut and performance problem

> BTW, I didn't complete my first thought above, which was to ask when you > last > vacuumed the DB, but then I saw that you were running autovac, so that > wasn't > likely the problem. > > BTW, if the problem is actually a raid array that is rebuilding, it should > be > (hopefullY) fixed by tomor

Re: [SQL] Update counter when row SELECT'd ... ?

> I have a simple table: > > name, url, counter > > I want to be able to do: > > SELECT * FROM table ORDER BY counter limit 5; > > But, I want counter to be incremented by 1 *if* the row is included in > that 5 ... so that those 5 basically move to the bottom of the list, and > the next 5 come

Re: [SQL] Power cut and performance problem

> I see you're running autovacuum. What's your disk subsytem look like? By > chance is it sitting on a RAID 5 that's running in degraded mode right now > while it scrubs? > Yes, that should be the problem. I will check that tomorrow morning with a Linux administrator. Thanks.

[SQL] Power cut and performance problem

OG: incomplete startup packet Where can I check, please? Is it more likely a hardware problem (the machine seems ok, no error detected)? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] help with function

> Hello, > > I have 2 tables where each table has a column named "comments" and the > tables are related as a one to many. I want to concatenate all the > comments of the many side to the one side so I wrote the following > plpgsql function to do so. > > > CREATE OR REPLACE FUNCTION fixcomment

Re: [SQL] connectby documentation

> -Message d'origine- > De : Michael Fuhr [mailto:[EMAIL PROTECTED] > Envoyé : lundi, mars 13, 2006 19:26 > À : Daniel Caune > Cc : [EMAIL PROTECTED]; postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 06:38:14PM -05

Re: [SQL] connectby documentation

> > > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > > works fine... Just a link on the documentation that fully explains > how > > > connectby() works would be great! :-) > > > > The contrib package should have installed a file named > README.tablefunc. > > > > You are

Re: [SQL] connectby documentation

> > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1" > > works fine... Just a link on the documentation that fully explains how > > connectby() works would be great! :-) > > The contrib package should have installed a file named README.tablefunc. > You are right. The documen

Re: [SQL] connectby documentation

> "outdated" packets is unfortunately a big issue on Debian. If you want > to have up-to-date apt-packages try > > www.backports.org > > Add one of the mirrors from the list to your sources.list, then run > apt-get update and then try to install again ... > :-) And you'll see, that you can instal

Re: [SQL] connectby documentation

> -Message d'origine- > De : Michael Fuhr [mailto:[EMAIL PROTECTED] > Envoyé : lundi, mars 13, 2006 11:12 > À : Daniel Caune > Cc : postgresql sql list > Objet : Re: [SQL] connectby documentation > > On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wr

[SQL] connectby documentation

Hi, I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... equivalence. It seems that PostgreSQL (version >= 7.4) supports a function connectby that provides similar feature. Unfortunately I don't find any documentation on that function. Could you please give me a link on such document

Re: [SQL] Ask a PostgreSql question (about select )

> Objet : Re: [SQL] Ask a PostgreSql question (about select ) > > Please post questions to the list. I'm forwarding this to the SQL > list, as I think it's probably most applicable. I don't know much > about the Oracle syntax you've used. Hopefully someone will be able > to help you. > > On Mar 1

Re: [SQL] pgsql aggregate: conditional max

> Hi, > > I need a special aggregation function. For instance, given the following > table data: > >aid| cat | weight > --+-+- > a1 | Drama | 1 > a1 | Romance | 6 > a1 | Short | 1 > a1 | Other | 7 > a2 | Comedy | 1

[SQL] plsql / time statement

Hi,   Is there any option to set so that psql provides the execution time of each SQL statement executed?   Regards,   -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418  

Re: [SQL] How to force PostgreSQL using an index

> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : mercredi, février 15, 2006 17:47 > À : Daniel Caune > Cc : Andrew Sullivan; pgsql-sql@postgresql.org > Objet : Re: [SQL] How to force PostgreSQL using an index > > "Daniel

Re: [SQL] How to force PostgreSQL using an index

> "Owen Jacobson" <[EMAIL PROTECTED]> writes: > > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > >> I see, but that doesn't explain whether it is possible to specify the > >> index to use. It seems that those options just force

Re: [SQL] How to force PostgreSQL using an index

> > > > Is there a way to force PostgreSQL using an index for a SELECT > > > > statement? > > > > > > Your best bet is to do > > > > > > set enable_indexscan=false; > > > > > > and then do the EXPLAIN ANALYSE for your select. > > > > I see, but that doesn't explain whether it is possible to speci

Re: [SQL] How to force PostgreSQL using an index

> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > > Hi, > > > > > > > > Is there a way to force PostgreSQL using an index for a SELECT > > statement? I just want to confirm that the index PostgreSQL decides to > > use is better than

[SQL] How to force PostgreSQL using an index

Hi,   Is there a way to force PostgreSQL using an index for a SELECT statement?  I just want to confirm that the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already analyze the table).   Regards,   -- Daniel CAUNE Ubisoft Online

Re: [SQL] Does PostgreSQL support job?

> Daniel Caune wrote: > >>> I'm not sure to understand. Why calling a function from a script is > >>> different from executing a series of SQL commands? > > [snip] > >>>Does that make sense? > >>It does make sense if myjob() does

Re: [SQL] Does PostgreSQL support job?

> > I'm not sure to understand. Why calling a function from a script is > different from executing a series of SQL commands? I mean, I can run a > script defined as follows: > > > > SELECT myjob(); > > > > where myjob is a stored procedure such as: > > > > CREATE OR REPLACE FUNCTION myjob() > >

Re: [SQL] Does PostgreSQL support job?

> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Alvaro Herrera > Envoyé : mercredi 1 février 2006 19:28 > À : Daniel Caune > Cc : Owen Jacobson; pgsql-sql@postgresql.org > Objet : Re: [SQL] Does PostgreSQL supp

Re: [SQL] Does PostgreSQL support job?

> -Message d'origine- > De : Owen Jacobson [mailto:[EMAIL PROTECTED] > Envoyé : mercredi, février 01, 2006 18:00 > À : Daniel Caune; pgsql-sql@postgresql.org > Objet : RE: [SQL] Does PostgreSQL support job? > > Daniel Caune wrote: > > Hi, > >

Re: [SQL] Does PostgreSQL support job?

> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de Bruce Momjian > Envoyé : mercredi, février 01, 2006 17:57 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Does PostgreSQL support jo

[SQL] Does PostgreSQL support job?

-scripts on Linux.   Thanks (Tom Lane J)   -- Daniel CAUNE  

[SQL] CREATE INDEX with order clause

lly, I’m not sure that is useful; perhaps PostgreSQL handles pretty well such query using an index such as:   CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC   ON GSLOG_EVENT(PLAYER_USERNAME, EVENT_NAME, EVENT_DATE_CREATED);   Any idea?   -- Daniel CA

Re: [SQL] Function with default value?

> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : dimanche 29 janvier 2006 10:48 > À : Daniel CAUNE > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Function with default value? > > Daniel CAUNE <[EMAIL PROTECTED]> wri

[SQL] Function with default value?

Hi,   Is it possible to define a function with some default values?   CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) …   Anyway, I didn’t find such a feature described in the PostgreSQL 8.1 documentation, but sometimes, that doesn’t mean that the feature doesn’t exis

[SQL] [HELP] Defining a function as a procedure

Hi,   Is there a way to define a function as a procedure, I mean a function that returns nothing.   CREATE OR REPLACE FUNCTION foo() AS $$ BEGIN   END; $$ LANGUAGE 'plpgsql';   Actually, PostgreSQL complains as a “function result type must be specified”.  I can patch my function

Re: [SQL] Executing plpgsql scripts using psql, is that possible?

> If you want to use plpgsql it will need to be within a function. In your > reply > you mention creating user-defined functions as part of the set up > procedure. > It would not be weird to include the static data function as part of that > procedure and then call it to load the data. I see no re

Re: [SQL] Executing plpgsql scripts using psql, is that possible?

> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] De la part de John DeSoi > Envoyé : lundi 16 janvier 2006 08:51 > À : Daniel CAUNE > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Executing plpgsql scripts using psql, is

[SQL] Executing plpgsql scripts using psql, is that possible?

Hi, I would like to write some administration plpgsql scripts that populate some tables (dimension tables) and to execute them using psql. I’m not sure that is possible with psql as it is with Oracle sqlplus or SQL Server MSQuery: Oracle sqlplus: --- DECLARE V_MyObjectID bigint;