I have general question about FOREIGN KEYs:
1. Suppose I have table A with primary key X, and another table B with
field Y.
2. When I 'ALTER TABLE B ADD FOREIGN KEY( Y ) REFERENCES A ON
UPDATE CASCADE ON DELETE CASCADE', that clearly spends some time
building a separate index.
On 2013-12-18 10:41, Kevin Grittner wrote:
Dean Gibson (DB Administrator) postgre...@ultimeth.com wrote:
... that clearly spends some time building a separate index.
No it doesn't. If you are observing activity at that time, it is probably from
validating that the constraint is initially
On 2010-11-04 15:41, Christine Penner wrote:
I have a table column I want to change from a boolean to a smallint.
changing false to 0 and true to 1. How do I do that?
Christine Penner
Ingenious Software
250-352-9495
ch...@fp2.ca
ALTER TABLE ALTER col_name TYPE SMALLINT
USING CASE WHEN
Oops; see correction below:
On 2010-11-04 16:41, Dean Gibson (DB Administrator) wrote:
On 2010-11-04 15:41, Christine Penner wrote:
I have a table column I want to change from a boolean to a smallint.
changing false to 0 and true to 1. How do I do that?
Christine Penner
Ingenious Software
Oops; previously sent from the wrong eMail address, so I don't know if
this actually got sent:
Two days ago I upgraded five DB boxes (for load balancing) from 8.3.0 to
9.0.1 in order to use replication. The replication configuration went
reasonably well, and now all the four hot_standby
On 2010-10-29 11:17, Alan Hodgson wrote:
I'm curious about this too. It seems that currently I'd have to
rebuild any additional slaves basically from scratch to use the new
master.
I think so long as you pointed (via primary_conninfo) the additional
slaves to the new (pending) master, before
On 2008-03-13 23:14, Scott Marlowe wrote:
Tis the other way round I'm afriad. Schemas live in dbs, not the other way
around. Maybe you were thinking tablespaces?
You're right; I was thinking of tables, which I routinely move around
from schema to schema.
That also means he should
On 2008-03-12 21:30, Scott Marlowe wrote:
...
Can't rename a db, complains that it doesn't exist. Yet psql -l shows that
it does and I can connect to it ???
mmdcc228_SETUP(120)% psql stdb2 -c alter database stdb rename to
stdb_tmp
ERROR: database stdb does not exist
On 2008-03-13 10:10, Gauthier, Dave wrote:
Ya, I'm thinking of dumping all the problem DBs, deleting them, recreating and
reloading.
Last thought: have you tried uninstalling and reinstalling PostgreSQL?
If something is corrupted on the disk, it's either the data or the
software. An
On 2008-03-09 01:45, Mitchell D. Russell wrote:
New to the list, so please forgive me in advance :)
I've been running 8.2 on windows server 2003 for quite some time now.
The database that I take care of stores records with various languages
in it (russian, chinese, etc) and has been working
On 2008-03-09 11:49, Mitchell D. Russell wrote:
Dean:
I did the dump as so: psql –Upostgres databasename c:\temp\dump.sql
I assume you meant pg_dump, not psql.
I think the database was set to SQL_ASCII before I dumped it, because
when I did the 2^nd restore last night to a new
On 2008-02-26 13:04, Tom Hart wrote:
I already have a php script that does some data scrubbing before the
copy. I added this line to the script and things seem to be working
better now
$line = iconv(ISO-8859-1, UTF-8, $line);
Thanks for the help guys :-)
Read up on the difference
On 2008-02-22 17:57, Ralph Smith wrote:
I'm looking at the v7.4 manuals and I don't see how to encode for
importing into a v8 DB using UTF8.
Maybe I'm making this hard on myself?
The old DB is using SQL_ASCII.
We'd like the new one to use UTF8.
As development proceeds, I'm going to have to do
On 2008-02-21 13:37, Justin wrote:
... I'm wondering if there is a way to create this in a single select
statement??
I can't think of a way to do it???
Break down your problem using VIEWs. Create a VIEW that gets just ONE
of the averages, based on a starting date. Then create a SELECT that
If I ALTER DATABASE ... SET client_encoding TO DEFAULT, is the default
the client_encoding in postgresql.conf when the server was last
started, or the value at the time the ALTER DATABASE ... SET
client_encoding TO DEFAULT statement is executed?
In other words, if I ALTER DATABASE ... SET
On 2008-02-21 19:59, Tom Lane wrote:
You can set client_encoding in postgresql.conf if you want to, but I'm having a
hard time understanding why you think that'd be a good idea --- *particularly*
if your database encodings aren't all the same.
regards, tom lane
On 2008-02-12 19:39, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability),
or do you CAST (x as char)? And one might ask in what context
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability), or
do you CAST (x as char)? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/ing or left() make
On 2008-02-12 07:30, Ken Johanson wrote:
Sure, but you're a prime candidate for understanding the value of
following the spec if you're trying to write software that works with
multiple databases.
The spec has diminished in this (CAST without length) context:
a) following it produces an
I've tried various places, and none seem to work. I've even done a
strings `which psql` | grep psqlrc to no avail.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
-02-09 18:45, Tom Lane wrote:
Dean Gibson (DB Administrator) [EMAIL PROTECTED] writes:
I've tried various places, and none seem to work. I've even done a
strings `which psql` | grep psqlrc to no avail.
pg_config --sysconfdir would tell you. I agree the documentation
on this is less than
On 2005-11-09 13:08, Martijn van Oosterhout wrote:
I want to run fast queries by knowing first characters of bar like :
1. Select records from foo where first character of bar is A
2. Select records from foo where first character of bar is B
3. Select records from foo where first two
CREATE TABLE new_name AS SELECT DISTINCT * FROM old_name;
DROP TABLE old_name;
ALTER TABLE new_name RENAME TO old_name;
On 2005-11-04 17:15, Peter Atkins wrote:
All,
I have a duplicate row problem and to make matters worse some tables don't have a PK or any unique identifier.
Anyone have
On 2005-10-20 15:46, Roger Hand wrote:
On Thursday, October 20, 2005 1:01 PM, Martijn van Oosterhout wrote:
On Thu, Oct 20, 2005 at 09:28:25AM -0700, Dean Gibson (DB Administrator) wrote:
I just find it surprising that XML is not one of the formats provided,
considering that XML
The problem described below in 7.4.x, does not occur in 8.0.4, even with
near-simultaneous VACUUMs and updating. Previously, if one VACUUM was
run within a minute or two of the other, the problem below occurred.
-- Dean
On 2005-09-19 09:26, Dean Gibson (DB Administrator) wrote:
Simultaneous
PSQL has the option to output the result of queries in several different
formats, including HTML. Suggestion: have an option to output query
results in XML format. Suggested format:
row
field-1-namefield-1 value/field-1-name
field-2-namefield-2 value/field-2-name
/row
etc. The user
On 2005-10-19 23:52, Michael Glaesemann wrote:
On Oct 20, 2005, at 15:45 , Roger Hand wrote:
On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote:
PSQL has the option to output the result of queries in several
different formats, including HTML. Suggestion: have an option
Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM from
the PostgreSQL site). This morning I found my servers very busy from
three queries that were two hours old:
The following query ran in a fraction of a second on 7.4.8:
SELECT receipt_date, process_date, callsign AS
.
-- Dean
On 2005-10-17 09:35, Dean Gibson (DB Administrator) wrote:
Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM
from the PostgreSQL site). This morning I found my servers very busy
from three queries that were two hours old:
The following query ran in a fraction
. The first one can only return 0 or 1 rows;
the second one can return 0, 1, or 2 rows.
An explain analyze of each should show why one is much faster than the
other.
On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) wrote:
In the query below, if I replace:
(SELECT TRUE
that going back to 7.4.8 is not as easy, as postgresql.conf has
changed going to 8.0, and my installation automatically migrates
configuration files to all servers.
-- Dean
On 2005-10-17 11:56, Tom Lane wrote:
Dean Gibson (DB Administrator) [EMAIL PROTECTED] writes:
The following query ran
What's the point of a binary search if the list is small enough to fit
on a line or two? And if a query can be substituted for N1-NN, you have
to read all the values anyway, and then the function is trivially
expressed as a normal query with no decrease in speed.
-- Dean
On Wed, 2005-10-12
, Dean Gibson (DB Administrator) wrote:
What's the point of a binary search if the list is small enough to fit on a
line or two? And if a query can be substituted for N1-NN, you have to read all
the values anyway, and then the function is trivially expressed as a normal
query with no decrease
Try (for simple cases):
DELETE FROM my.table WHERE somecondition;
INSERT INTO my.table (somefield) VALUES ('$someval');
In complex cases it may be necessary to INSERT the values into a
temporary table, which is then used to condition the DELETE before
INSERTing the temporary table into your
You can set up pg_hba.conf so that only certain Unix users that have
access to the local Unix PostgreSQL socket can access the database
without a password (every other process uses a TCP/IP connection); then
move the socket location to other than /tmp and restrict its access w/
Unix controls.
Simultaneous VACUUMs in tables in different schemas appear to interact.
Observed in v7.4.5 7.4.8 on Fedora Core 1.
Details:
I have a database consisting of several schemas. Two of these schemas
are contain eight tables each (about 700K rows each), which are
populated and updated daily via
Even simpler: COALESCE( a = b, a IS NULL AND b IS NULL )
-- Dean
Greg Stark wrote on 2004-09-27 08:17:
Stephan Szabo [EMAIL PROTECTED] writes:
On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason [EMAIL PROTECTED] wrote:
On a similar note, I've found myself wanting an extended '=' operator
On Thursday, Sept 18 Bruno Wolff said:
One option for you is to use the list address in the from header when
posting to the list. That will hide your address and not break
replies. Most likely the list checks the envelope sender address to see
whether or not the message needs moderator
38 matches
Mail list logo
