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 gradual backup.
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 any connected state.
On Thu, Mar 27, 2008 at 11:05 PM, ajcity [EMAIL PROTECTED] wrote:
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
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:
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
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
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.
May be selective COPY will
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.
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
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
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
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 psql
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:
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:
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 you
If I wanted to use that with a command like COPY (SELECT * FROM country
WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file
location for the remote machine as the filename or do I specify the
location for local machine?
And what if the psql clients are different (local: 8.1.5
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 'filename'; do I specify the file
location for the remote machine as the filename or do I specify the
location for
ajcity wrote:
If I wanted to use that with a command like COPY (SELECT * FROM country
WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file
location for the remote machine as the filename or do I specify the
location for local machine?
And what if the psql clients are different
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 operations
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
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 have to know
Greg Sabino Mullane napsal(a):
snip
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
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 COPY this
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)
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
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 DISTINCT
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 DISTINCT ON (value) id,
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 12
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.
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 id, min(value)
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 gradual
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?
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 text files
So
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
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
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
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
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
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 a Unix command
Hello Glyn,its confusing, but You didnt 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 ALTER
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 was an
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 different
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
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
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
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 -
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 'filename'; do I specify the file
location for the remote machine as the filename or do I specify the
location for local machine?
And what if the
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
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,
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 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
Thanks Tom ludwig, I understand now.
ba rel=nofollowGlyn Astill/a
/b
- 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
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 orderrow
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
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
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
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 think
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
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
in
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 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 result
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
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
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
different from
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
+---+-
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?
Absolutely
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
0.5-1
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
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
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 advice
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 escape the
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 for
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:
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 outer
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
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 8GB,
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
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
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
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
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
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 running
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
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
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
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
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
87 matches
Mail list logo
