Re: [SQL] PostgreSQL - ERROR: Cannot cast type character varying to

Under PostgreSQL 7.3 it was necessary to use an intermediate cast to text when converting from varchar to int4. sum( unidades::text::int4 ) should do. Regards, Christoph Mauricio Fernandez wrote: > > Hello I´m new in the list, and I want to learn a lot about postgresql, now > I´m working i

Re: [SQL] how many tuples on a cursor?

Tom Lane wrote: > > Christoph Haller <[EMAIL PROTECTED]> writes: > > What else seems to work is MOVE 2147483647 > > (INT_MAX) and then get the max number of rows > > by PQcmdTuples(). > > I'd suggest "MOVE FORWARD ALL" rather than hard-wirin

Re: [SQL] how many tuples on a cursor?

Andreas Kretschmer wrote: > > Hello, > > I want to know how many tuples there on a cursor, and i found > http://groups.google.de/groups?hl=de&lr=&threadm=3ACA7BB0.7020106%402cactus.com&rnum=7&prev=/groups%3Fhl%3Dde%26lr%3D%26q%3Dpostgres%2Bcursor%2Bcount%26btnG%3DSuche > > But, this posting is v

Re: [SQL] trigger/rule question

Enrico Weigelt wrote: > > * Christoph Haller <[EMAIL PROTECTED]> wrote: > > Hi, > > > I assume this still refers to > > [SQL] RULE for mtime recording > > from last Friday. > > ehm, yeah. I forgot that I've already asked this stuff ... >

Re: [SQL] trigger/rule question

