to be a superior product.
Of course when someone /does/ know PostgreSQL, it's usually a sign that
they have more than a passing familiarity. I wonder how many MySQL
admins are on the same level of proficiency as Windows admins due to
ubiquitity.
Gregory Wood
---(end of broadcast
Does dbmirror do that? No, it does not. It also doesn't support
promoting a slave database to a master; that has to be done manually, so
I wouldn't consider that too big a problem.
Worse in my opinion is that sequences don't get updated... so a slave
that tries to do an insert on a replicated
Jan Wieck wrote:
If you don't know the answers to that, I assume it isn't that easy as
people try to make believe. And in case the answer is that is not
possible but ..., then you better think again what you want that
replication setup for.
Although I agree with your points (especially having
I have a comment field in a table that I want populated if another field
has a certain value. Is it possible to set a check constraint for this?
Example:
Let's say we have fields
Purchase_type smallint check(purchase_type 4)
comment varchar
I need a check rule to something like (pseudo code)
Stephane Tessier wrote:
Hi,
I have a problem with triggers in postgresql 7.3.4 and PHP... I have a lot
of insert to do from a table x to table y ( average of 1 rows each
time). I use a trigger AFTER INSERT but it seems that PHP wait for the
result of the trigger to ending the script... Is it
Mike Nolan wrote:
However, if I update table 'B' and the 2nd trigger fires, that trigger
will still see the OLD value if does a query on table 'A', since I
think transaction atomic rules require that any updated values aren't
made available to the outside world (including other triggers) until
Further pointers are welcome, otherwise if I figure it out myself, I'll
post the results.
Just an opinion here, but I would implement these queries as views
similar to the information schema. That way you won't have to port
everything when the database changes. Once you upgrade to 7.4 you can
Perhaps a combination of those settings is what did it?
Maybe --- what was the failure message exactly, once you got it?
Managed to fix it before I got any errors actually. Simple enough to
re-break it however:
FATAL: max_fsm_pages must exceed max_fsm_relations * 16
Looks like I was 6
Yeah, I know. I was just trying to defend mysql. ^_^ We use both, and so
far, it's been the smaller headache, so...
That may be true... until you have to implement transactions and/or foreign
keys at the application level.
The really strange thing is, one of our newwer databases has
But the question itself tells that you're about to implement
a major design error in your application. Holding database
locks during user interaction IS A BAD THING. Never, never
ever do it that way. And anybody telling you something
different is
key constraint is ON DELETE NO ACTION, which confusingly
enough aborts the transaction (it's defined that way in the
SQL standard, don't ask me why they called it NO ACTION).
NO ACTION because it takes no action on the child record? Makes sense when
you consider that
Any suggestions would be more then appreciate as always. Is there a
better way to do what I'm trying to do?
I would recommend using only one sequence for the master table. Then just
reference that sequence value for the two foreign keys. For example:
BEGIN;
INSERT INTO Bugs (bug_date) VALUES
This code looks fine to me, other than missing the actual trigger statement
is missing. Assuming table 1 is named apps:
DROP TRIGGER OnApplicationsDelete ON apps;
DROP FUNCTION ApplicationsDeleteFn();
CREATE FUNCTION ApplicationsDeleteFn()
RETURNS OPAQUE
AS '
BEGIN
delete from ports where
Can you please tell me, how to do this in postgresql
Use a SERIAL datatype:
http://postgresql.crimelabs.net/users-lounge/docs/7.1/user/datatype.html#DAT
ATYPE-SERIAL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
A direct query gets appropriate rows of data:
dbname=# select * from partdef where shpname = 'IDC16W';
...while the very same query (substituting LIKE for the '=' sign) gets
nothing!?
dbname=# select * from partdef where shpname LIKE 'IDC16W';
Can someone please tell me the really stupid
Inside a transaction,
I insert a row into the table, with an autonumber id.
Now I want to return the id of the newly inserted row. How can I do
that?
It seems that the first insert statement is not committed, and so the
second statement couldn't return the newly id.
SELECT
Then, I just have a telnet/ssh/etc session on the server running :
Would this be useful to you?
Seem just as easy to run psql to me. In fact a bit easier as I don't have to
switch back and forth between windows.
I guess it goes back to the days when I was first learning SQL on a MySQL
web
... So anytime I see something that requires some sort of shell access
I don't like it. EXPLAIN is one of those things.
Huh? EXPLAIN doesn't require shell access ... it merely requires a
client that doesn't drop NOTICE messages into the bit bucket.
Clients that drop NOTICEs are broken
Could u just tell me if it is possible to have a Windows Client and the
Server running on Linux and having Postgres and the two communicate thru
something like the ODBC.
Yes, although it's preferable to use a native library.
Does Postgres have anything for the advanced features like OleDB
I'm partial to the ZEOS Database Explorer myself. I don't have experience
with a multitude of tools, so the best I can offer is that I have no
problems with the latest version :) Actually, I can tell you that it has the
advantage of a native interface (rather than relying on ODBC). And it's
free,
When the name of a sequence auto-generated from a SERIAL type would be
longer than 32 chars, it appears that CREATE TABLE uses a different
name truncation algorithm than DROP SEQUENCE. Example below. Note
the difference between the following:
'process_state_subscripti_id_seq'
I am generating scripts from MSSQL Server and converting them to create
objects in PostgreSQL. It is suprisingly easy. However, I think I may have
hit a rock.
It appears that PostgreSQL does not support listing constraints to be
added as in the following syntax:
I don't know why it won't
I have a plpgsql update trigger on one of my tables. The intention of the
trigger is to check to see if a particular field is updated, and if so
INSERT a record into another table. The IF statement looks something like
this:
IF new.MyField old.MyField THEN
This works great when MyField has an
This table currently has 224 rows of data in it.
The following queries *ALL* take approx. .433 seconds to run.
select * from status s where s.site_id = 18 and s.host_id = 49 and
s.product = 'BETA' and s.class = 'APPS' and s.subclass = 'MONITOR' ;
select * from status s where s.site_id =
I have a table with approx. 2mln records.
There were a few for which I had to update statusfield, so I did:
update table set statusflag = 'U' where id in ('id10', 'id20',
'id30');
this took so long that I cancelled it, and used separate
update table set statusflag =
What I'd like to do is pull a list of records where there is a range
of last names; say from A - F.
select * from table where last_name LIKE 'A%' AND last_name LIKE 'F%'
- for example.
The above code I've tried for this doesn't seem to work as I'd expect
it too?
When you use the AND
Can anyone tell me what this notice means?
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s) ?
It means that PostgreSQL will automatically create triggers to perform your
foreign key checks :)
Seriously though, PostgreSQL has to have some mechanism to check that
I don't know if you can name a column date because I think it's a
reserved word.
Oddly enough, it *does* work (at least on my version of 7.1), although I
would recommend against doing it if for no other reason than it's confusing.
Greg
---(end of
Ahhh, but I didn't use a double quote identifier. This statement worked fine
for me:
CREATE TABLE atable (
title VARCHAR(20),
name VARCHAR(20),
id SERIAL PRIMARY KEY,
date DATE);
Greg
- Original Message -
From: Joel Burton [EMAIL PROTECTED]
To: Gregory Wood [EMAIL PROTECTED]
Cc: Poul L
I'm surprised that works at all... the name of the table is atable,
isn't
it? Try this:
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
Greg
Thanks for the help. I wrote the command out wrong in the post, I did try
the
one that you wrote out, and it didn't
To the best of my knowledge, you can't use parameters for table names. But
if you have 7.1, you can use the EXECUTE command to dynamically generate
(and execute) any SQL statement. Check out:
http://postgresql.readysetnet.com/devel-corner/docs/programmer/plpgsql-descr
iption.html
Specifically:
This has been discussed quite a few times recently (in fact, I posted a
similar reply yesterday). From Tom Lane:
"Change php_pgsql.h to refer to postgres_fe.h not postgres.h."
Greg
- Original Message -
From: "Gordon A. Runkle" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday,
You didn't mention which version either of those machines are running. I
believe in 7.1 Postgres the database filenames changed to a numerical
database id of some sort. Prior to that, they were named the same as the
database. I don't know the reasoning behind the switch, but I'm fairly sure
it
SELECT sum(grade) / count(grade) As GPA FROM grades;
(bad juju if 0)
No kidding... that kid totally failed ALL his classes! And before someone
points it out, yes I saw the DBZ.
Greg
---(end of broadcast)---
TIP 1:
I am pretty sure that pg_dump produces a consistent snapshot, using
the same transaction isolation mechanism (MVCC) as the database
itself. If one of the gurus posts and says otherwise, believe him,
not me. ;)
My impression from reading the source was that it was consitant per-table
I have a table and want to make a view updateable with rules. Table has a
sequence that creates default value to primary key when inserting.
And now the question: Is it posible to return value of primary key from
rule of inserting with NEW? Is that value what i would get from extern
I have a table and want to make a view updateable with rules. Table
has a
sequence that creates default value to primary key when inserting.
And now the question: Is it posible to return value of primary key
from
rule of inserting with NEW? Is that value what i would get from
Boulat Khakimov [EMAIL PROTECTED] writes:
Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.
Er, what's wrong with psql's "\d table" ?
Those without shell
43 hours? Ouch, that is quite a long time! I'm no expert by any means, but
here are a few tips that I've picked up on this list that might help out:
1. The COPY command is blazing fast for importing, if you are certain your
input is clean, this is the way to go. Read more about that here:
Using a DateTime value in a WHERE clause is not a good idea for the reason
that DateTimes are usually represented by a floating point value in the
database itself. And since floating point numbers are prone to rounding
errors, they don't make for a good unique identifier.
If you have to use
columns with default timestamp('now') see to be
defaulting to the time I started posgresql!
I noticed that timestamps (in my case CURRENT_TIMESTAMP) are taken from the
beginning of a transaction. You didn't mention how you were accessing the
database, but if you were updating everything inside
Oh, I just made sure that I started a new transaction :)
I actually prefer that timestamps are handled that way... once I realized
*why* I had triggered INSERTs that were stamped 45 minutes earlier than I
thought they should have been.
Greg
Yes, and that's a feature :)
If you want a wall
I am trying to design a database in SQL Server ver7, using
Can anyone PLEASE HELP??
Possibly, but you'd be more likely to find an answer on an SQL Server
mailing list. This list is for the PostgreSQL database.
Greg
We recently upgraded our development server to 7.1 and I believe I've
noticed an inconsistency with how update triggers behave on version 7.1
versus 7.0. Since I'm not sure which should be the expected behavior I have
no idea if it is a bug or not. Here is the situation:
I have a table in which
You can detect whether a field is actually being *changed* by comparing
NEW.field and OLD.field. You cannot distinguish the case where the
UPDATE didn't mention a field from the case where it did but assigned
the same value that was already there (eg UPDATE foo SET x = x).
This behavior was
I ran some test to see how many queries PostGreSQL can handle per
seconds through the libpq library : not more than 200.
So, I was wondering if it is possible to write C function, compiled as
.so which can perform SQL queries such as SELECT or UPDATE ?
I think it could increase
SELECT * FROM my_table WHERE my_field LIKE 'A%';
Greg
- Original Message -
From: "Jorch" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 16, 2001 12:39 PM
Subject: Query question
How can I make query
SELECT * FROM my_table WHERE my_field = 'A*';
Meaning that I
Someone will correct me if I'm wrong, but I'm pretty sure all you have to do
is start a transaction. Example:
begin;
create table t1 (col1 varchar(10));
insert into t1 (col1) values ('test123');
rollback;
Greg
- Original Message -
From: Guang Mei
To: [EMAIL PROTECTED]
Sent:
DROP TABLE employees
Error: ERROR: Relation 'employees' does not exist
And execution halts which is I believe why he wanted to check for the
existence before trying to DROP. I'd love to know if this exists as
well...
would come in very handy during development time.
Execution does
Then it goes right back to what someone else eluded to... If you don't
like what you see in the documentation, contribute. Send in diffs, or
even new items. If you don't know what's needed go to the DOCS list and
ask. Getting people to help with documentation for ANY project is like
he example in the documentation (located at:
http://www.postgresql.org/docs/postgres/xplang.htm#AEN22060 ). Specifically
I executed both the CREATE FUNCTION and CREATE TRUSTED PROCEDURAL LANGUAGE
statements.
Any help would be most appreciated.
Gregory Wood
51 matches
Mail list logo
