Brent Wood wrote:
I am looking to convince a business which does not use schemas, but
does use separate databases to move to Postgres & having to shift
from this paradigm is an issue for them. They are perfectly entitled
to require such an approach, if we want to increase the user base of
Postgr
> I have a table that has 3 date columns :
>
> create table xyz (
> xyz_id integer,
> date1 timestamp,
> date2 timestamp,
> date3 timestamp
> )
>
>
> I want to select in a query the xyz_id and the max date column for
> each row
> something like :
> create table temp2 as select xyz_id (max
Hi List;
I have a table that has 3 date columns :
create table xyz (
xyz_id integer,
date1 timestamp,
date2 timestamp,
date3 timestamp
)
I want to select in a query the xyz_id and the max date column for
each row
something like :
create table temp2 as select xyz_id (max date?) where .
Ah I missed this email. I agree with Teodor that this is not the best
way to implement this functionality. At the time I was in a bit of hurry
to have something better than the default one and just hacked this. And
if we want to have this functionality across languages and parsers it
will be better
I have mixed feelings,
I agree that pretty much equivalent functionality CAN be delivered using
schemas, but some RDBMS's do not have this restriction. Business cases &
preferences do not necessarily follow database design preferences or
capabilities, so irrespective of whether a schema approac
Thanks Richard,
I went through dblink and tried it . But I am not able to pass variables to
sql stmt of dblink_exec function . Basically If I hardcode the values for
updating a table for the sql statement in different database it is working
fine. But What I wanted to do is get the info from ex
Alex Adriaanse <[EMAIL PROTECTED]> writes:
> Unfortunately, we do not have any core dumps from those. Is there
> anything else I can provide to make tracing this easier? Could we use
> the addresses mentioned in the segfault messages for anything useful?
Hmm, you could try attaching to a runni
x asasaxax wrote:
Hi,
I have the following tablecreate table product(cod serial, user_cod
bigint, constraint product_fk Foreign Key(user_cod) references user(cod),
constraint product_pk Primary Key(cod, user_cod));
What i want to happend is that:
user_codcod
1
Thanks everyone for the suggestions so far.
Tom Lane wrote:
The segfaults (sig11s) are a bit disturbing too --- what that probably
indicates is someplace using malloc() and failing to test for failure,
neither of which is a good thing. Did you by any chance get core dumps
from those? A stack t
Hi,
I have the following tablecreate table product(cod serial, user_cod
bigint, constraint product_fk Foreign Key(user_cod) references user(cod),
constraint product_pk Primary Key(cod, user_cod));
What i want to happend is that:
user_codcod
1 1
1
I've 4 tables
create table items(
item_id serial primary key,
attributes...
);
create table item_attributes(
item_id int references items (item_id)
attributes...
);
create table baskets(
basket_id serial primary key,
...other stuff
);
create table basket_items(
item_id int referenc
The only way I knew to display the source code of a function was with
\df+, which produces very hard-to-read output, because it returns
several columns about the function, but usually all I want is the source.
So I "created" a function (i.e., shameless copied the psql interpreter)
to display j
Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> My guess is that the total allocated VM is fairly close to the limit of
> your configuration and that a slightly higher than normal load and a
> background autovacuum took it over the edge. My guess is that if you up
> the swap to 4GB or perhaps
Hello,
Does anybody know of the performance impact of using uuid over int4?
Specifically, I am assuming that it will be about 4 times slower since
it is 128 bits. Is this correct?
Benjamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subsc
Chris Mayfield <[EMAIL PROTECTED]> writes:
> See attached -- I've simplified my actual database quite a bit, but this
> example shows the same results.
OK, here's the problem:
> CREATE VIEW v AS
> SELECT id, COALESCE(opt, 0) AS opt FROM b;
You're using this inside the nullable side of an oute
On Fri, Mar 28, 2008 at 01:38:57PM -0500, Alex Adriaanse wrote:
> First some background information:
>
> Software (at the time of the memory errors): CentOS 4.5 (x86_64) running
> its 2.6.9-55.ELsmp Linux kernel, PostgreSQL 8.1.9 (from RPMs provided on
> the PostgreSQL web site: postgresql-8.1.9
On Fri, Mar 28, 2008 at 05:29:06PM -0400, Terry Lee Tucker wrote:
> Thanks Sam. No, that is not what I tried. I had tried:
> UPDATE bill SET notes = 'blah, blah, yeaE'\n'more stuff.'
> It didn't dawn on me that the E went in front of the whole string!
it's always easy when you know how!
> Thanks
On Friday 28 March 2008 17:21, Sam Mason wrote:
> On Fri, Mar 28, 2008 at 05:06:10PM -0400, Terry Lee Tucker wrote:
> > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
> >
> > The problem is a line like 'UPDATE bill SET notes = 'blah, blah,
> > yea\nmore stuff';
> >
> > How to I esc
On Fri, Mar 28, 2008 at 05:06:10PM -0400, Terry Lee Tucker wrote:
> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
>
> The problem is a line like 'UPDATE bill SET notes = 'blah, blah, yea\nmore
> stuff';
>
> How to I escape the newline embeded in the string? I've tried the advic
Scott Marlowe wrote:
Just because you can set max_connections to 2000 doesn't mean it's a
good idea. If your client needs 1000 persistent connections, then put
a connection pooler between your app (I'm guessing php since it
operates this way) and the database.
Running 1000 connections is a LOT,
Greetings:
We are moving our application from 7.4.14 to 8.3.1. One giant step for
mankind...
Anyay, I have several triggers that update notes fields in certain tables and
loading the trigger function is giving me the following error:
psql:bill/bill_preupd_func.plsql:83: WARNING: nonstandard us
On Fri, Mar 28, 2008 at 12:38 PM, Alex Adriaanse
<[EMAIL PROTECTED]> wrote:
> I have a client that experienced several Out Of Memory errors a few
> weeks ago (March 10 & 11), and I'd like to figure out the cause. In the
> logs it's showing that they were getting out of memory errors for about
>
On Sat, Mar 29, 2008 at 04:05:15AM +0900, Craig Ringer wrote:
> Sam Mason wrote:
> >>ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check
> >>CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]';
> >
> >Just out of interest, what's the lower() function call doing?
>
> Abs
josep porres escreveu:
maybe this?
select value, max(id) as id, max(order_field) as order_field
from mytable
group by value
order by 3
Wrong. For the op data you will obtain tuples not in original relation.
bdteste=# SELECT * FROM foo;
id | value | order_field
+---+-
JackpipE <[EMAIL PROTECTED]> writes:
> I'm selecting tables from my db using query:
> SELECT
> pg_stat_user_tables.relname
> FROM
> pg_stat_user_tables
> WHERE
> (pg_stat_user_tables.relname LIKE 'name_hosp_%')
> The problem I have is that the table names returned by this query are
> differe
Sam Mason wrote:
ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check
CHECK lower(trim(both ' ' from email)) LIKE '[EMAIL PROTECTED]';
Just out of interest, what's the lower() function call doing?
Absolutely nothing. That's what I get for reading my mail at
stupid-o-clo
I have a client that experienced several Out Of Memory errors a few
weeks ago (March 10 & 11), and I'd like to figure out the cause. In the
logs it's showing that they were getting out of memory errors for about
0.5-1 hour, after which one of the processes would crash and take the
whole databa
On Fri, Mar 28, 2008 at 2:19 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> reading the archives, you wrote:
> "Because it isn't a slice expression --- you used colon nowhere, so the
> result type is going to be text not text[]. (Remember that the parser
> must determine the expression's resu
Teemu Juntunen, e-ngine wrote:
Hi Tomasz,
with receipt rows I can't see any other option with key as running number.
First I tried to use serial, but it didn't start from zero again when the
foreign key (receipt number) changes. It just continues the serial despite
of the foreign key.
Then I
On Thu, Mar 27, 2008 at 8:58 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> If you want a sub-array you need to use the slice notation, eg
> tdr_tags[2:2][1:2]
The slice approach is not a general solution...in fact there seems to
be no way to convert an array of N dimensions to N-1 dimensions except
Ben wrote:
> create table attrs (id serial primary key, name text);
> create table obj (id serial primary key, name text);
> create table att (oid int references obj.id, aid int references attrs.id,
> value_int int, value_float float, value_text text, value_bool bool,
> value_date date);
I th
Hello
it's on czech site, but in english lang
http://www.pgsql.cz/index.php/Introduction_to_PostgreSQL_SQL
http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29
http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks
Regards
Pavel
Hi Tomasz,
with receipt rows I can't see any other option with key as running number.
First I tried to use serial, but it didn't start from zero again when the
foreign key (receipt number) changes. It just continues the serial despite
of the foreign key.
Then I decided to do my own serial which
See attached -- I've simplified my actual database quite a bit, but this
example shows the same results.
Thanks,
--Chris
--
-- Why does the optimizer insist on sorting a clustered table?
--
-- NOTE: This script requires 540 MB of disk space and about
-- 12 minutes to run (on my good old Sun-Bla
Richard Broersma wrote:
On Thu, Mar 27, 2008 at 11:33 PM, Michael Fuhr <[EMAIL PROTECTED]> wrote:
Or a few minutes with shapefiles and PostGIS, using the latter's
spatial functions to identify geometries that touch. Below are the
results of such an operation; I haven't verified the entire li
On 2008-03-28 13:27, Teemu Juntunen wrote:
> I am developing an ERP to customer and I have made few tables using a
> row number as part of the key. When deleting a line from such a
> table, I have made an after delete trigger, which fixes the row
> numbers with following command:
> UPDATE orderro
Thanks Tom & ludwig, I understand now.
Glyn Astill
- Original Message
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Friday, 28 March, 2008 3:24:34 PM
Subject: Re: [GENERAL] Users, groups and inheritance questions
Hello Glyn,
it's confusing, but Y
I'm selecting tables from my db using query:
SELECT
pg_stat_user_tables.relname
FROM
pg_stat_user_tables
WHERE
(pg_stat_user_tables.relname LIKE 'name_hosp_%')
The problem I have is that the table names returned by this query are
different from the ones in my db. Not all of them but 10%-20%.
On Thu, Mar 27, 2008 at 11:33 PM, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Thu, Mar 27, 2008 at 06:00:57PM -0400, Colin Wetherbee wrote:
> > brian wrote:
> Or a few minutes with shapefiles and PostGIS, using the latter's
> spatial functions to identify geometries that touch. Below are the
>
I'm working on a project which requires me to keep track of objects,
each of which can have an arbitrary number of attributes. Although
there will be many attributes that an object can have, the data types
of those attributes won't be all that varried (int, float, text,
boolean, date, etc.)
Yes, it works fine. Never came to my mind to simply use aggregate functions
on fields which I do not want in the group clause.
Is it common practice to do so in such cases? It seems odd somehow.
_
Von: josep porres [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 28. März 2008 14:15
A
ashish-21 wrote:
>
> ajcity wrote:
>> If I wanted to use that with a command like "COPY (SELECT * FROM country
>> WHERE country_name LIKE 'A%') TO ''; " do I specify the file
>> location for the remote machine as the or do I specify the
>> location for local machine?
>> And what if the psql c
carty mc wrote:
I have a question regarding postgres Trigger. We have two
applications which connect to two different databases (Both are
postgres). Is it possible to create a trigger (row based) in one
database say A, which can access another database say B and updates a
table there.
Sure - ch
Greetings from Finland to everyone!
I joined the list to hit you with a question ;)
I am developing an ERP to customer and I have made few tables using a row
number as part of the key. Frex. Order rows table has a key of order number
and row number like Receipt rows table has a key of Rece
Hello,
I have a trouble with PG and can't find out why it terminates and goes to
recovery mode :(
LOG:
2008-03-28 13:29:39 LOG: server process (PID 6852) exited with exit code 3
2008-03-28 13:29:39 LOG: terminating any other active server processes
...
2008-03-26 17:29:39 FATAL: the database
I have a question regarding postgres Trigger.
We have two applications which connect to two different databases (Both are
postgres).
Is it possible to create a trigger (row based) in one database say A, which
can access another database say B and updates a table there.
In brief, Is it
On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar
<[EMAIL PROTECTED]> wrote:
> I am fairly new to Postgres. However, I have to say that I agree with
> Barry's comments.
The real problem here is that you are not using the db properly. You
should have one db with all these data in it in differ
if you create its in a template1
regards...
El jue, 27-03-2008 a las 23:10 -0400, David T escribió:
> Hi all,
>
> I am using Ubuntu 7.10 with PostgreSQL 8.2. I have just finished creating
> two C functions and have successfully loaded them using "CREATE OR REPLACE
> FUNCTION" ... This wa
Hello Glyn,it's confusing, but You didn't read the manual very carefully! Short excerpt:The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges fordatabase objects and role memberships). It does not apply to the special role attributes set by CREATEROLE and ALT
On Fri, Mar 28, 2008 at 3:41 PM, Tomasz Ostrowski
<[EMAIL PROTECTED]> wrote:
> On 2008-03-28 02:00, Andrej Ricnik-Bay wrote:
> > On 28/03/2008, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> >> Agree, except I would prefer "pg" instead of "pgc".
> >
>
> > And it's been taken for about 35 years by
Glyn Astill <[EMAIL PROTECTED]> writes:
> I thought that if user 'test' was in group 'admins' and I specified INHERIT
> then it'd inherit those permissions?
No, inheritance of permissions only works for GRANT-able permissions;
the special role properties like CREATEDB are outside that scope.
I
Michael Fuhr wrote:
On Thu, Mar 27, 2008 at 06:00:57PM -0400, Colin Wetherbee wrote:
brian wrote:
I'd like to add a table, state_neighbours, which joins each state with all
of its neighbour states. Does anyone know where I can find such a list?
I'm not interested in "nearest neighbour", just
I have a question regarding postgres Trigger.
We have two applications which connect to two different databases (Both are
postgres).
Is it possible to create a trigger (row based) in one database say A, which
can access another database say B and updates a table there.
In brief, Is it
Chris Mayfield <[EMAIL PROTECTED]> writes:
> [ planner finds better plan with a forced ORDER BY ]
That shouldn't happen. Can you show the details of your case?
It may be something specific to the particular view definition...
regards, tom lane
--
Sent via pgsql-general
Hi chaps,
Apologies in advance if there's something in the docs I've missed here, but I
have had a good look around and I can't find a good explanation anywhere.
I'm looking at setting up group roles to manage our users, but I can't quite
get my head around how the inheritance is supposed to wo
On 2008-03-28 02:00, Andrej Ricnik-Bay wrote:
> On 28/03/2008, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
>> Agree, except I would prefer "pg" instead of "pgc".
>
> And it's been taken for about 35 years by a Unix command called "page".
> From its man-page.
>pg - browse pagewise through te
Adam Rich wrote:
> > > Oh, then there should have been some options in the survey along the
> > > lines of "things are fine how they are."
> >
> > Oh, a bit of answer-forcing wasn't beneath him.
>
>
> Ummm... Isn't that what Option A is about ?
>
>
> 1) What type of names do you prefer?
> --
> Hi all,
> I am trying to backup a large table with about 6 million rows. I want
> to
> export the data from the table and be able to import it into another
> table
> on a different database server (from pgsql 8.1 to 8.2). I need to
> export the
> data through SQL query 'cause I want to do a gr
On Fri, 28 Mar 2008, Sam Mason <[EMAIL PROTECTED]> writes:
> On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
>> The result in this case should be:
>>
>> id value order_field
>> 3 10 1
>> 5 12 2
>> 45 8
Yet another lame solution:
test=# SELECT max(id) AS
Teemu Juntunen, e-ngine wrote:
Greetings from Finland to everyone!
On behalf of everyone, hello Finland.
I joined the list to hit you with a question ;)
That's what it's there for.
I am developing an ERP to customer and I have made few tables using a row
number as part of the key. Frex.
maybe this?
select value, max(id) as id, max(order_field) as order_field
from mytable
group by value
order by 3
2008/3/28, Stanislav Raskin <[EMAIL PROTECTED]>:
>
> Hello everybody,
>
>
>
> I have a table like this one:
>
>
>
> id value order_field
>
> 1 103
>
> 2
On Fri, 28 Mar 2008, Sam Mason <[EMAIL PROTECTED]> writes:
> On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
>> The result in this case should be:
>>
>> id value order_field
>> 3 10 1
>> 5 12 2
>> 45 8
>
> SELECT id, value
> FROM (
> SELECT DISTINC
On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
> id value order_field
> 1 10 3
> 2 12 4
> 3 10 1
> 45 8
> 5 12 2
>
> Hence selecting rows with distinct values, but primarily ordered by
> order_field, instead of value, which is requires by DI
Greetings from Finland to everyone!
I joined the list to hit you with a question ;)
I am developing an ERP to customer and I have made few tables using a row
number as part of the key. Frex. Order rows table has a key of order number
and row number like Receipt rows table has a key of Rece
Hello everybody,
I have a table like this one:
id value order_field
1 103
2 124
3 101
4 5 8
5 122
What I want to do, is to do something like
SLECT DISTINCT ON (my_table.value)
my_
Greg Sabino Mullane napsal(a):
Nobody want to rename psql. Personaly, I dislike current command
names for long long time. Many times I tried create unix user by
createuser command. And these names could be potential names of
system commands.
Yours is the first time I've heard of anyone with
On Fri, Mar 28, 2008 at 06:43:00PM +0900, Craig Ringer wrote:
> Alain Roger wrote:
> > I do not agree with you Sam.
> >
> > Stored procedure are safe from hacking (from external access).
>
> In that a stored procedure encapsulates a series of data operations,
> meaning that the client doesn't hav
am Fri, dem 28.03.2008, um 3:01:43 -0700 mailte ajcity folgendes:
>
>
>
>
> Local file systems, and the user postgres needs write-access. I'm using
> /tmp/... for such.
> And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a
> table via 'create table as select ...' and COP
Local file systems, and the user postgres needs write-access. I'm using
/tmp/... for such.
And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a
table via 'create table as select ...' and COPY this table.
I'm trying to avoid exporting to the local machine before uploadin
ajcity wrote:
> If I wanted to use that with a command like "COPY (SELECT * FROM country
> WHERE country_name LIKE 'A%') TO ''; " do I specify the file
> location for the remote machine as the or do I specify the
> location for local machine?
> And what if the psql clients are different (local: 8
Alain Roger wrote:
> I do not agree with you Sam.
>
> Stored procedure are safe from hacking (from external access).
In that a stored procedure encapsulates a series of data operations,
meaning that the client doesn't have to know the details or even have
privileges to run the individual operatio
am Fri, dem 28.03.2008, um 2:08:17 -0700 mailte ajcity folgendes:
>
>
> If I wanted to use that with a command like "COPY (SELECT * FROM country
> WHERE country_name LIKE 'A%') TO ''; " do I specify the file
> location for the remote machine as the or do I specify the
> location for local mach
If I wanted to use that with a command like "COPY (SELECT * FROM country
WHERE country_name LIKE 'A%') TO ''; " do I specify the file
location for the remote machine as the or do I specify the
location for local machine?
And what if the psql clients are different (local: 8.1.5 remote:8.2.6)?
On Friday 28. March 2008, josep porres wrote:
>Hi guys,
>
>Is there any other online place, apart from
>http://www.postgresql.org/docs/8.3/static/plpgsql.html ,
>to get a reference or a wider explanation of PL/pgSQL ?
>Do you recommend any book?
I found this page rather useful:
http://www.onlamp.
ajcity wrote:
> Thanks all. The COPY command seems to do the work.
> One more thing, say I want the data dumped on a remote machine rather than
> on the current machine, how would I do that without having to first dump it
> on the local machine then uploading to the remote machine?
>
Install ps
Thanks all. The COPY command seems to do the work.
One more thing, say I want the data dumped on a remote machine rather than
on the current machine, how would I do that without having to first dump it
on the local machine then uploading to the remote machine?
--
View this message in context:
h
am Fri, dem 28.03.2008, um 9:16:34 +0100 mailte josep porres folgendes:
> Hi guys,
>
> Is there any other online place, apart from
> http://www.postgresql.org/docs/8.3/
> static/plpgsql.html ,
> to get a reference or a wider explanation of PL/pgSQL ?
A lot of well-explained code-snippets can y
Hi guys,
Is there any other online place, apart from
http://www.postgresql.org/docs/8.3/static/plpgsql.html ,
to get a reference or a wider explanation of PL/pgSQL ?
Do you recommend any book?
thx
Josep Porres
On Thu, Mar 27, 2008 at 11:10:39PM -0400, David T wrote:
> Anyway - I would like these new functions to be permanently available
> to a database, or to all databases. These functions will ultimately
> be called from PHP, where there is no guarantee of server state in
> between requests (maybe a re
Hello,
I have a scenario with two tables, one with 5M rows and the other with
about 3.7M (a subset taken from the first table). Each is clustered
using its primary key (a single bigint column), and pg_stats shows that
the id's correlation is 1 for both tables. In addition, I have a view
ove
Hi all,
I am using Ubuntu 7.10 with PostgreSQL 8.2. I have just finished creating two
C functions and have successfully loaded them using "CREATE OR REPLACE
FUNCTION" ... This was an extremely smooth process, and I have a lot of respect
for the dev team for creating such a robust system.
Any
ajcity wrote:
>
>
> CAJ CAJ wrote:
>
>> Have you looked at "pg_dump -t"
>> http://www.postgresql.org/docs/8.2/static/app-pgdump.html
>>
>> Joey
>>
>>
>>
>
> Thanks for quick response but "pg_dump" does not allow me to dump from a
> SQL SELECT query which is what I wanna do.
>
>
Ma
On Thu, Mar 27, 2008 at 10:29:37PM -0700, Swaminathan Saikumar wrote:
> 4. Why not provide that feature as a core feature, rather than an add-on? If
> the community really feels strongly about this, discourage this practice
> with a best-practices section, citing problems with examples, and
> worka
I do not agree with you Sam.
Stored procedure are safe from hacking (from external access).
>From my point of view transitions should be used only as internal purpose or
via intrAnet and not thru intErnet.
at list this is how under MS SQL they use to teach.
regarding unique constraint, i already
CAJ CAJ wrote:
>
>
> Have you looked at "pg_dump -t"
> http://www.postgresql.org/docs/8.2/static/app-pgdump.html
>
> Joey
>
>
Thanks for quick response but "pg_dump" does not allow me to dump from a
SQL SELECT query which is what I wanna do.
--
View this message in context:
http://w
85 matches
Mail list logo
