> 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
>
> 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
>
> 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
> 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
>
> > 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
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
> 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
---
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:=
>
>
> 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
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
>
> 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
>
> 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
>
> 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
>
> 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
>
> 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,
>
> 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
>
> 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
>
> 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=
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,
> >
>
>
> 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 ;
>
> 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
---
>
> 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
>
> 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
>
> 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
>
> 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
>
> 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"
> <[
>
> 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
>
> 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
>
> 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
>
> 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
>
> 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.
>
> 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
>
> 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:
>
>
> 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
>
> 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
>
> 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
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
>
> 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
> ==
> ==
> ==
>
>
> On 09/02/2004 12:50 Christoph Haller wrote:
>
> >
> > \d Transfer_ModRange
> >Table "public.transfer_modrange"
> > Column | Type | Modifiers
> > +--+---
> > module_pointer | sma
>
> 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;
>
>
> 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");
>
>
> 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.
>
>
> 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
>
>
> 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 =
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
>
> 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
>
> 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.
>
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
>
> 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
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)
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
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
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
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
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
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
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
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
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
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.
> >
&
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
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 (
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
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 "
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
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
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 ...
>
"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
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
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
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();
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
>
"[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
; 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.
> >
>
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:
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
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
> 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
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_
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
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
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 ...
>
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
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
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
101 - 185 of 185 matches
Mail list logo
