On 2013-12-18 10:41, Kevin Grittner wrote:
Dean Gibson (DB Administrator) 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 valid.
Ah ha! T
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.
Hi all
I am no database wizard so I am hoping someone may be able to assist me :)
I get a daily CSV file of 6.5 million records. I create a temporary
table and COPY them in. On completion I create an index on the mdnid
column. This column is also indexed in table 2. This part is very
fast. I had
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
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 col_n
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, befor
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" serv
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" servers are
(streaming) replicating just fine from the primary DB server. If the
primary fails and
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 ignore
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 u
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
> mmd
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 SQL
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-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 bet
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 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
Act
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 ... S
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
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 mi
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/in
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 outp
gsql/
-- Dean
On 2008-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 --sysco
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
How do i remove this user?I am running on Win XP here.
Joshua D. Drake wrote:
Gibson wrote:
I seem to get this error "user postgres could not be created, user
account already exists" when trying to install postgres. Anyone knows
how to fix this?
Sounds to me like the user postgres
I seem to get this error "user postgres could not be created, user
account already exists" when trying to install postgres. Anyone knows
how to fix this?
Erik Jones wrote:
Erik Jones wrote:
Ok, consider the following table definition:
CREATE TABLE stats (
id SERIAL PRIMARY KEY,
hits bigint d
ebsite or link where i can see
the VB.net or C# code that does the SQL insert of the jpeg file into the
"Oid" data type column and also how to do a SQL select to retrieve and
display the image on a web form? Thanks
--
Cheers
Gibson Tang
Game Programmer
179, River Valley B
Mark Gibson wrote:
I have unexpected results when trying to cast a string to a timestamp:
test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5'
test-# ;
timestamptz
2006-06-24 20:00:00-05
Seems that what I get is about 10 hours earl
I have unexpected results when trying to cast a string to a timestamp:
test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5'
test-# ;
timestamptz
2006-06-24 20:00:00-05
Seems that what I get is about 10 hours earlier than I expect...
Any ideas why
Michael Glaesemann wrote:
On Jun 25, 2006, at 14:23 , Mark Gibson wrote:
SELECT play_length - INTERVAL 'play_seconds seconds' ...
The column isn't interpolated into the string. Try
SELECT play_length - play_seconds * INTERVAL '1 second'
That wor
If play_length is a timestamp, I can do this:
SELECT play_length - INTERVAL '13 seconds' ...
But what if play_seconds is a column?
SELECT play_length - INTERVAL 'play_seconds seconds' ...
This doesn't work.
ERROR: invalid input syntax for type interval: "play_seconds seconds"
Can anyone
Scott Marlowe wrote:
If you need a unique constraint on the text field anyway, and it's a
natural key, you're generally better of using that field as the pk.
However, if it's not a natually unique key, then it shouldn't be the pk,
and int is a perhaps better choice.
There are two VERY opposit
Is there a disadvantage to having the primary key for a table be a text
type vs. an integer type? Performance? Any difference between having a
varchar or char as a primary key?
My instinct tells me that an integer is preferred, but I'm looking for a
more concrete answer.
Thanks,
Mark
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 characters
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 a
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
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 is
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 to
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:
field-1 value
field-2 value
etc. The user would be responsible for adding the enclosing XML.
NULL value
"_OperatorClass" (cost=0.00..1.07
rows=1 width=13)
Filter: (class_id = $0)
Sorry about the post to pgsql-general; since this appeared to be a 8.0
regression, I posted it there. I guess I should subscribe to
pgsql-perform ... ???
If/when you think this will
o queries aren't the same. 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,
opposed to about
fifteen minutes under 7.4.8.
-- 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
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 applic
hu, 2005-10-13 at 11:30, 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 no
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 a
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. De
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
Newbie - please help me choose a direction.
I want to know what would be the best front-end app/language to use for
postgresql for exact form replication and ease of use. PHP, Python,
Java, Rekall (the Kompany), other? All suggestions welcome and appreciated.
Background---
I
Andrew Sullivan wrote:
On Wed, Oct 06, 2004 at 05:25:58PM +0100, Mark Gibson wrote:
I had to remove Slony's schema manually as I was having problems
with it. I was in the process of removing all Slony related stuff,
and all my slave tables when this problem occurred, and was going to
start
Tom Lane wrote:
Mark Gibson <[EMAIL PROTECTED]> writes:
Right then, I think I've got this sorted,
DROP TABLE worked after a swift:
DELETE FROM pg_depend WHERE objid = 5173132;
There's something awfully flaky going on here. The system should never
have let you get into this st
Mark Gibson wrote:
I'm guessing I'm gonna have to route through pg_catalog for this and
delete all deps manually, but it this going to be safe?
Would I be better off dumping and restoring the whole database?
Right then, I think I've got this sorted,
DROP TABLE worked after a swi
Tom Lane wrote:
Mark Gibson <[EMAIL PROTECTED]> writes:
I kept getting the following error:
ERROR: cache lookup failed for relation 4667548
This implies that something someplace still has a link to the table with
that OID. You could do "\set VERBOSITY verbose" so that the c
Ying Lu wrote:
Hello,
In mysql, we use "show processlist" to see all current process. Could
someone let me know in PostgreSQL, what commands that we can check the
current connections and processes please?
SELECT * FROM pg_catalog.pg_stat_activity;
--
Mark Gibson
Web Developer &
hema
using the pg_catalog tables?
Is there any other information I should provide that may help?
Specs:
Redhat Enterprise Linux 3
PostgreSQL 7.4.5
Slony-I 1.0.2
Cheers
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(en
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 '=' op
Google Mike wrote:
How do I list the schema for a table or procedure? Is there a command
I can do in psql to list this, or do I have to join a series of tables
to see that?
You can do a "pg_dump -s " to see the full schema of the
database. To see just a table of the database issue the command
"p
easily delete the person in
tbl_everyone and insert it again in tbl_employees...
Have you tried deferred constraints, eg:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
DELETE ...;
INSERT ...;
END;
I've haven't had chance to test this, but I think this could
be what you're looking for.
--
M
Mark Gibson wrote:
Hi,
I'm having intermittent problems connecting to my PostgreSQL database
from PHP, using Kerberos credentials forwarded from mod_auth_kerb.
[snip]
The trouble is that sometimes the connection works,
and sometimes it doesn't. It's very unpredictable. :(
ot;;
$db = pg_connect($connstr);
$res = pg_query($db,"SELECT current_user, session_user;");
print_r(pg_fetch_all($res));
pg_close($db);
?>
Cheers
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
of array's:
Is there any way to specify array slices from a subscript to the
beginning or end of an array?
eg:
element to end: array[5:*]
beginning to element: array[*:5]
At present it is possible by using an extreme +ve or -ve subscript
value, but this isn't nice.
C
ns?
The only thing you can do is change your column name.
The manual tells you about the system columns here:
http://www.postgresql.org/docs/7.4/static/ddl-system-columns.html
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(en
Hi, all.
We are running PostgreSQL 7.2.2 (7.4.1 in a few weeks). We have a
pesky legacy application which periodically leaves open idle
connections. (I know... fix the application). In the short term, I
would like to find out if the database engine has a time-out
configuration option for i
Doug McNaught wrote:
John Gibson <[EMAIL PROTECTED]> writes:
Assuming similar memory and disk sub-systems, I am considering a Quad
Xeon system vs. a Dual Itanium for PostgreSQL. I believe that the
PostgreSQL code is written for 32 bit and not optimized for the 64 bit
Itanium cpu. That
JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '{schema_name}' AND
c.relname = '{table_name}' AND
a.attisdropped = false AND
a.attnum > 0
Replace {schema_name} and {table
Mark Gibson wrote:
You need to quote the table name if it contains upper case or strange
characters:
SELECT "companyID" FROM app;
Obviously I meant column name, but it applies to any object identifier ;)
--
Mark Gibson <[EMAIL PROTECTED]>
Web Developer & Database Admi
Postgres 7.4.1.
Regards,
Iker
You need to quote the table name if it contains upper case or strange
characters:
SELECT "companyID" FROM app;
--
Mark Gibson <[EMAIL PROTECTED]>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
actical).
Is this a limitation of PostgreSQL or dblink?
Could dblink use type names instead of oid's?
If not, could dblink be adapted to use some kind of
remote oid -> local oid mapping table for datatypes?
I would be willing to have a poke around in dblink.c,
if someone could confirm my findi
Hi, all.
I have a table which is continually updated with the latest totals. I
would like to take snapshots of some of the data in that table and store
it in a second table to run statistics on it later. What might some
ways of doing this be?
Illustrative (I hope) example using fruit-qty-on-
"Linux" training is not standardized by any measure either. Lots of
companies and "institutions" offer their own training courses. Some
of these grow to be fairly well recognized and are offered in similar
form repeatedly in different locations, but that is not
"standardized" in the sense y
Hi, all.
Re: version 7.2.2
I modified the postgresql.conf file as below and got "really neat"
debugging turned on. :)
pg_ctl reload -D
Unfortunately after I was done, I wanted to turn it off again. I
commented out the lines and ran the "reload" again. This did not have
any effect. So, I
that can be configured to do it in rewriting
rules. I used to use Sendmail, but am now using Postfix. Suggestions welcome!
-- Dean Gibson
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Greetings to the list from DownUnder!
I have created a number of triggers and do not remember exactly what I
called them. How do I list the triggers (to refint.c) I have created and
which tables they reference etc.??
-
ANDREW GIBSON
Hi,
Has anyone achieved Macintosh connectivity to PostgreSQL?
If so, any details would be appreciated i.e. which ODBC driver
Thanks,
-
ANDREW GIBSON (Director - Information Services)
Cannon Hill Anglican College http
Hi,
Has anyone achieved Macintosh connectivity to PostgreSQL?
If so, any details would be appreciated i.e. which ODBC driver
Thanks,
-
ANDREW GIBSON (Director - Information Services)
Cannon Hill Anglican College http
74 matches
Mail list logo
