Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
Is it possible that there is some limitation to the number of statements
in a single transaction?
2^32, and if you'd exceeded it, you'd get a very specific error message
saying so.
Well, that's a relief. At least it means I'm not running i
I think I saw something posted about this recently, but I can't find it
in the archives now. :(
I want to have created and updated fields in a table that are kept
up-to-date by the database and can't be changed accidentally. I think
this can be done with rules, but I'm not sure of the best way to
Hi,
I need to use ORDER BY clause in a UNION query and the Order BY
columns are not included in the SELECT statement. I tried like this
(select from a) UNION (select . from b) order by a.ename;
It says that
ERROR: Attribute "ename" not found
How to do this.
rgds
Antony Paul
On Mon, Jan 10, 2005 at 05:28:47AM -0600, Jim C. Nasby wrote:
> I think I saw something posted about this recently, but I can't find it
> in the archives now. :(
>
> I want to have created and updated fields in a table that are kept
> up-to-date by the database and can't be changed accidentally. I
On Fri, Jan 07, 2005 at 12:04:00PM -0400, Marc G. Fournier wrote:
> A current list of *known* supported platforms can be found at:
> http://developer.postgresql.org/supported-platforms.html
> We're always looking to improve that list, so we encourage anyone that is
> running a platform not l
Antony Paul wrote:
Hi,
I need to use ORDER BY clause in a UNION query and the Order BY
columns are not included in the SELECT statement. I tried like this
(select from a) UNION (select . from b) order by a.ename;
It says that
ERROR: Attribute "ename" not found
How to do this.
The "o
Try
select a.col1 as ename from a
union
select b.othercolumn as ename from b
order by ename
Give the columns you want to order on the same name using the "as XXX"
syntax, and remove the "a." prefix from the order statement.
John Sidney-Woollett
Antony Paul wrote:
Hi,
I need to use ORDER BY cl
Alban Hertroys <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Alban Hertroys <[EMAIL PROTECTED]> writes:
>>> As they're inserts, and therefore not even touching the same data, I'm
>>> quite certain it's not some kind of row locking issue (does that even
>>> happen at all with MVCC?).
>>
>> I'm
Richard Huxton writes:
> Antony Paul wrote:
>> I need to use ORDER BY clause in a UNION query and the Order BY
>> columns are not included in the SELECT statement. I tried like this
>>
>> (select from a) UNION (select . from b) order by a.ename;
>>
>> It says that
>> ERROR: Attribute
Michael Fuhr wrote:
On Fri, Jan 07, 2005 at 09:00:12PM +0100, Florian G. Pflug wrote:
CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text)
RETURNS "trigger" AS $$
See the "Trigger Procedures" section of the PL/pgSQL documentation.
The first paragraph contains this:
Note that the f
Chris <[EMAIL PROTECTED]> writes:
> This is Postgresql 8 RC 1 on freebsd 5.3.
> I changed the type of a column from varchar(128) to varchar(256), and
> right after that I got the following messages when doing inserts on
> the table in question.
> <41e0e2c3.fd54>ERROR: SMgrRelation hashtable corr
Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
It does have a reference to a table with statusses, but those are rather
static. I suppose an integrity check is comparable to doing a select
with respect to locking strategies? (Meaning that it wouldn't be the
cause of my problem).
No,
Hi,
Citing "Jim C. Nasby" <[EMAIL PROTECTED]>:
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp
[snip]
> Does anyone have an example of the best way to handle this scenario?
Something along the lines o
Forgive my ignorance, but I'm still learning about much of this stuff.
If you perform:
select an_id, int_value from my_table where int_value>400;
The table has an index on int_value and there are enough rows to
warrant using it. Doesn't the database perform in index scan on
int_value followed b
On Mon, 2005-01-10 at 15:45 +0100, Daniel Martini wrote:
> Hi,
>
> Citing "Jim C. Nasby" <[EMAIL PROTECTED]>:
> > ON INSERT: force created and updated to be current_timestamp
> > ON UPDATE: deny updated created. force updated to be set to
> > current_timestamp
> [snip]
> > Does anyone have an exam
On Mon, Jan 10, 2005 at 10:26:46 -0500,
Alex Turner <[EMAIL PROTECTED]> wrote:
> Forgive my ignorance, but I'm still learning about much of this stuff.
> If you perform:
>
> select an_id, int_value from my_table where int_value>400;
>
> The table has an index on int_value and there are enough
Does postgres have a function to determine the data type of an
argument? I'm looking for something analogous to PHP's gettype
function. I had a look through the documentation and did a few likely
pattern searches with \df. Nothing came up.
Apologies in advance if the answer is obvious.
Cheer
I'm no database writing guru, but wouldn't it just be a matter of
adding a transaction number to an index entry so as to determine it's
newness and only retrieve entries with an older transaction number?
I'm guessing that the theory is that most insert transactions will be
committed, or only conta
On Mon, Jan 10, 2005 at 11:51:51AM -0500, Alex Turner wrote:
> I'm no database writing guru, but wouldn't it just be a matter of
> adding a transaction number to an index entry so as to determine it's
> newness and only retrieve entries with an older transaction number?
No, it's more complex than
On Mon, Jan 10, 2005 at 11:51:51 -0500,
Alex Turner <[EMAIL PROTECTED]> wrote:
> I'm no database writing guru, but wouldn't it just be a matter of
> adding a transaction number to an index entry so as to determine it's
> newness and only retrieve entries with an older transaction number?
No, bec
Hi all,
I have another question, I hope it isn't too basic. ^.^
I want to do a select from multiple tables but not join them. What I
am trying to do is something like this (though this doesn't work as I need):
SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
file_info_2 b, fi
On Tue, Jan 11, 2005 at 03:28:08AM +1100, Brendan Jurd wrote:
> Does postgres have a function to determine the data type of an
> argument?
In what context? What problem are you trying to solve?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)-
I hope I've read your question properly - I seem to be giving answers to
un-asked questions lately! ;)
How about...
SELECT file_name from file_info_1 WHERE file_name='/'
union
SELECT file_name from file_info_2 WHERE file_parent_name='/'
union
SELECT file_name from file_info_3 WHERE file_parent_na
Typo, that should have been:
SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
file_info_2 b, file_info_3 c WHERE a.file_parent_dir='/' AND
b.file_parent_dir='/' AND c.file_parent_dir='/';
(All the WHERE... are the same)
Madison
Madison Kelly wrote:
Hi all,
I have another questi
To be honest, it looks like you might need a schema adjustment.
Normalization means keeping one kind of thing in one place, avoiding
ugly queries. Inheritance can also help with this too.
Alex Turner
NetEconomist
On Mon, 10 Jan 2005 12:22:41 -0500, Madison Kelly <[EMAIL PROTECTED]> wrote:
> Hi
On Mon, Jan 10, 2005 at 12:22:41PM -0500, Madison Kelly wrote:
> What I need is to return all of the matches in all of the tables
> in a single column.
Maybe you're looking for UNION -- see the "Combining Queries" section
in the "Queries" chapter of the documentation.
--
Michael Fuhr
http://www
John Sidney-Woollett wrote:
I hope I've read your question properly - I seem to be giving answers to
un-asked questions lately! ;)
How about...
SELECT file_name from file_info_1 WHERE file_name='/'
union
SELECT file_name from file_info_2 WHERE file_parent_name='/'
union
SELECT file_name from file
Am Montag, 10. Januar 2005 18:22 schrieb Madison Kelly:
> Hi all,
>
>I have another question, I hope it isn't too basic. ^.^
>
>I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I
> need):
>
> SELECT a.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
As for the first part, try this:
(SELECT file_name FROM file_info_1 WHERE file_name = '/') UNION
(SELECT file_name FROM file_info_2 WHERE file_parent_dir = '/') UNION
(SELECT file_name FROM file_info_3 WHERE file_parent_dir = '/')
As for sorting the com
On Mon, Jan 10, 2005 at 12:47:53PM -0500, Madison Kelly wrote:
Madison,
> The trick now is I need to know which table each result came from. I can
> add another column and record the table number and SELECT that at the
> same time but before I do I was wondering if I can do this more
> efficie
Alex Turner wrote:
To be honest, it looks like you might need a schema adjustment.
Normalization means keeping one kind of thing in one place, avoiding
ugly queries. Inheritance can also help with this too.
Alex Turner
NetEconomist
Hi,
The schema started off with all the data in one place as y
On Mon, Jan 10, 2005 at 12:47:53 -0500,
Madison Kelly <[EMAIL PROTECTED]> wrote:
>
> The trick now is I need to know which table each result came from. I can
> add another column and record the table number and SELECT that at the
> same time but before I do I was wondering if I can do this mor
Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 03:28:08AM +1100, Brendan Jurd wrote:
Does postgres have a function to determine the data type of an
argument?
In what context? What problem are you trying to solve?
Well, I solved the original problem in a different way, but I'd still
like t
Bruno Wolff III wrote:
On Mon, Jan 10, 2005 at 12:47:53 -0500,
Madison Kelly <[EMAIL PROTECTED]> wrote:
The trick now is I need to know which table each result came from. I can
add another column and record the table number and SELECT that at the
same time but before I do I was wondering if I c
SELECT 'table1' AS source, file_name FROM file_info_1 WHERE
file_parent_dir='/'
UNION
SELECT 'table2' AS source, file_name FROM file_info_2 WHERE
file_parent_dir='/'
UNION
SELECT 'table3' AS source, file_name FROM file_info_3 WHERE
file_parent_dir='/' ORDER BY file_name;
On January 10, 2005
On Mon, Jan 10, 2005 at 13:24:50 -0500,
Madison Kelly <[EMAIL PROTECTED]> wrote:
>
> ^.^; Can you point me to docs that will help me learn how to do that?
> Thanks! Or rather, do you mean add a column to the table with an ID for
> the table that I select beside the file_name? If so, that is wh
I was not hoping that indexing, per se, would help me.
In fact, indexing smiles would be of virtually no use
to me, except for exact matches, e.g. where smiles = 'CCCOC';
I was only trying to subvert the use of indexing for
my own purposes, to store the parsed smiles somewhere
automatic for the sql
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
SELECT file_name, 1 FROM file_info_1 WHERE file_parent_dir='/' UNION
SELECT file_name, 2 FROM file_info_2 WHERE file_parent_dir='/' UNION
SELECT file_name, 3 FROM file_info_3 WHERE file_parent_dir='/'
ORDER BY file_name;
The second column now shows whic
On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:
>
> The original problem had to do with querying a row-returning function.
> I had an SQL function that returned "SETOF record", and I was trying to
> use it in the FROM clause of a query. To do so, you need to provide a
> list of colu
Frank D. Engel, Jr. wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
SELECT file_name, 1 FROM file_info_1 WHERE file_parent_dir='/' UNION
SELECT file_name, 2 FROM file_info_2 WHERE file_parent_dir='/' UNION
SELECT file_name, 3 FROM file_info_3 WHERE file_parent_dir='/'
ORDER BY file_name;
The s
Example :
psql
create table test (id serial primary key, data10 varchar(10), data20
varchar(20), data text );
insert into test (data10, data20, data) values ('ten','twenty','all i
want');
python
import psycopg
db = psycopg.connect("host=localhost dbname=.")
c = db.cursor()
c.execute( "SELE
On Mon, Jan 10, 2005 at 11:23:42AM -0600, Bruno Wolff III wrote:
> There have been discussions in the past about why the core developers
> feel that moving visibility status into indexes would be a net loss
> on average. I don't think there has been one for a while, but you can
> try searching the
That's not what I meant...
I meant, what does 'c1c1C(=O)N' means ?
If the search operation is too slow, you can narrow it using standard
postgres tools and then hand it down to your C functions. Let me explain,
I have no clue about this 'c1c1C(=O)N' syntax, but I'll suppose you
On Mon, Jan 10, 2005 at 11:16:03AM -0500, Sven Willenberger wrote:
> These could also be combined into one trigger since they are nearly
> identical anyway:
>
> CREATE FUNCTION combined_trigger() RETURNS TRIGGER AS '
> BEGIN
>NEW.update := CURRENT_TIMESTAMP;
>IF TG_OP = ''INSERT'' THEN
>
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister co
hello to all
Soon time I am going to face a migration of a DBII
database our dear postgresql .
I will thank for any advice that can give me, in
addition this migration
will have 20 million records.
20 million record is to large instalation, exist some
tips for this type of facilities?
Tia .
Best
Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:
The original problem had to do with querying a row-returning function.
I had an SQL function that returned "SETOF record", and I was trying to
use it in the FROM clause of a query. To do so, you need
Hi,
On Mon, 10 Jan 2005, Chris wrote:
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?
www.PostgreSQL.org holds docs only for stable releases.
Regards,
--
Devrim GUNDUZ
devrim~gunduz.org, devr
Pierre-Frédéric Caillaud wrote:
Example :
psql
create table test (id serial primary key, data10 varchar(10), data20
varchar(20), data text );
insert into test (data10, data20, data) values ('ten','twenty','all i
want');
python
I know that these kinds of functions are available from other la
Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:
The original problem had to do with querying a row-returning function.
I had an SQL function that returned "SETOF record", and I was trying to
use it in the FROM clause of a query. To do so, you need to provide a
20 million rows isn't that large, unless you've got some really wide
rows. You shouldn't have any major issues, just make sure you are
vacuuming periodically.
On Mon, Jan 10, 2005 at 06:22:34PM -0300, marcelo Cortez wrote:
>
> hello to all
>
> Soon time I am going to face a migration of a DBII
On Mon, 2005-01-10 at 15:22, marcelo Cortez wrote:
> hello to all
>
> Soon time I am going to face a migration of a DBII
> database our dear postgresql .
> I will thank for any advice that can give me, in
> addition this migration
> will have 20 million records.
> 20 million record is to large in
On Tue, Jan 11, 2005 at 08:29:33AM +1100, Brendan Jurd wrote:
> My post was all about finding out whether postgres has this
> functionality.
Certainly PostgreSQL provides a way to discover a row's column
types, but how to do it depends on where you're trying to do it
from. If you're writing a cl
Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 08:29:33AM +1100, Brendan Jurd wrote:
My post was all about finding out whether postgres has this
functionality.
Certainly PostgreSQL provides a way to discover a row's column
types, but how to do it depends on where you're trying to do it
from.
Basically what I want is to limit a user to not being able to view
certain tables within a schema, in this case the public schema. Say
we have 300 tables in a database but a particular user only needs
access to 3 of them. I don't really want them to even be able to view
all the other tables. Us
Dear All,
I was a bit surprised to find that
to_char(0,'.99')
returns .00 rather than 0.00.
Is this a bug or a feature? How do I get what I want?
(This is with 7.4.2. Appologies if it is a known problem. The search
form in the list archives seems to split words on _, so looking for
hi bruno,
yeah, the postgresql problem was fixed by changing IF statements to
CASE statements in forum.module code:
// remove:
-- $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid),
l.last_comment_timestamp, IF(l.last_comment_uid, cu.name,
l.last_comment_name) as last_comment_name,
lol wrote:
Hi,
I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adj
We are soon to be doing performance testing. Our testing environment
contains 4 SCSI disks in a RAID5 configuration, while our production
environment will have 6 SCSI disks, which I'm suggesting we allocate as
4 in RAID5 for data and 2 in RAID1 for WAL.
Because we don't have separate disks for WA
Daniel Schuchardt wrote:
lol wrote:
Hi,
I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some
to_char(0,'9990.00')
?
On Mon, 2005-01-10 at 22:48 +, Phil Endecott wrote:
> Dear All,
>
> I was a bit surprised to find that
>
> to_char(0,'.99')
>
> returns .00 rather than 0.00.
>
> Is this a bug or a feature? How do I get what I want?
>
> (This is with 7.4.2. Appolog
On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:
> Actually I'm looking for an internal function -- something within
> postgres' implementation of SQL itself, which I can use in queries
> independent of the front-end language. The same way you use functions
> like to_char() or now
"Guy Rouillier" <[EMAIL PROTECTED]> writes:
> We are soon to be doing performance testing. Our testing environment
> contains 4 SCSI disks in a RAID5 configuration, while our production
> environment will have 6 SCSI disks, which I'm suggesting we allocate as
> 4 in RAID5 for data and 2 in RAID1 f
Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:
Actually I'm looking for an internal function -- something within
postgres' implementation of SQL itself, which I can use in queries
independent of the front-end language. The same way you use functions
like to
Tom Lane wrote:
>> Because we don't have separate disks for WAL in our test environment,
>> I'd like to minimize the effect of WAL. I've read the sections in
>> both the tuning guide and the base documentation, and I'm still a
>> little unclear about fsync and wal_sync_method. If I set fsync
>> F
"Guy Rouillier" <[EMAIL PROTECTED]> writes:
> Thanks, Tom. This app has very high insert activity. What's the best
> way to minimize the effect of WAL given that we don't have separate
> disks to put it on in our test environment?
There's not a lot you can do about it :-(. You can take some amo
On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:
> Your coltype() function is exactly what I'm looking for. I'd envisaged
> something that takes an anyelement argument and returns the type as
> text, but returning the OID is even better.
>
> Can you please provide the source for t
The link is not there, but the doc sure is :
http://www.postgresql.org/docs/8.0/static/
Is it the right version ?
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?
Chris
When I try to connect to template1 via psql, I get the following error
message:
psql: FATAL: Database "template1" does not exist in the system catalog.
I get a similar error when trying to do a pg_dumpall.
In searching the list and trying to find a good place to start, I downloaded
pgfsck and I
I apologize if this is a duplicate of a message I just sent. The message
didn't appear to go through. I seem to have encountered a major corruption
error. I am currently running PostgreSQL 7.3.6. Here is an example of an
error I received:
# psql -h 127.0.0.1 -p 5432 -U postgres template1
ps
"Aaron Mark" <[EMAIL PROTECTED]> writes:
> When I try to connect to template1 via psql, I get the following error
> message:
> psql: FATAL: Database "template1" does not exist in the system catalog.
Hm, can you connect to any other databases? If so, what does "select *
from pg_database" show?
Devrim GUNDUZ wrote:
Hi,
On Mon, 10 Jan 2005, Chris wrote:
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?
www.PostgreSQL.org holds docs only for stable releases.
Regards,
--
Devrim GUNDUZ de
Unfortunately, I can't seem to connect to any other database. I only had
one non-system database so I tried that, and that I was thinking there was a
template0, so I tried that as well.
Thanks in advance for any help you can provide!
Aaron
From: Tom Lane <[EMAIL PROTECTED]>
To: "Aaron Mark" <[E
Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:
Your coltype() function is exactly what I'm looking for. I'd envisaged
something that takes an anyelement argument and returns the type as
text, but returning the OID is even better.
[...snip slick function...]
Now
I need to know the day of the week for a schedule rotation algorithm i'm working
on. Initially i was going to use the function for day of the year and week of
the year to find the first day of the year, but it seems like it would be easier
to use the postgres, dmy format and then parse the strin
[EMAIL PROTECTED] writes:
> I need to know the day of the week for a schedule rotation algorithm
> i'm working on.
extract(dow ...) might help.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your d
On Jan 11, 2005, at 15:03, [EMAIL PROTECTED] wrote:
I need to know the day of the week for a schedule rotation algorithm
i'm working
on. Initially i was going to use the function for day of the year and
week of
the year to find the first day of the year, but it seems like it would
be easier
77 matches
Mail list logo
