exactly what I should've done from the beginning.
Hope this helps -
Scott
On Jan 30, 2012, at 2:03 PM, Scott Frankel wrote:
Hi M,
On Jan 30, 2012, at 11:46 AM, A.M. wrote:
On Jan 30, 2012, at 2:40 PM, Scott Frankel wrote:
Hi all,
What's the best/correct way to cause
Hi all,
What's the best/correct way to cause the PostgreSQL server to startup
automatically when rebooting on OSX 10.7 Lion?
I'm using a macports install of postgres 8.4 and went through a couple grueling
days, sudo'd up to my eyeballs, to restore the postgres user and have a working
Hi M,
On Jan 30, 2012, at 11:46 AM, A.M. wrote:
On Jan 30, 2012, at 2:40 PM, Scott Frankel wrote:
Hi all,
What's the best/correct way to cause the PostgreSQL server to startup
automatically when rebooting on OSX 10.7 Lion?
I'm using a macports install of postgres 8.4 and went
Hi all,
Is there a way to pipe text into a COPY statement's stdin input using
cmd-line psql?
I'm using the following syntax to enter large strings of text into a
table. The text itself has a json-like syntax that has the potential
for carrying numerous special characters.
for Michael's
suggestion; eg:
cat myfile | psql -c COPY mytable (name, description, text) FROM
stdin
Thanks!
Scott
On Jun 21, 2011, at 1:10 PM, John R Pierce wrote:
On 06/21/11 12:43 PM, Scott Frankel wrote:
Hi all,
Is there a way to pipe text into a COPY statement's stdin input
using cmd
Hi all,
Is it possible to control the representation of boolean data in a
pg_dump?
The pg docs say that booleans can be stored as 't', 'true', 'y',
'yes', or '1'. My db is storing them as 't' and pg_dump is outputing
them as 'true'. Can I coerce pg_dump to output 't' as 1?
I'm using
On Aug 11, 2010, at 10:12 AM, Michael Glaesemann wrote:
On Aug 11, 2010, at 13:00 , Scott Frankel wrote:
The pg docs say that booleans can be stored as 't', 'true', 'y',
'yes', or '1'.
Booleans are not stored as those literals: those are only
acceptable literals (i.e., string
On Aug 11, 2010, at 3:57 PM, Michael Glaesemann wrote:
On Aug 11, 2010, at 18:21 , Scott Frankel wrote:
On Aug 11, 2010, at 10:12 AM, Michael Glaesemann wrote:
One option is to use COPY to export the data in a format you like.
For example: COPY (SELECT CAST(boolean_column AS INT) FROM
On Aug 8, 2010, at 2:45 AM, Torsten Zühlsdorff wrote:
Scott Frankel schrieb:
On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:
John Gage schrieb:
On reflection, I think what is needed is a handbook that features
cut and paste code to do the things with Postgres that people do
today
On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote:
John Gage schrieb:
On reflection, I think what is needed is a handbook that features
cut and paste code to do the things with Postgres that people do
today with MySQL.
Everyone of my trainees want such thing - for databases, for
Hi all,
What's the best way to insert long strings that contain numerous
special characters into a PG database?
I'm assuming that importing an SQL script with prepared statements is
the way to go. If so, how to escape all the special characters?
I've found documentation on
On Jul 30, 2010, at 1:13 AM, Richard Huxton wrote:
On 30/07/10 07:52, Scott Frankel wrote:
I have a number of very long strings that each contain many
instances of
semi-colons, single quotes, forward and back slashes, etc. I'm
looking
for an efficient and safe way to write them to my db
On Jul 30, 2010, at 9:11 AM, Richard Huxton wrote:
On 30/07/10 16:57, Scott Frankel wrote:
On Jul 30, 2010, at 1:13 AM, Richard Huxton wrote:
On 30/07/10 07:52, Scott Frankel wrote:
I have a number of very long strings that each contain many
instances of
semi-colons, single quotes
Hi all,
Is it possible to perform an SQL Dump without using pg_dump?
I have a special case situation wherein my application has access to a
remotely-hosted PG (8.3) database, but does not have access to its
admin tools. (There's a longer backstory here that I'm happy to
explain if
Thanks for all the suggestions. COPY may work for my purposes. The
SSH tunnel option for using pg_dump is very interesting.
Thanks!
Scott
On Jul 26, 2010, at 9:18 AM, David Fetter wrote:
On Mon, Jul 26, 2010 at 08:58:59AM -0700, Scott Frankel wrote:
Hi all,
Is it possible
Hi all,
I'm working with prepared statements directly in pg for the first time
and have a couple of questions.
Does a prepared statement used to insert into a table need to insert
into all columns of the table? I've found that, for a table with a
serial sequence key as its first column,
Works! The bug in my example was not passing the INSERT statement an
explicit list of column names, as per any non-prepared insert.
Thanks!
Scott
On Jul 23, 2010, at 2:53 PM, Daniel Verite wrote:
Scott Frankel wrote:
I've found that, for a table with a
serial sequence key
Excellent! Thanks for providing the link. I think the 'add columns
and move data' option would best fit my needs.
Thanks!
Scott
On Feb 2, 2010, at 11:44 PM, Shoaib Mir wrote:
On Wed, Feb 3, 2010 at 4:14 PM, Scott Frankel
fran...@circlesfx.com wrote:
Hi all,
Is it possible
Hi all,
Is it possible to specify a position when adding a column to a table?
I want to swap one column for another without losing the column's
position. eg: given that 'foo' is the 5th column in an 8 column
table, I want to replace it with a 'bar' column at column 5.
ALTER
Hi all,
What's the proper way to store directory path strings in a table,
especially ones with backslashes like windows?
I'm currently using a prepared statement with bind value. Do I need
to pre-parse all user entries to identify any backslash characters
before passing the string to
Excellent! Mild testing so far, but it seems to work. Thanks!
Scott
On Jan 29, 2010, at 3:00 PM, Cédric Villemain wrote:
2010/1/29 Scott Frankel lekn...@pacbell.net:
Hi all,
What's the proper way to store directory path strings in a table,
especially
ones with backslashes like
Hi all,
Is there a query I can use to find the location of a db cluster?
I've found a term that looks promising (\d+ pg_database;), but can't
seem to tease a directory path from it. The docs list several common
locations, but mine doesn't appear to be one of them.
Searching my local
Hi all,
Is there a query I can use to find the location of a db cluster?
I've found a term that looks promising (\d+ pg_database;), but can't
seem to tease a directory path from it. The docs list several common
locations, but mine doesn't appear to be one of them.
Searching my local
Exactly what I was looking for. Thanks!
On Jan 21, 2010, at 10:50 AM, Thomas Kellerer wrote:
Scott Frankel wrote on 21.01.2010 18:34:
Hi all,
Is there a query I can use to find the location of a db cluster?
SELECT name,
setting
FROM pg_settings
WHERE category = 'File Locations
Hello,
Is it possible to perform a select in the where clause of a statement?
I have a situation where I've got one arm tied behind my back: I can
only have a single table in the select and from clauses, but the where
clause appears to be freed from that restriction.
Given a statement
Hello,
Is it possible to join tables in the where clause of a statement?
I ask because I have a situation where I only have access to the where
clause of a select statement on a single table, yet I want to perform
a join on multiple tables. eg:
Given a statement as follows:
SELECT
On Sep 8, 2009, at 4:02 PM, David W Noon wrote:
On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL]
where clauses and multiple tables:
Is it possible to join tables in the where clause of a statement?
[snip]
Given a statement as follows:
SELECT foo.foo_id, foo.name
Hello,
Is it possible to perform selects in a where clause of a statement?
Given a statement as follows:
SELECT foo.foo_id, foo.name
FROM foo, bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';
I'm looking for a way to recast it so that the select and from clauses
Hello,
Is it possible to perform selects in a where clause of a statement?
Given a statement as follows:
SELECT foo.foo_id, foo.name
FROM foo, bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';
I'm looking for a way to recast it so that the select and from clauses
Thanks for the thorough explanation and link to more docs. Very much
appreciated!
Scott
On Aug 24, 2009, at 11:03 AM, Raymond O'Donnell wrote:
On 24/08/2009 18:37, Scott Frankel wrote:
If I understand how tables are managed internally, there are 2
sequences: my explicit foo_id
Hello,
Is it possible to select or otherwise view a table's primary key values?
I'm troubleshooting the following error:
ERROR: duplicate key value violates unique constraint foo_pkey
The insert that yields the error seems innocuous enough:
INSERT INTO foo (color_id,
Hi Ray,
On Aug 24, 2009, at 9:48 AM, Raymond O'Donnell wrote:
On 24/08/2009 17:31, Scott Frankel wrote:
The insert that yields the error seems innocuous enough:
INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019,
2);
It seems as if there's a sequence (foo_pkey) that's
Got it! Yes, this started happening after loading from a pg_dump.
Thanks for the explanation!
Scott
On Aug 24, 2009, at 10:52 AM, Tom Lane wrote:
Scott Frankel lekn...@pacbell.net writes:
Is there a command that lists the values for the internal,
foo_foo_id_seq, sequence?
select
-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Scott Frankel
President/VFX Supervisor
Circle-S Studios
510-339-7477 (o)
510-332-2990 (c)
--
Sent via pgsql-general mailing list (pgsql-general
an integer value
FATAL: unrecognized configuration parameter
default_text_search_config
Thanks again!
Scott
On Jul 6, 2008, at 10:48 AM, Tom Lane wrote:
Scott Frankel [EMAIL PROTECTED] writes:
When I try to start 8.3, the log file lists a fatal error in the
postgresql.conf file
to version 8.1 as I've run into a bug in Qt's
QPSQL driver. I'm able to create tables and add rows of data to them;
but my model.select() statements all fail. The This version of
PostgreSQL is not supported and may not work message is ominous ;)
Thanks in advance!
Scott
Scott Frankel
platforms. They are written to a local disk and are accessed
directly from that location.
Thanks for the info!
Scott
On Dec 1, 2005, at 8:02 PM, Tom Lane wrote:
Scott Frankel [EMAIL PROTECTED] writes:
Yes, the tar file contains a file called 1765.dat. A `cat` of that
file shows nothing
What does this error mean?
pg_restore: [tar archiver] could not find header for file 1765.dat
in tar archive
All of a sudden, I'm encountering this error as I debug a problem
with the db interface I'm using. My backups are created using a
script that hasn't changed recently, though I
if the pg_restore error is due to my
upgrade to postgres 8.1 or macosx 10.4.3.
Thanks
Scott
On Dec 1, 2005, at 4:47 PM, Tom Lane wrote:
Scott Frankel [EMAIL PROTECTED] writes:
What does this error mean?
pg_restore: [tar archiver] could not find header for file 1765.dat
in tar archive
Voila! Xcode v2.1 worked.
Thanks!
Scott
On Nov 9, 2005, at 2:22 PM, Tom Lane wrote:
Scott Frankel [EMAIL PROTECTED] writes:
My build of postgresql-8.1.0 is failing on MacOSX.
/usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0)
file: -lSystem is not an object file
I just upgraded from postgres 7.x.x to 8.1.0 and am getting the following error from cmd-line psql:could not save history to file "my_home_dir/.psql_history": Invalid argumentSearching the archives, I found a thread about this error wherein the poster "solved" the problem by commenting out the
OK. Thanks!
Scott
On Nov 10, 2005, at 10:20 AM, Tom Lane wrote:
Scott Frankel [EMAIL PROTECTED] writes:
[ Mac OS X ]
could not save history to file my_home_dir/.psql_history:
Invalid argument
Searching the archives, I found a thread about this error wherein the
poster solved
After upgrading from postgres 7.x.x to 8.1.0, my database restore
produces the following error msgs. The database is quite pedestrian:
a dozen or so tables, couple of triggers, some comments, c. Note
that even though I get the following errors, a '\dt+' returns the
comment strings.
Voila, 2. Executed as superuser and, yes, no errors.
Thanks again!
Scott
On Nov 10, 2005, at 3:51 PM, Tom Lane wrote:
Scott Frankel [EMAIL PROTECTED] writes:
After upgrading from postgres 7.x.x to 8.1.0, my database restore
produces the following error msgs.
SCHEMA public postgres
I'm no expert, but I did just accomplish a restore from backup; so at
least I can help you with your second question.
pg_dump is the animal you'll want to use to create your backup. I
use it wrapped in a script that automatically timestamps and names
the output appropriately. Args
My build of postgresql-8.1.0 is failing on MacOSX. Following the
INSTALL file, I passed configure flags for bonjour (what's that?) and
python support. i.e.:
./configure --with-bonjour --with-python
My invocation of make, launched from the postgres account, returns
the following
After a server crash forced a reboot, `pg_ctl start` fails with a FATAL
error. Log output says that the lock file, postmaster.pid, already
exists.
Can I just su to root and delete the .pid file to relaunch? Or will
this have nasty side-effects?
Thanks in advance!
Scott
Bingo -
Thanks!
On May 23, 2005, at 1:14 PM, Scott Marlowe wrote:
On Mon, 2005-05-23 at 14:58, Scott Frankel wrote:
After a server crash forced a reboot, `pg_ctl start` fails with a
FATAL
error. Log output says that the lock file, postmaster.pid, already
exists.
Can I just su to root
Though I haven't embarked on any expeditions into the world of printing
yet, I've found Python to be a terrific language for db access. My
solution uses Apple hardware, OSX, postgres, the psycopg programming
interface to Python, Python, and my slowboat hacking with wxPython to
build a GUI
| mono
2 | 3 | mono
(2 rows)
On Apr 27, 2005, at 1:20 PM, Scott Frankel wrote:
I am trying to construct a rule that performs an UPDATE if specific
conditions are met in an INSERT statement. Limiting UPDATE's SET
action to just the new row by testing for the new primary key
My original post got eaten. Apologies in advance if you receive this
message twice.
I am trying to construct a rule that performs an UPDATE if specific
conditions are met in an INSERT statement. Limiting UPDATE's SET
action to just the new row by testing for the new primary key is
failing
Close. Thanks for the very helpful suggestions!
As I read the doco on rules and dissect the rule I've constructed, one
issue
remains: the UPDATE in my rule causes additional rows to be added to
the parent table. How is that possible? How can it be suppressed?
i.e.: My rule specifies that
On Mar 23, 2005, at 2:42 PM, Klint Gore wrote:
Rows inserted into inherited tables are visible to the parent. It's
effectively the same as having a union all on the 2 tables. Using the
only qualifier is how you stop the union happening.
This explains it.
Thanks!
Scott
This is weird. I have two tables: one inherits from the other. And I
have a
rule that populates the inherited table with changes from the first.
When I
update a row in the first table, I get an ever-larger number of rows
added to
both it and the inherited table. i.e.:
update 1
);
What does GUC stand for? ;)
Thanks!
Scott
On Mar 22, 2005, at 6:55 PM, Stephan Szabo wrote:
On Tue, 22 Mar 2005, Scott Frankel wrote:
This is weird. I have two tables: one inherits from the other. And
I
have a
rule that populates the inherited table with changes from the first.
When I
update a row
INSERT INTO
dm_user_history SELECT * FROM dm_user WHERE id = old.id;
CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE
dm_user SET active_flag = FALSE WHERE id = old.id;
Scott Frankel [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Is there a canonical form that db
Is there a canonical form that db schema designers use
to save changes to the data in their databases?
For example, given a table with rows of data, if I UPDATE
a field in a row, the previous value is lost. If I wanted to
track the changes to my data over time, it occurs to me that
I could,
1)
Got it. Thanks!
Scott
On Mar 1, 2005, at 10:52 PM, Ragnar Hafstað wrote:
On Tue, 2005-03-01 at 16:51 -0800, Scott Frankel wrote:
Sweet! And not so sweet.
The natural join worked beautifully with my test schema; but it failed
to yield any rows with my real-world schema. I think I've tracked
I want to return all records that match criteria across three separate
tables
and , in spite of reading up on joins, have so far been unable to
design a
solution that doesn't require caching a hash table of intermediate
results.
Here's the situation:
Let's say color names belong to a set of
statement to function properly? Or am I SOL?
Thanks again!
Scott
On Mar 1, 2005, at 2:28 PM, Ragnar Hafstað wrote:
On Tue, 2005-03-01 at 13:42 -0800, Scott Frankel wrote:
[snip problem]
Task: find all color names in each of palette1's tones.
Can this be done in a single SQL statement?
[snip
My first schema design has passed all the tests I've thrown it so far,
'cept one -- and a
simple one at that. I wonder if the following boils down to a question
of query construction
or if I need to redesign my schema.
Consider the (contrived) example of 3 universities, where each hosts a
I want to ensure data integrity when inserting into a table, preventing
multiple
entries of identical rows of data.
Does this call for using a trigger?
How would triggers perform a query to test if data already exists in
the table?
(The doco outlines how triggers perform tests on NEW data
declaration of the first solution? Or is there a functional
difference between
them that I should choose between?
Thanks again!
Scott
On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote:
I want to ensure data integrity when inserting into a table,
preventing multiple
entries of identical rows of data
On Nov 12, 2004, at 8:24 AM, Michael Fuhr wrote:
[Top-posting fixed]
On Fri, Nov 12, 2004 at 09:06:08AM -0500, Goutam Paruchuri wrote:
Scott Frankel wrote:
ORDER BY DESC LIMIT 1 is much simpler and more readable than a
sub-query. Though the sub-query approach looks to be a good template
How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?
i.e.: given the following two tables, I want to replace the color_id
of 1
with the color_name 'red.' (The SQL to create the two tables follows
below.)
Still too new to SQL to have run across this yet ...
How does one return the latest row from a table, given multiple entries
of varying data?
i.e.: given a table that looks like this:
color | date
+
red| 2004-01-19
blue | 2004-05-24
red| 2004-04-12
blue |
On Nov 11, 2004, at 5:09 PM, Michael Glaesemann wrote:
Scott,
On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote:
color | date
+
red| 2004-01-19
blue | 2004-05-24
red| 2004-04-12
blue | 2004-05-24
How do I select the most recent entry for 'red'?
SELECT color, MAX
ORDER BY DESC LIMIT 1 is much simpler and more readable than a
sub-query.
Though the sub-query approach looks to be a good template for ensuring
more
accurate results by being more explicit.
Thanks to all who responded!
Scott
SELECT * FROM colortable WHERE color = 'red' ORDER BY date DESC
I'm attempting to debug a script that should perform a simple INSERT of
values,
but for some reason doesn't. The insert appears to occur without
error, printing
INSERT 18015 1 upon completion. Nonetheless, no data values appear
to be
added to the table when queried in psql.
Questions:
- What
I should have *myself* committed.
Thanks for the suggestions (and OID tip)! It turned out that my script
was
not committing the transaction, so the insert was getting rolled-back.
Thanks
Scott
On Oct 26, 2004, at 12:39 PM, Scott Frankel wrote:
I'm attempting to debug a script that should
Sorry to burden this list (again) with a psycopg question.
I'm stuck at a table creation step and am not getting results
from their list.
Question: what's the appropriate syntax for specifying a primary
key?
My very simple table creation test (based on their first.py
example is failing ...
Ack! I sent this msg on October 14. Since then, I've gotten the
primary
key stuff working.
Please disregard this msg.
Thanks
Scott
On Oct 14, 2004, at 4:16 PM, Scott Frankel wrote:
Sorry to burden this list (again) with a psycopg question.
I'm stuck at a table creation step and am not getting
I recently installed PostGreSQL-7.4.5 on my OSX 10.3.5 system. I did not, however
have the problem you're encountering. There was no postgres user already
created on my system.
1. It's not like postgres just rolls off the tongue. It's hard to imagine another user of
your system choosing that
This is a test -- please ignore
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
First, apologies in advance for this somewhat OT post ...
I'm looking for a source of information on using the psycopg
interface to postgresql. A mailing list would be ideal. I've
poked at their wiki, but not found what I'm looking for.
Also, new to both postrgresql psycopg, my questions appear
If I understand your description of the problem sufficiently, I believe
you're
running into trouble at the su postres step.
Although I'm quite new to postgres, I do know that creating a user
account
named postgres is recommended, if not required, by the installation
process.
Entering su
I'd like to know if anyone has recommendations for which Python DB-API
2.0
interface to use with PostGreSQL-7.4.5.
The database and tools to interact with it will be hosted on a MacOS
10.3.x machine.
The db schema represents a small production studio environment with
only a handful
of users.
)
- my ability to pronounce its name ;)
Now back to the schema ...
Thanks
Scott
On Oct 6, 2004, at 9:13 PM, Scott Frankel wrote:
I'd like to know if anyone has recommendations for which Python DB-API
2.0
interface to use with PostGreSQL-7.4.5.
The database and tools to interact
I'm looking for the file postgres.h in my recent install of
postgres-7.4.5 on
a MacOS 10.3.5 system.
I'm attempting to build PyGreSQL-3.5, which appears to require the
postgres
include dir.
My build of postgres-7.4.5 did produce an include dir,
/usr/local/pgsql/include;
but it does not
Default now installs all headers ... with v8.0 of postgres, right?
New to postgres (and db's for that matter) I just downloaded the
tarball for postgres-7.4.5. Invoking the make install-all-headers
cmd did indeed produce the servers subdir under include.
Unfortunately, my attempts to build
Additional note: configure warned of an old version of bison when I
attempted an install of postgresql7.4.5 the other day. Seems the
version
that comes with OSX 10.3.x is too old for postgres.
Only trick I encountered in installing bison from tarball was locating
the
install product in
Good to know ...
Scott
On Oct 1, 2004, at 9:08 AM, Doug McNaught wrote:
Scott Frankel [EMAIL PROTECTED] writes:
Additional note: configure warned of an old version of bison when I
attempted an install of postgresql7.4.5 the other day. Seems the
version
that comes with OSX 10.3.x is too old
I am attempting to install postgresql7.4.5 on OSX 10.3.5 and have
questions about
readline.
For use with Python, I have readline.so installed in
/Library/Python/2.3/.
- Is this manifestation of readline sufficient/appropriate for
postgres?
- If not, what do I need and where do I get it from?
83 matches
Mail list logo