Hello.
I have a table
create table foo (
a serial,
b int,
c int,
more fields ...);
and now I wish to remove for each combination of b and c, all the
rows except the one with the highest value of a.
For example
a b c other fields
=
1 5 5 .
2 5 5
3 2 3
4 2 2
Thank you all for your replies.
2010/10/8 Alban Hertroys :
> On 8 Oct 2010, at 8:59, A B wrote:
>
>> Hello.
>>
>> I have a table
>>
>> create table foo (
>> a serial,
>> b int,
>> c int,
>> more fields ...);
>>
>> an
Hello!
I have a question about catching exceptions.
If I write a plpgsql function like this
begin
do stuff;
exception
when X then
when Y then
...
end;
If the "do stuff" part can result in two different unique_violation
exception (having two unique constraints), how can I detect w
Hello.
How do you create an index for only some of the rows in a table? I
read in the docs:
"The expression used in the WHERE clause can refer only to columns of
the underlying table, but it can use all columns, not just the ones
being indexed. Presently, subqueries and aggregate expressions are
Hello.
I'm probably doing some very basic error here, but I get
ERROR: record "new" is not assigned yet
The tuple structure of a not-yet-assigned record is indeterminate.
when I try this small example
create table foo(x int);
create or replace function trigger_foo() returns trigger language
Thanks for the suggestion, but
CREATE TRIGGER trigger_foo BEFORE INSERT ON foo for each row EXECUTE
PROCEDURE trigger_foo();
gives me the same error.
2011/2/10 Vick Khera :
> On Thu, Feb 10, 2011 at 9:29 AM, A B wrote:
>> Can someone help me spot the error? :-)
>>
>
>
I'm very embarresed now.
There were another trigger that caused a problem. Now it works.
Thank you all for helping! :-)
2011/2/10 Adrian Klaver :
> On Thursday, February 10, 2011 6:29:58 am A B wrote:
>> Hello.
>>
>> I'm probably doing some very basic error here,
Hi.
If I have table A (x integer primary key);
and table B (y integer references A on delete cascade );
and that a new item (x=70) gets inserted into A and a lot of items go into B
that references the new item in A.
Now, if I really have to do:
delete from A where x=5;
update A set x=5 where x=7
Hi. newbie question, but what will happen if I do
begin work;
select ...
insert ...
and so on...
commit
and somewhere a query fails. will I get an automatic rollback? If not, is
there a way to get that behaviour?
I'm using php to make all these calls and they have all to be succesfull or
no one o
Hello.
I think I need som help on this function I write in plpgsql
I want to return
CREATE OR REPLACE FUNCTION gList(tid_ TIMESTAMP) RETURNS AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT DISTINCT custid,action,nr FROM ...
IF rec.action = ...
END I
ery , like this
CREATE FUNCTION foo() RETURNS AS
BEGIN
RETURN QUERY SELECT a,b,c,d,... FROM T1,T2,... WHERE ;
END;
but what do I write instead of
2) when I select stuff, iterate over the result before returning it
CREATE FUNCTION foo() RETURNS AS
BEGIN
FOR re
> What exactly about the documentation isn't clear?
I would have liked a few more examples... but that is perhaps just me.
> Like the documentation says: SETOF sometype.
Ah, so I just create my own type with "CREATE TYPE ..." and use that
type in the function.
--
Sent via pgsql-general mailing l
> How you generate the results is up to you. when you have them you
> either use RETURN NEXT or RETURN QUERY to return them to the caller.
Now I get the reply
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "actionlist" line 11 at return next
an
Great! :D That did the trick!
Thank you so very much!
2008/5/16 Richard Huxton <[EMAIL PROTECTED]>:
> A B wrote:
>>>
>>> How you generate the results is up to you. when you have them you
>>> either use RETURN NEXT or RETURN QUERY to return them to the caller.
&
I get a lot of
Error server closed the connection unexpectedly This probably means
the server terminated abnormally before or while processing the
request.
and I think I need some help finding out what is the problem. Any
suggestions on where to start? I think I have maximum logging on
(debug5) b
I have a query like this in a plpgsql function:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''') INTO newid'
and I get the response:
ERROR: syntax error at or near "INTO"
LINE 1: ...','2008','4',NULL) RETURNING currval('id_seq') INTO n
I have not found any core dumps. The server seems not to stop
completely but continue to run.
2008/5/30 Zdenek Kotala <[EMAIL PROTECTED]>:
> Do you have any core dump? Stack trace should help.
>
> Zdenek
>
> A B napsal(a):
>>
>> I get a lot of
>
> EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
> ('||vals||') RETURNING currval('''||seqname||''')' INTO newid
>
> Note where last quote goes.
That was exactly what I wanted to do!
SELECT 'Thank you' FROM heart;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
Hi.
I run a function
CREATE OR REPLACE FUNCTION addRating(tbl_ INTEGER,value_ INTEGER)
RETURNS void AS $$
DECLARE
tablename TEXT;
fieldname TEXT;
BEGIN
tablename:='Rating_'||tbl_;
fieldname:='val';
EXECUTE 'UPDATE '||tablename||' SET '||fieldname||'='||value
> I think you'd be well advised to rethink your table layout so you don't
> need so much dynamic SQL. The above is going to suck on both
> performance and readability grounds, and it doesn't look like it's
> accomplishing anything you couldn't do by combining all the Rating
> tables into one table
Thanks for the suggestion on GET DIAGNOSTICS.
But concerning EXECUTE, if I do
BEGIN
EXECUTE QueryA
EXCEPTION WHEN OTHERS THEN
QueryB
END;
will it execute QueryB if QueryA fails?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscripti
Hello.
I suspect that in a plpgsql function
DECLARE
c2 REAL;
cadiv REAL;
works but
c2 REAL;
c2div REAL;
doesn't.
Is this true, and if so, what are the rules for the names in the
function? I use 8.1.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to you
> it works in my 8.1
>
> postgres=# CREATE LANGUAGE plpgsql;
> CREATE LANGUAGE
> postgres=# create or replace function foo(a int) returns void as
> $$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language
> plpgsql;
> CREATE FUNCTION
> postgres=# select foo(10);
&
By changing c2,c3,etc. to something else xc2,xc3, etc. it worked!
So was the problem that I refered to the same names in the SELECT statement?
2008/6/30 A B <[EMAIL PROTECTED]>:
> Then my assumption was wrong.
> Here is the entire function and it fails with the names
> c2,c2div,c
> Vyacheslav Kalinin writes:
>
>> $conn = pg_pconnect("dbname=foo");
>
> Please reconsider and use plain pg_connect().
Would you like to elaborate on that? Why connect and not pconnect?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
Hi.
I have a little problem (and a suggestion for a solution) that I
wondered if anyone would care to comment on.
I have a standard table filled with customers (with a unique customer
id, names etc.) and for each customer I need to store some integer
values. The problem is that the number of integ
> The way you described the problem the EAV solution sounds like the best
> match--not sure if I'd use your synthetic keys though, they will save a
> bit of space on disk but queries will be much more complicated to write.
I guess I'll have to build procedures for all the complicated queries
when e
2009/6/16 Greg Stark
> On Tue, Jun 16, 2009 at 12:21 PM, A B wrote:
>
> I don't think think it's fair to call this EAV actually. It sounds
> like the integers are a collection of things which represent the same
> thing. Ie, they're all bank balances or all distance
2009/6/16 A B :
>
> 2009/6/16 Greg Stark
>>
>> I don't think think it's fair to call this EAV actually. It sounds
>> like the integers are a collection of things which represent the same
>> thing. Ie, they're all bank balances or all distances driven
> Your problem is currently sounding very much like an exam question; you
> seem to be arbitrarily making decisions without showing any real data.
> When you deal with real problems in the real world you're normally
> making compromises when you model things and hence the decisions
> wouldn't be as
Hi.
How can I abort a query that I see is listed in
select * from pg_stat_activity;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi there!
I have a small problem, in a database I need to export parts of a
database table to another server, and I could easily accomplish ( I
hope) that by creating a view and select * from the view and send it
over to the other server or use triggers to record what rows are
inserted and delete
Hi there!
select * from pg_stat_activity;
shows me a
select my_function()
query that has been running for too long.
How do I kill it?
kill -9 of the procpid seems to kill the entire server process. So
I'm not really comfortable with that.
The query was started by a webscript that was close
> select pg_cancel_backend(pid);
> will kill a running query.
Thanks.
> Sometimes cancel_backend fails because there's no point where the code
> checks for a cancel.
What would such a point in the code look like?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make cha
> I generate e-mail messages to a database table and then with a CronJob I
> sent the e-mails.
>
> My doubt is... The CronJob runs every 10 minutes, but If I have 100.000
> e-mails to send the script will not be able to send all the 100.000 e-mails
> in 10 minutes.
>
> How can I deal with this prob
> [quote]
> The other way is to let the cron job spawn new processes (up to a
> limited number of child proceses) as long as there are mails to send.
> These child processes runs as long as there are mails to send, then
> they die. The cron job will then mostly do process controll/start new
> proce
Hello.
Is there any doc or wiki page that describes what filesystems that are
recomended to use (OS is Linux) for PostgreSQL?
Information about filesystems options/mount options and how well
they work with different RAID setups is also of interest.
--
Sent via pgsql-general mailing list (pgs
Hello.
I use swedish locale
show lc_numeric;
lc_numeric
-
sv_SE.UTF-8
and I get a . (dot) in all floating-point numbers.
This makes me wonder, when can I see the effects of the locale? That is, I get
select 355/113.0 as pie;
pie
3,1415929203539823
Hello.
If I have a table like this
create table fleet ( ship_id integer, location point);
and fill it with a lot of ships and their locations and then want to
create an index on this to speed up operations on finding ships within
a certain region (let's say its a rectangular region), how do I
gets used?
(I've run the queries a thousand times to make sure the total runtime
is consistent, and it is)
2010/9/23 Jeff Davis :
> On Thu, 2010-09-23 at 12:45 +0200, A B wrote:
>> Hello.
>>
>> If I have a table like this
>>
>> create table fleet ( ship
2010/9/25 Tom Lane :
> Jeff Davis writes:
>> There's no reason that there couldn't be a point <@ box operator in the
>> opclass, but nobody really uses these geometric types that come with
>> core postgres (at least, not that I can tell).
>
> Actually, as of 9.0 there is a point_ops opclass for GI
Sorry, Gmail made med confused, my biggest "thank you" was to Richard
Huxton, who showed me code that worked.
2010/9/26 A B :
> 2010/9/25 Tom Lane :
>> Jeff Davis writes:
>>> There's no reason that there couldn't be a point <@ box operator in the
Thank you both! :-)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
But there not any problem with returning values with code like this
DECLARE
retval RECORD;
retval.c2 :=
RETRUN NEXT retval;
if c2 is a field on some table?
2008/6/30 Karsten Hilbert <[EMAIL PROTECTED]>:
> On Mon, Jun 30, 2008 at 12:44:31PM +0200, A B wrote:
>
>>
> But there not any problem with returning values with code like this
>
> DECLARE
> retval RECORD;
>
> retval.c2 :=
> RETRUN NEXT retval;
>
> if c2 is a field on some table?
Oh, that seems also to be problematic.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To ma
In my function I have (kept the important part)
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$
DECLARE
retval RECORD;
BEGIN
some loop
retval.jd := tmp.id;
retval.d2 := _c2;
retval.d3 := _c3;
RETURN NEXT retval;
end loop
retu
> Sure, declare your result like my example:
>
> test=# create or replace function ab() returns setof record as $$declare r
> record; begin select into r 1,2;return next r;end;$$language plpgsql;
Unfortunatly I have not the luxury of creating the record with a
single SELECT command.
Isn't there a
I did read the select line also, and
select * from foo() as (a integer, b integer, c integer);
gives me unfortunatly the error
ERROR: record "retval" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
So you are telling me this is an error that is cause
work?
I think I need some help with the syntax for the expression above...
I will also try to create a datatype of my own and see if that works
as a last way out.
2008/6/30 A. Kretschmer <[EMAIL PROTECTED]>:
> am Mon, dem 30.06.2008, um 14:25:30 +0200 mailte A B folgendes:
>> I did
As a final note, it worked fine with a custom data type! :-)
No problem returning values (yet)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
What should I replace the command
INSERT INTO table (name) VALUES (value) RETURNING currval('my_id_seq')
into my_var;
with if I have to use version 8.1?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
I have a table with rows like this
A 1
A 1
B 3
B 3
C 44
C 44
and so on.
and I want it to be
A 1
B 3
C 44
so how can I remove the all the duplicate lines but one?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
> There is probably a more elegant way of doing it, but a simple way of doing
> it ( depending on the size of the table ) could be:
>
> begin;
>
> insert into foo select distinct * from orig_table;
> delete from orig_table;
> insert into orig_table select * from foo;
>
> commit;
Just to make it c
Hi. This is just some thoughts about database design.
I often find my self having to do this
update table_XY set x=..., y=... where x=... AND y=;
if not found then
insert into table_XY (x,y) values (...,...);
end if;
Is this normal or are there something else I could do so I don't have
t
Sorry if this is a double posting, I think the previous message was lost.
I have two tables
T (
id int primary key,
a int,
b int
)
T2 (
id int references T,
c int
);
and I wish to get 20 lines from T like this
select id,a,b from T where id not in (select id from T2 where c=5) limit 20;
but
I guess I can't do like this
SELECT id,a,b FROM T WHERE id NOT IN (SELECT id FROM T2 WHERE
c=5) LIMIT 20;
I only want 20 rows.
How do I do it?
I have table
T (
id ,a,b integer, primary key(id) );
T2 (id
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make ch
Yes it obviously does! I must admit that (as usual) I found the error
shortly after sending the post.
The problem was that instead of
select id,a,b from T where id not in (select id from T2 where c=5)
I wrote
select id,a,b from T where id not in (select YYY from T2 where c=5)
where YYY was a
Hi.
I would like to compare two columns a and b and find all cases where
a is a part of b, like this
select * from mytable where a ilike b;
but that will not give me a row in the case when a = 'foo' and b='FOOTBALL'
and I want that to be a match.
So how do I rewrite my expression? I can't find
So I have created a role
create role my_role with NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN
ENCRYPTED PASSWORD 'secret';
Now I wish to grant only select for this role on some tables.
So I do
revoke all privileges on table X from my_role;
and i do this for all my tables (X is table name) ?
Hello.
I was just asked by a mysql-user how do you do
insert . on duplicate key update
(or however they have it in mysql) in postgresql, if you are going to
run commands from the command line?
My solution up till now has been a function with the
BEGIN
insert
EXCEPTION WHEN
> Here is the appropriate documentation link, where they have an example:
> http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> The primary difference is that they use a loop, which is more robust. In
> theory, if you delete the record between when t
Assuming you have a table where some rows have the same values in all
columnes, how do you find these rows?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'd like to get my postgresql logging sent to the file
/var/log/postgresql.log, and have that file rotated to
postgresql.log.0.gz etc.
my postgresql.conf says
log_destination = 'stderr,syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
but the messages goes into /var/log/messages.
Does
Hello there!
I have a tablefoo( userid integer, data integer); with the
constraint unique(userid,data)
Now I wish to select one userid and calculate the differences between
the data -values (when they are sorted) .
For example
if the table contains:
4, 100
5, 200
5, 210
5, 231
I want the
> With 8.4's analytic capabilities you can do this:
> select * from (
> select userid, data - lag(data) over (partition by userid order by data)
> diff
> from foo) q
> where diff is not null;
Thank you! That worked perfectly!
--
Sent via pgsql-general mailing list (pgsql-general@postgres
Hi.
I just logged into a system and found this in the log when trying to
start postgres.
LOG: database system shutdown was interrupted; last known up at
2009-12-07 06:27:33 CET
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 15/B320AF68
LOG:
Hello there.
I read http://www.postgresql.org/docs/current/static/sql-altertable.html
and find it interesting that
" Adding a column with a non-null default or changing the type of an
existing column will require the entire table to be rewritten. This
might take a significant amount of time for a
Hello.
Doesn't this work in plpgsql functions?
EXECUTE 'UPDATE mytable set ... ...'
IF FOUND THEN
do stuff
END IF;
It seems it always evaluate to false in the if statement, Isn't found
used to see if an update has modified rows?
Are there any alternatives to selecting the row and see
Hello.
It's time to get new hardware for a server that will run both
PostgreSQL and Apache.
The workload will be similar to that of your standard "PHP forum"
(most selects and logging of stuff that has been read)
The modell I'm looking at right now is
2x Xeon E5520 2,26 GHz 8 MB (8 cores in tot
Hi there!
I'm trying to comprehend the current status of filesystem settings and
PostgreSQL settings.
If I run on a machine (using Linux and ext3) with no battery backed
raid controller then I should use fsync=on and disable the write cache
on my harddrives to avoid corruption.
If I had a contr
> Don't put SAS drives on a 3ware controller. They say that works now, but
> they haven't really gotten it right yet--their controllers are still only
> good with SATA drives.
How bad will it be with SAS drives? Is there so little performance
gain witn 3ware+SAS?
Scott Marlowe stated in earlier
Hello!
I have a table (think of it as a table of log messages)
time | message
---
1 | a
2 | b
3 | b
4 | b
5 | a
the three 'b' are the same message, so I would like to write a query
that would give me a result that is similar to what the unix
x | message | counts
> -+-+-+
> 1 | 1 | a | 1
> 2 | 3 | b | 2
> 4 | 4 | c | 1
> 5 | 5 | a | 1
> 6 | 7 | c | 2
> 8 | 10 | a | 3
> (6 rows)
>
> --end
&g
Hi there.
I'm stuck with a machine with so very slow I/O one starts to remember
the good-old-days when we had 3,5" floppies.
So I can't do anything with the hardware, but what settings in the
config should I use to make handle the extremly slow I/O?
Any suggestions?
--
Sent via pgsql-general ma
Setting
shared_buffers = 28MB
makes the startup script say
/etc/init.d/postgresql-8.3 restart
* Service postgresql-8.3 starting
* Starting PostgreSQL ...
waiting for server to
start...could
not start server
Ah, it seems to be a problem with the SHMMAX variable.
I think I've solved it now.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
>From the docs: http://www.postgresql.org/docs/8.3/interactive/sql-update.html
"According to the standard, the column-list syntax should allow a list
of columns to be assigned from a single row-valued expression, such as
a sub-select:
UPDATE accounts SET (contact_last_name, contact_first_name) =
Hi.
I just discovered three "postmaster" processes running and really
eating all the cpu.
I'm not sure why?
So, what steps do I take for finiding the queries that is causing this?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://ww
blocking the query, seriously!
Still interested in findingout how to detect what is going on.
There were other processes, could it be a lock? I have not specified
any special transaction or so. I was calling from a PHP script (I
think).
2009/1/30 A B :
> Hi.
> I just discovered three "
2009/1/30 Laurent Wandrebeck :
> 2009/1/30 A B :
>> Hi.
> Hello,
>> I just discovered three "postmaster" processes running and really
>> eating all the cpu.
>> I'm not sure why?
>> So, what steps do I take for finiding the queries that is causin
Hi.
I have a table foo(id serial primary key, b int); and I want an insert function
create or replace function insert_to_foo(bvalue integer) returns integer as
declare
newindex integer;
begin
... insert into foo (a,b) values (default,bvalue) returning id
< THIS LINE
--
Hi.
>From the manual I read that timestamps are stored as double but they
can also be stored as 8 byte integers. I understand the precision
problem with floats and the limited range of the integers and I feel
confident that I should not worry about the Year 294276 or Year
5874897 problems (highest
Hi there!
If I have a database that have a size of a few GB and run this on a
machine with 48 GB of ram, What parameters should I set to make
Postgres use all the memory that is available? For a not so very
technical person it is a little cryptic to find out.
Thanks.
--
Sent via pgsql-general
Hi,
In the next project I'm going to have a number of colums in my tables,
but I don't know how many, they change. They all use integers as
datatype though.. One day, I get 2 new columns, a week later I loose
one column, and so on in a random pattern.
I will most likely have a few million rows of
> Well, not as such. Except that deleting a column doesn't really delete
> it, it hides it, so it never really goes away. So the number of
> "columns" in your table will only go up and eventually you're going to
> reach the point (around 1600 IIRC, probably earlier) where it will
> simply stop work
2009/3/30 David Fetter :
> On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote:
>> Hi,
>> In the next project I'm going to have a number of colums in my tables,
>> but I don't know how many, they change.
>
> Stop right there. You need to get some sanity in
86 matches
Mail list logo