Enrico Weigelt wrote: > > Hi folks, > > for database synchronization I'm maintaining an mtime field in > each record and I'd like to get it updated automatically on > normal writes (insert seems trivial, but update not), but it > must remain untouched when data is coming in from another node > (t

Re: [SQL] people who buy A, also buy C, D, E

Dan Langille wrote: > > On 26 Apr 2005 at 14:24, Christoph Haller wrote: > > > Dan Langille wrote: > > > > > > The goal of my query is: given a book, what did other people who > > > bought this book also buy? I plan the list the 5 most popular such

Re: [SQL] people who buy A, also buy C, D, E

Dan Langille wrote: > > The goal of my query is: given a book, what did other people who > bought this book also buy? I plan the list the 5 most popular such > books. In reality, this isn't about books, but that makes it easier > to understand I think. > > We have a table of customer_id (watch_

Re: [SQL] UPDATE WITH ORDER BY

> Rodrigo Carvalhaes wrote: > > Hi Guys! > > I need to make an UPDATE on a column reordering it with a sequence > using order by a description. > Confusing??? Well.. Let me give an example... > > Today, my table it's organized like this: > > Code / Description > 9 Orange > 15

Re: [SQL] string to date conversion

Jerome Alet wrote: > > Hi, > > I'm working on an existing Pg database which, please don't ask why, > stores dates as varchar attributes in the form '/MM/DD' > > I'm not allowed to modify the tables to use 'timestamp' instead, > so I'd like to convert on the fly when retrieving datas with > s

Re: [SQL] RULE for mtime recording

Enrico Weigelt wrote: > > Hi folks, > > I'd like to write an update rule, which touches the a mtime field > (=current_timestamp) on normal update - when the mtime field isnt > explicitly set. If the update query explictly sets an mtime value, > this value has to be let through. > > my tables loo

Re: [SQL] TIMESTAMP / summertime

T E Schmitz wrote: > > Hello, > > I *detest* British summertime. This year it took me two days to adjust. > Now I am realizing that my program might need some adjusting too: > > Joking aside, I need some advice regarding TIMESTAMP colums and I can't > quite get my head round this at the moment:

Re: [SQL] Your question about date

; RESULT: > --- > result > > 31 > 33 > 25 > > Am Montag, den 21.03.2005, 10:54 +0100 schrieb Christoph Haller: > > Octavio Alvarez wrote: > > > > > > Sorry, I tried to make my subject as good as possible. > > >

Re: [SQL] timestamp precision - can I control precision at select time

"[EMAIL PROTECTED]" wrote: > > I have a database with several tables that use timestamp without time > zone type. I upgraded from an older pgsql and have code that does not > expect the precision in the select output. Is there a way I can get the > effect of zero precision without modifying all th

Re: [SQL]

Octavio Alvarez wrote: > > Sorry, I tried to make my subject as good as possible. Ahem, what subject? > > I have a table where I store the dates in which I take out of my inventory > (like "installation dates") > > table > --- > row_id SERIAL > date DATE > fk_item INTEGER >

Re: [SQL] outputting dates

Kenneth Gonsalves wrote: > > hi, > i have set my datetype to 'European, SQL'. This correctly accepts > dates of the form dd/mm/. However, the output is still in the > '-mm-dd' format. How do i get the default output as dd/mm/? > -- > regards > kg > I have select version();

Re: [SQL] How to force subquery scan?

Tambet Matiisen wrote: > > Another (possibly design?) problem of mine. I have a function > product_cost(product_id, date), which does simple SELECT call. I > declared it as STABLE, hoping that multiple invocations of the same > function are replaced with one. Query is something like this: > > SEL

Re: [SQL] How to cast VARCHAR to BYTEA and vice-versa?

similar errors when trying to go the other direction. > > On Mon, 14 Mar 2005 11:32:26 +0100, Christoph Haller <[EMAIL PROTECTED]> > wrote: > > "Moran.Michael" wrote: > > > > > > Hello all, > > > > > > I have a table with a VARCHA

Re: [SQL] How to cast VARCHAR to BYTEA and vice-versa?

"Moran.Michael" wrote: > > Hello all, > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > How do I cast VARCHAR to BYTEA? > > The following doesn't seem to work as it yields the 'cannot cast varchar to > bytea' error message: > > varchar_data::bytea > > On the

Re: [SQL] Performance of Views

There is no difference in run-time between a VIEW and executing the underlying SELECT because it's the same. You may move your question to the [PERFORMANCE] list for hints to speed up your query. Regards, Christoph Steffen Boehme wrote: > > Hello there, > > i have a short question ... >

Re: [SQL] SQL error: function round(double precision, integer) does not

TJ O'Donnell wrote: > > I received the following error when executing a SQL statement: > > SQL error: > ERROR: function round(double precision, integer) does not exist > > In statement: > > select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, > round((parameter*oe_coun

Re: [SQL] copy rows

I have not been too deep into 8.0, but AFAIK from the 7.x releases, the only way to do things like you describe, is do it the hard way: use SELECT INTO to get the field values you want into variables modify the necessary field values do the INSERT via Executing A Dynamic Command Inserting a re

Re: [SQL] Rule problem with OLD / NEW record set

Ralph Graulich wrote: > > Hello everyone, > > given is a table with a version history kind of thing I am currently > working on. Upon this table there is a view and the application interacts > with the view only, updating/inserting/deleting is controlled by rules. It > seems like the record set "

Re: [SQL] case sensitive/insensitive confusion

Peter Eisentraut wrote: > > Christoph Haller wrote: > > It seems to me under hpux the sort is done case sensitive, > > as would one expect on SQL_ASCII encoding, whereas > > under linux a case insensitive sort is done. > > The sort order depends entirely on the loc

[SQL] case sensitive/insensitive confusion

I am seeing different ORDER BY results on a character column on different machines. I have (1) ResyDBE=# select version(); version PostgreSQL 7.4.5 on hppa-hp-hpux10.20, compiled by GCC gcc (

[SQL] postgressql.org

Has anybody accidentally hit this "postgressql.org"? I did. I don't know if freeloader is the correct english term for the german "Trittbrettfahrer", but that's what it looks like. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forge

Re: [SQL] Unresolved external: tgetent

be fixed. > > --- > > Christoph Haller wrote: > > I doubt this is the right list, but it's the only one I'm subscribed to, > > > > so please, if one of the core members is reading this, forward it to the > > right one. > > &

[SQL] Unresolved external: tgetent

I doubt this is the right list, but it's the only one I'm subscribed to, so please, if one of the core members is reading this, forward it to the right one. I have successfully compiled and installed 7.4.5 yesterday. After doing 'make' I saw the line "Ready to install". template1=# select version

[SQL] SET AUTOCOMMIT TO OFF

Please, could someone point me to the right list or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq. The case is pretty urgent, as we have to move our applications to Linux and thought it's better to use PG7.4. We used to have PG7.3 under HPUX, and we think of a temporary downgrade ba

Re: [SQL] session-wide autocommit off

Richard Huxton wrote: > Christoph Haller wrote: > > Looks like I asked the wrong question the other day. > > How can I achieve a session-wide autocommit off? > > As it is possible inside a psql session via > > \unset AUTOCOMMIT > > I am using the libpq interf

[SQL] session-wide autocommit off

Looks like I asked the wrong question the other day. How can I achieve a session-wide autocommit off? As it is possible inside a psql session via \unset AUTOCOMMIT I am using the libpq interface. TIA Regards, Christoph ---(end of broadcast)--- TIP

[SQL] version 7.4 autocommit removed

The Release Notes on 7.4 state Observe the following incompatibilities: The server-side autocommit setting was removed and reimplemented in client applications and languages. Server-side autocommit was causing too many problems with languages and applications that wanted to control their own auto

Re: [SQL] Finding duplicated values

Kent Anderson wrote: > I have a few tables that have duplicated values from an import from a > different database. I have two keys I tried to set as primary and got > an error ERROR: could not create unique index > DETAIL: Table contains duplicated values.Is there some join I can use > to compa

Re: [SQL] libpq-fe: PQgetvalue() ?

AFAIK it does allocate memory. You cannot PQclear(pgresult_varible) while cstring_varible is in use. You do not need to free cstring_variable, PQclear(pgresult_varible) will do. I personally prefer to allocate local memory, "strcpy" PQgetvalue, and then PQclear. But that's a matter of taste, I supp

Re: [SQL] Stored procedures and "pseudo" fields

Lars Erik Thorsplass wrote: > On Tue, 20 Jul 2004 09:45:06 -0600, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > Kinda like this: > > > > > > SELECT *, acl_check( objects.obid, ) AS mode FROM objects > > > WHERE mode > 0; > > > > Here's the problem. In order to do the select, the query first need

Re: [SQL] select

Knut P Lehre wrote: > I have a table with fields: > id A B C D E F > where id is an int4 primary key. > In this table there is information like: > 1 a1 b1 xxx xxx > 2 a1 b1 xxx xxx xxx xxx > 3 a2

[SQL] How to delete the not DISTINCT ON entries

Referring to the DISTINCT ON example SELECT DISTINCT ON (location) location, time, report FROM weatherReports ORDER BY location, time DESC; How would I delete those entries skipped by the DISTINCT ON expression? TIA Regards, Christoph ---(end of broadcast)

Re: [SQL] a wierd query

> > select distinct on (date, employee_id) employee_id, date from > ( > select distinct on (begindate, employee_id) begindate as date, employ= > ee_id from workhour > UNION > select distinct on (enddate, employee_id) enddate as date, employee_i= > d from workhour > )a

Re: [SQL] where is this problem (trigger)

> > I fire this trigger whenever my client updates a row > in the db: > > CREATE FUNCTION notify_jobinfo() RETURNS "trigger" > AS ' > BEGIN > EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.jobnumber; > RETURN NEW; > END > ' > LANGUAGE plpgsql; > > CREATE TRIGGER notify_jobinfo > AFTER U

[SQL] Index not used - now me

I know there have been dozens of threads on this subject and I have searched the archives well (I hope at least), but still ... I have select version(); version -- PostgreSQL 7.3.4 on hppa-hp-hpux10.20, comp

Re: [SQL] working with schema

> > Hi all, > > I'm just experimenting with schema usage. I'm going to use it as a fake > 'multi-database' system. Is Postgresql support coding schema name using string > variable so I can pass it with parameter? I'm give u an example: > > I have schema: D200401,D200402.D200403,D200404, etc. >

Re: [SQL] Multi ordered select and indexing

Have you thought of using a functional index on both columns? Regards, Christoph > > Hi! > What is the simplest solution for this query type: > > SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC; > > In our experience, postgres cannot use a multi-colum index on (col1, > col2) in t

Re: [SQL] Concatenate results of a single column query

> > > Marco Lazzeri <[EMAIL PROTECTED]> writes: > > > SELECT > > p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id) > > FROM people AS p > > > Any suggestions? > > Something like: > > db=> create aggregate array_aggregate (basetype = integer, sfunc = array_append, > stype =

Re: [SQL] Grant permission to all objects of user A to user B

> > Dear Friends, > > Postgres 7.3.2. > > I have an database with owner USRA who owns about 300 objects (tables and f= > unctions). Now I want to give 'ALL' permission to all the objects of USRA t= > o another database user USRB. > > If I use the grant i need to type all the objects as comma se

Re: [SQL] Row count after SELECT INTO?

> > Dear SQL and plPgSQL gurus, I seek for your wisdom. > > I have a variable assignment via SELECT INTO in my function, and I want > to separate 3 cases: > 1) no rows found; > 2) one row found; > 3) many rows found (ambiguous select). > > The first case is simple, I use FOUND variable for it. >

Re: [SQL] Help with COPY command

> > Ok. I found the libpq syntax for COPY in the Programmer's manual. I've > got a working version, but wanted to verify something. > > PQexec(conn, "COPY foo FROM STDIN"); > PQputline(conn, "3\thello world\t4.5\n"); > PQputline(conn,"4\tgoodbye world\t7.11\n"); > ... > PQputline(conn,"\\.\n"); >

Re: [SQL] sub-select parameter problem

> > Hello, > > Imagine the following query: > > --- > SELECT > > tableA.field1, > tableA.field2, > =20=20 > tableB.field1, > tableB.field2, > > ( > SELECT tableC.field2 > FROM tableC > WHERE tableC.field1 =3D tableB.field1 - 1; >

Re: [SQL] Index not used - now me

> > > On 09/02/2004 12:50 Christoph Haller wrote: > > > > > \d Transfer_ModRange > >Table "public.transfer_modrange" > > Column | Type | Modifiers > > +--+--- > > module_pointer | sma

Re: [SQL] locking problem

> > hi, > > can anyone help me out on the following scenario: > why this is happening, if i'm doing any thing wrong or its the feature of p= > ostgres... I'd say it's a feature - see below. > > regards > cheetor > == > == > ==

[SQL] Empty String Comparison Standard compliant?

Just a short question (PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1) SELECT ('' > 'GDMF') ; SELECT ('GDMF' > ''); ?column? -- f (1 row) ?column? -- t (1 row) Are these results standard compliant? Looks at least pretty reasonable to me. It's just because I

Re: [SQL] How can I get the last element out of GROUP BY sets?

> > I'm trying to produce summary data from a table (using PGSQL 7.4.1): > > CREATE TABLE readings( "when" timestamp, value integer ); > > The summary will be based on various time periods. I've been using date_trunc( > 'hour', "when" ) and GROUP BY for the min/max/average readings with no prob

Re: [SQL] problem with function trigger

> > Hi I'm trying to update a table column with a pl/pgsql function and a > trigger. > But I didn't managed to make it work so far. > > Here's my function code : > > CREATE FUNCTION public.calcul_impact() RETURNS opaque AS ' > > DECLARE > id_line integer; > quantity integer; > single_price

Re: [SQL] Email function using c instead of tclu

> > Hi, > I have tried without any success to get this pgmail for tclu resolved. > Does anyone have or know of the same sort of function as pgmail but > supporting c, as this is one of the installed languages I have access > to under postgresql. So for instance: > > CREATE FUNCTION sendemail(x

Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL

> > I have some instead of triggers in Oracle, some update instead of triggers > and some insert instead of triggers. I was thinking that I could maybe use > instead of rules in PostgreSQL to get the same effect. I converted the > instead of trigger in Oracle into a PostgreSQL function below: >

Re: [SQL] Ooops ! :( Recovery techniques

> > My machine just crashed in the middle of an update. > > When I restart the postgresql server the database engine appears ok and the= > n tells me that a file /data/pg_clog/0697 is not there.=20 > > I presume that these are the transaction log files.=20 > I would like to truncate the old logf

Re: [SQL] Validity check in to_date?

> > I just discovered that to_date() function does not check if supplied > date is correct, giving surprising (at least for me) results: > > fduch=# SELECT to_date('31.11.2003', 'DD.MM.'); > to_date > > 2003-12-01 > > or even > > fduch=# SELECT to_date('123.45.2003', 'DD.MM.

Re: [SQL] Problem: Postgresql not starting

> > Hi All > I am using postgresql 7.2 on Linux. It does not start when i am trying th= > is > /etc/rc.d/init.d/postgresql restart. > It give an error saying "postmaster already running". > I have done > rm -f /var/lib/pgsql/data/postmaster.pid > and > rm -f /var/run/postmaster.pid > > But it sti

Re: [SQL] how to read bytea contents by using pgsql scripts

> > Hi Christoph, > Thanks for ur reply. > Let me explain my problem..., In one of my table.. we used bytea > datatype for one field.. actaully i want to know the original stored > contents (not in byte's) from that field by using pgsql scripts. > > Thanks in advance.. > > With regar

Re: [SQL] how to read bytea contents by using pgsql scripts

> > Hi All, > Is their any way to read the bytea contents by using pgsql script. > > Thanks & regards > Vijay > > _ > Not sure if that's what you are asking for but you can do e.g. $PGSQLD/bin/psql -d -f ./query2.txt > ./quer

Re: [SQL] Compare strings which resembles each other

> > Hy, I have a table containing some rows. Each row contains a field name > which is of type String. > > For example: > in row1 string1 is "Adieu Poulet", > in row2 string2 is "Adieu a Poulet", > in row3 string3 is "Adiue Poulet", > in row4 string4 is "Adieu Pouleet", etc.. > (That is each stri

Re: [SQL] Need another way to do this, my sql much to slow...

> > Ok, I figured that part out by simply changing the way I'm doing to > query, and writing a function to handle the reply. But it will introduce > another problem. How to I pass special characters, any character, to a > function like this: > > select msg_2_env('"Ann's Free Gifts & Coupons" > <[

Re: [SQL] Need another way to do this, my sql much to slow...

> > I am attempting to write a spam management add-on for my corporate > server, I have the database and the apps written but one part is WAY to > slow to be usefull. > > The following view: > > Column | Type | Modifiers > ---+-+--- > env_send

Re: [SQL] Conversion Problem

> > Apologies as this probably isn't really for this list but... Right. > > In postgresql you can execute a statement such as: > > SELECT 1 > 2; > > And it would return 'f' > > Does anyone know if you can do this in SQL Server as I have to do a > conversion of some prewritten SQL code. No, I

Re: [SQL] How to know column constraints via system catalog tables

> > The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or > a.attnum = x.conkey[2])" which assumes there is two columns forming the primary key of a table. Perhaps, I should explain what I'm trying to achieve with this SQL. > Not exactly. Look at this CREATE TABLE fil

Re: [SQL] How to know column constraints via system catalog tables

> > Thanks for the reply but my main problem is I'm trying to find the primary column(s) > and the data type of these column in a table. pg_constraint's conkey is a int2[] > field. So if i have a table called 'films' (taken from postgreSQL doc) that has two > primary keys (composite primary key

Re: [SQL] Getting the row_count value outside of a function

> > I can get the row_count value inside of a function, > but I can´t get it outside of a function. > > How can I do this? > > Regards, > Enio > Ok, you're outside of a function, but there must be another environment which isn't nirvana. Where are you? Regards, Christoph ---

Re: [SQL] How to know column constraints via system catalog tables

> > Hi, > > I need to query each column's constraint and name of a table in > postgreSQL v7.3.4 with a single SQL query but don't know how. Would > appreciate any pointers! > > Thank you. > > Regards, > Damon > select relname,conname from pg_constraint,pg_class where pg_class.oid=conrelid ;

Re: [SQL] Dynamic Query for System functions - now()

Regards, Christoph > > Kumar, > why dont you try > > prepare fooplan2(int4) as select now() + ($1||' month')::interval; > execute fooplan2(12); > > > > O kyrios Kumar egrapse stis Nov 10, 2003 : > > > Dear Christoph Haller, > > >

Re: [SQL] Dynamic Query for System functions - now()

> > Dear Friends, > > I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to generate= > a dynamic query to fetch the next month interval. > > select now()+ interval'1 month'; -- This is working fine. > > I wanna dynamically assign the interval number. i,e --> select now()+ inter=

Re: [SQL] create type input and output function examples

> > I've seen the docs for create type and an example of the syntax to create a > type. What I haven't seen is the functions that are passed for the input and > output elements. > > CREATE TYPE box (INTERNALLENGTH = 8, > INPUT = my_procedure_1, OUTPUT = my_procedure_2); > > Now what would

Re: [SQL] Using UNION inside a cursor

> > Dear Friends, > > I am working on Postgresql 7.3.4 on RH Linux Server 7.3.=20 > > I have problem in executing the following procedure > > CREATE OR REPLACE FUNCTION list_history() > RETURNS refcursor AS > 'DECLARE > ref REFCURSOR; > BEGIN > OPEN ref FOR > (SELECT * FROM history WHE

Re: [SQL] URGENT!!! changing Column size

> > On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote: > > Dopping the whole database just for a column change? > > I guess some people have really small databases that don't take 3 days to dump > and reload. :-) > And you are on the safe side regarding indexes, views, procedures,

Re: [SQL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax

> > Please CC me, I am not subscribed. > > An imaginary SQL statement > INSERT INTO table FETCH ... FROM cursor; > looks almost the same as currently available > INSERT INTO table SELECT ...; > > I tried it because I needed to insert a row in a table > after I DELETEd a set of rows, something li

Re: [SQL] Especial delimiters caracter

> > Thanks for your replay... > I resolve the problem with sed before i post the question. > But a really want to know if i can handle all with posgre script. > > Mariano > No, you cannot. Because copy is also intended to work vice versa i.e. copy from file to table and therefore the doubled ba

Re: [SQL] Change of the datatype of a column

> > How is that I can alter the datatype of the column . > Search the archives on that topic. There should be numerous posts about how to achieve that. Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] now() in loop statement

> > Hello, > > What is wrong with this function because the mytime variable contain the > same value in each iteration: > CREATE OR REPLACE FUNCTION public.time_test() >RETURNS int4 AS > ' > DECLARE > count int4; > mytime timestamp; > BEGIN > count := 0; > mytime := n

Re: [SQL] Data Calculation

According to your scenario I did the following create table answerkey ( question_number int, answer varchar(2)); create table studentanswer ( student_id varchar(6), answer01 varchar(2), answer02 varchar(2)); insert into answerkey values(1,'A'); insert into answerkey values(2,'B'); insert into stu

Re: [SQL] plpgsql triggers question -> foo := NEW ?

> > Thanks a lot for Your reply by I wanted to create some *foo* variable ( > in declare part of pl/pgsql trigger function) and to set : > foo := NEW > > That's why I've posted the link from interactive docs - there is the > same question > > http://www.postgresql.org/docs/7.3/interactive/plpgsql-t

[Fwd: [SQL] plpgsql triggers question -> foo := NEW ?]

Christoph Haller wrote: > > > > I've searched the interactive docs and found this link: > > > > http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html > > > > There is no answer to the question below: How to set foo:= NEW or > foo:= >

Re: [SQL] selecting duplicate records

> 1. How to select duplicate records only from a single table using a select > query. > e.g. select sid,count(sid) from location group by sid having count(sid)>1; Do you get the idea? Your request is pretty unspecific, so if this is not what you're asking for, try again. Regards, Christoph ---

Re: [SQL] sub query

The reason why the first one SELECT MAX(e.d), e.host_position FROM (SELECT host_position, COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e GROUP BY e.host_position ORDER BY 1 LIMIT 1; did not work is because the query needs a ORDER BY 1 DESC LIMIT 1 like the second one. Mind the

Re: [SQL] sub query

> > > Hi I have this problem, when I try to run this query: > > > > SELECT MAX(d), host_position FROM (SELECT host_position, > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; > > > > am getting and ERROR: Attribute e.host_position must be GROUPed or > used in > > an aggregat

Re: [SQL] sub query

> Hi I have this problem, when I try to run this query: > > SELECT MAX(d), host_position FROM (SELECT host_position, > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; > > am getting and ERROR: Attribute e.host_position must be GROUPed or used in > an aggregate function. > > Pl

Re: [SQL] INSERT INTO ... SELECT

> > Coincidentally, I have a very similar case using some twists. > > The table I want to insert data is something like > > table A ( > user01 int, > user02 int, > ... > primary key (user01, user02), > CHECK ( user01 < user02 ) > ); > > And the user table is: > > tabe user_table ( > user int constr

Re: [SQL] How to speeed up the query performance

> > How do I speed up the quey performance if I've a query like this : > Does 'not in' command will affected the performance?. Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4 AFAIK. > > select > ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.rout

Re: [SQL] Order of triggers - totally lost

> The docs say in trigger-datachanges.html: > > "Changes made by query Q are visible by queries that are started after query > Q, no matter whether they are started inside Q (during the execution of Q) > or after Q is done. > > This is true for triggers as well ..." > > Trying to understand that, I

Re: [SQL] Insert a description while creating a table

> > I want to insert descriptions at the columns of my tables but without > > using the command COMMENT ON. I want to do it together with the table > > creation. Is that possible? > > > > I wanna do something like this: > > > > create table test ( > > id serial 'Descripitions about ID', > >

Re: [SQL] Insert a description while creating a table

> On Wed, Aug 13, 2003 at 02:54:59PM +0200, Christoph Haller wrote: > > \dd test shows > > > > Object descriptions > > Schema | Name | Object | Description > > +--++- > > (0 rows) > > > > Thi

Re: [SQL] sub-sel/group problem

> > select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task > from rides r, loco_dets l where r.rlid = l.lid group by rtid; > Looks like another implicit FROM clause mystery. Try ... GROUP BY r.rtid ; The same goes for the JOIN. Regards, Christoph --

Re: [SQL] length of recordset read through a cursor

> > >> After declaring a cursor, one way of obtaining the length of the > >resultset > >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a > >"MOVE nn" > >> where nn is the length of the resultset. (A negative MOVE can then be > >used > >> to allow starting to fetch records

Re: [SQL] Problem with looping on a table function result

> > I've met the following problem. > I had successfully written a function divide_into_days(timestamp, timestamp) > which returns setof (timestamp, timestamp) pairs - a list of days the > given interval is divided into. > > What I want is to use each record from resultset to pass to another > func

Re: [SQL] length of recordset read through a cursor

> > After declaring a cursor, one way of obtaining the length of the resultset > is to perform a "MOVE 0" and read the PQcmdStatus which returns a "MOVE nn" > where nn is the length of the resultset. (A negative MOVE can then be used > to allow starting to fetch records from the beginning of the re

Re: [SQL] Fwd: Bad Join moment - how is this happening?

> I have a view: > > create or replace view addenda as > select > documents.id, > documents.oid, > documents.projects_id, > documents.doc_num, > documents.description, > documents.date, > documents.createdate, > documents.moddate, >

Re: [SQL] NEW and a subselect in a rule

> > So, I met such a problem: it's impossible to use NEW in a subselect > used in a (non-select) rule. The error is: exist>. Is this a way to do that newertheless (without using of a > function, of course)? > Could we see the CREATE RULE command causing the error? Regards, Christoph -

Re: [SQL] One to many query question

> > On Wed, Jul 30, 2003 at 05:26:23PM -0400, Dmitry Tkach wrote: > > >How do I write a query to find all CDs that are NOT Rock? > > > > > What about > > select * from cd where not exists (select 1 from cd_genres where cd_id > > = cd.id and genre='Rock')? > > Thanks everyone! This did indeed work,

Re: [SQL] time precision.

> I'm using PG-7.2.4 on Solaries. > When I do: > > template1# select time(6576); > ERROR: TIME(6576) precision must be between 0 and 13 > > Where am I wrong? What's the purpose of this function? And where did you find it in the docs? I didn't see it before. Regards, Christoph

Re: [SQL] Problem using Subselect results

> I want to use the result of a subselect as condition in another one. > > table1: a,b > table2: a,c > > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table1 WHERE b=3D1) my_ab, > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > > this is just an example - i know i could cros

Re: [SQL] time delay function

> > Pseudo code: > > begin trans > select * from table1 > WAIT FOR 20 SECS > update table1 set blah = 'blah' > end transcation > > In pgplsql, Im looking for something like a function that I can use to make the process to wait for 20 secs before con tinuing to execute the next sql statment? > AFAIK

Re: [SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)

> > though this question has been asked several times before (but never really > answered), I have to give it another try. > > I have a multi-user application which synchronizes access to certain datasets > via the database itself. If a user has a project in that application open no > other user sh

Re: [SQL] columnar format

> > i would like to ask for the sql statement to the output: > > ITEM DESC Jan Feb Mar Apr ... Sep Total > xx 999 999 999 999 ... 999 9,999 > > where "Jan" column is sum of all "x" items purchased on Jan, "Feb" column as > sum of Feb purchases, and so on up to "Sep", and "

Re: [SQL] How to make a IN without a table... ?

> > Like "Tell me how much i spent between the 4th and the 7th og this month > ?" uses 4 and 7 as arguments, but need to fetch the datas in the > database. > Clear enough ? :-) Yes. > > What do you mean by table function ? Table functions are aka SetReturningFunctions. Refer to http://techdocs.pos

  1   2   >