d the same way:
DROP table foo;
DROP sequence foo_id_seq;
DROPDB dbname will *always* delete everything in a database, assuming
you have permissions to use it.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(
more than a
> hour. My database has 6 MB.
>
> I can backup my database directory on Windows (using ZIP) and put it on Linux?
Might want to try pg_dump (rather than dumpall), perhaps even using the
-t option to pick just one table. This could let us find out if you can
dump *anything* or n
On Wed, 10 Oct 2001, Ian Barwick wrote:
> I'm doing some work for a smallish company which conducts
> its business largely online. Currently they have a legacy
> mishmash of Oracle and MySQL databases which they wish
> to unify one one platform (RDBMS with client access via
> browser and custom s
On Wed, Aug 06, 2003 at 12:55:52PM -0400, Joel Burton wrote:
Reposting, with some clarification to my request. Thanks to the several
responses I received originally.
Yes, I know that a perfectly vaild PGSQL SQL file could contain only
ANSI SQL and therefore not be recognized as PG-related. In
is smart enough to copy any output
(such as the RAISE NOTICE), my fear is that it is actually running
the function a second time.
Does anyone know if this caching actually happens yet, or is this a
future feature?
Thanks.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
On 19 Jul 2000, at 14:30, Tom Lane wrote:
> "Joel Burton" <[EMAIL PROTECTED]> writes:
> > I have a function that always returns the same answer given the same
> > input (no database lookups, etc.). The pg Users' Manual documents
> > the attribute
mn should be
different for each record *from the moment of inception*, this gives
Access something really unique to hang its hat on.
Works for me; let me know if it doesn't work for you.
--
Has anyone ever collected a FAQ of Access-on-Postgresql? I've got
a
few tips (nothing heavy, jus
way to build a patch file that
> > contains the changes? I have never done this before.
>
> "diff -c" against current sources, done so that the correct file
> pathnames are visible in the diff output; that is, cd to top level of
> distribution tree and do something like diff -c
> src/backend/utils/fmgr/dfmgr.c.orig src/backend/utils/fmgr/dfmgr.c
> Don't forget to include diffs for documentation updates, as well.
>
>regards, tom lane
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
Have you tried this with triggers? I've never done this w/rules, but
with triggers, I don't have this problem.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
talling PostgreSQL w/driver manager.
I love my other ODBC datasources, but reinstalled those. It's hardly
elegant, I'm sure they're are better ways, but, hey, it worked for
me. Your mileage may *definitely* vary, You've been warned, etc.,
etc.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
ail me (or this list,
or better still, the INTERFACES list), so it can be added to the FAQ?
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
if I have it correct:
. blobs cannot be dumped
. blobs are not normally vacuumed
So, for most of us, I think the TOAST feature of 7.1 that allows >8k
row sizes is much nicer. (Unless, of course, you really want to store
binary data, not just long text fields.)
Good luck,
--
Joel Burton,
On 30 Nov 2000, at 1:24, Igor V. Rafienko wrote:
> on Nov 29, 2000, 19:17, Joel Burton std::cout'ed:
>
> [snip]
>
> | > 5) BLOB Support.
> |
> | Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard |
> right) will support much longer row sizes
m able to add table and column descriptions, and I am also able
>to retrieve the table description. The problem is that I can not
>find a way to retrieve the description comments on table
>columns Any assistance would be greatly appreciated.
>
> Dale.
>
--
Joe
y SQL-92 compliant" sure isn't one of them. :-)
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
Yes, yes, I know about *some* of them [8k limit springs to mind!]
(C hackers no doubt no *lots* more.) But, in terms of, "as comes
out in our SQL syntax", compared to Oracle, we're free and clear.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
t reimports perfectly, but is less self-
documenting.
In the tiny-wishlist department, I would like it if there were an easy
way to change the referential integrity behavior (delete, block,
etc.) for an existing relationship.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
+rx this directory, and all directories above
this directory.
On our server, we've created a directory for pgsql copying and
dumping that is rwx for postgres and our DBAs.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
C++ to compile
this.
Via ftp, that's in the /dev directory.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
___ Get more from the Web. FREE MSN Explorer download :
> http://explorer.msn.com
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
(please CC me directly
> since I don't subscribe to this mailing list).
select yourdate - '-01-01'::date from yourtable;
seems to work just fine, and returns an integer.
Both PostgreSQL and MySQL say there have been 730485 between 1/1/0
and 1/1/2000. Something we all
asswd and /etc/shadow playing with my mind!
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
the datetime field, I believe.
Plus, this should be (slightly?) faster that a PLSQL routine.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
> Given UNIONs are not implemented within views in 7.0.3, what are
the
> generally suggested work-arounds, if any?
Ick. We just did the full SELECTs all the time. A pain.
FYI, this is fixed in 7.1. Beta Real Soon Now.
--
Joel Burton, Director of Information Systems -*- [EMAIL PRO
e answers can be found in the Pg+Access FAQ at
www.scw.org/pgaccess.
Good luck,
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
ccess are better in pgsql-interfaces,
which deals with interfaces to PostgreSQL.
good luck,
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
you instantly and selectively look at particular
parts of your db dump, rather than worming your way through yourself.
Better still:
You can also SELECT * FROM pg_rules WHERE rulename='my_rule';
(at least in 7.1 betas; don't remember about 7.0.x)
--
Joel Burton <[EMAIL PROTECT
e).
You can use ~*, you can also index on function results (eg, you can create
an index on a function. So, you could
create unique index yourtable_pkey on yourtable ( upper(pkeyfield));
which would only allow one 'Joel', regardless of how you cap me.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
I use PoPy and think it's dandy. I also thought it *was* the DB API 2.0--
am I mistaken here?
PyGreSQL is more commonly used, and has (IMHO) a simpler, more dict-like
interface, but isn't (AFAIK) thread-safe, nor DB API compliant.
--
Joel Burton <[EMAIL PROTECTED]>
Director o
ow using the default-only values.
I've been looking around for a miraculous SELECT
default_value(table.class) function, but can't seem to find it. Any ideas?
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
gets to the GUI app and back again, it
looks like '''f''::bool' to PG, so it sees it as text, not as the boolean
value false. Similarly, "current_user"() isn't resolved, etc.
So, one solution might be: is there a way to 'resolve' these before they
ou just
wrap them wiuth the double quotes.
(cf to the mess in MySQL, where some things are case-sensitive, and some
things aren't, and it varies based on whether the server OS is case
sensitive. Ick.)
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information
ow wonderful they are.
I hope this helps, and I do hope it doesn't sound condescending. I moved
to PostgreSQL from using MySQL, and for several months after first playing
with it, I just thought it was a bigger, more complicated database that
did 'the same stuff' as M
ld be
\c dbname is converted to lowercase
\c "DBname" is treated, case insensitive
(which is how everything else in the PG world works.)
But, to substitue this now would break existing apps, wouldn't it? ;-)
--
Joel Burton <[EMAIL PROTECTED]>
Director of Info
ct to PG. PG
has database connectors for all of these languages.
If you're stuck, perhaps it would help non-FM-users on the list for you to
give us a sense of what you can do w/your FM data.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center
he sophisticated features of PG?
Does anyone want to help put this together?
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
On Wed, 4 Apr 2001, Marcin Wasilewski wrote:
> hello everybody,
>
> Maybe someone could help me with ODBC setup with POSTGRESQL
>
> thanks,
> Marcin
FAQ at www.scw.org/pgaccess
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Supp
there's a command-line switch to not show them if not
neccssary, IIRC.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all list
> This runs OK. However, when I go to use it as follows:
>
> SELECT tester('sometext');
>
> I get the error:
>
> NOTICE: plpgsql: ERROR during compile of tester near line 1
> "RROR: parse error at or near "
If this is the actual function you used, then
QL problem, but something quick in Perl could do
this:
perl -pi.bak -e 's/php4/php/gi;' `find -name "*.php3"`
will iterate over all files in the current directory and below named
*.php3. It iterates over every line in the file, subbing php for php3.
Followups to a Perl li
lus sensitive password
information, and can be viewed only by the superuser.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
in together, to simulate
application- and web-usage.
In addition, did you wrap this in a transaction? Otherwise, you're
performing one transaction for *every single* insert, which is much slower
than in a a transaction.
(Generally speaking, if you want to just add 40,000 rows to a tabl
full-text indexing, cryptographic hash
functions (including
SHA1 and MD5), user-handled long-term cooperative locking, ISBN/ISSN
number types, and more. In
addition, there are many additional functions that can be downloaded from
web sites or from the
PostgreSQL discussion lists.
--
Joel Burton &
On Mon, 9 Apr 2001, Lincoln Yeoh wrote:
> At 05:30 AM 08-04-2001 -0400, Joel Burton wrote:
> >On Tue, 3 Apr 2001, Livio Righetti wrote:
> >> Also we used Postgresql for Radius (authentication) et we have to make 3
> >> vacuum per day otherwise the first server is
ype it returns?
Juan --
At present, there is no reasonable way for a procedure to return a result
set (like a table).
Could you write a view that returns the data that you want?
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---
---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
--
es websensebycat and netscapereduce do have indices on it.
> Netscapereduce has 60020 entries and websensebycat has around 6000 entries.
> Urlcounts has 55 entries. The whole thing is running postgres 7.1RC2.
> Anybody has a tip to accelerate this. The whole thing is running on a E250 with
); usually all that's needed
is just to edit the SQL statements to create/select/etc. from the schema.
If there is a calorie intake application, please don't tell me about it
;-)
Good luck!
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center
e conversion.
http://www.greatbridge.org/project/pgadmin/projdisplay.php
There's a useful FAQ of info on PostgreSQL + Access at
http://www.scw.org
Your English is *much* better than my Italian! :-)
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Supp
ce of every word
fragment, with a reference back to the row that contains it. Searching
against this is indexed, and is speedy. The only downside is that you will
have a *large* table holding the full text index.
More help can be found in the README file in contrib/fulltextindex
HTH,
--
ian book on the website -- it explains this very well.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
the unique
> index? I found that using PHP scripts to do inserts for a file of this size
> take MUCH longer than I'd like, so I'd like to avoid having to do it that
> way if I can. Any help is appreciated. Thanks!
There are a few options.
This was discussed yesterday, in the thre
ss client.
w/7.0.3, it was possible to update tables from Access, and it is in 7.1
If you don't have that experience, you probably have some settings wrong
in the ODBC driver. There's a FAQ at www.scw.org/pgaccess.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Inform
;
' LANGUAGE 'plpgsql';
(Reg_SeatsLeft() is a simple SQL function w/o any references to NEW or
OLD)
If I try to INSERT into this table, I get
ERROR: record old is unassigned yet
So, why hasn't the logic short-circuited? Am I missing something?
--
Joel Burton <[
w table to use the existing sequence, and not
create a new one. In our example, that would be:
CREATE TABLE pers_new (
id int not null default nextval('pers_id_seq'),
...
);
rather than just "id serial not null".
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Di
After re-creating, I re-dump the file to a new name:
$ pg_dump -S postgres > database1.sql
5) I compare the lengths the two files to make sure that they are
almost exactly the same. This is just to double-check to make
sure that pg_dump didn't screw anything up. If yo
', which returns a bunch of hits.
Good luck,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister comman
test=# create view its_really_the_same_thing as select true where 1
between 1 and 3;
test=# \d its_really_the_same_thing
...
View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support C
gt; > View definition: SELECT 't'::bool WHERE ((1 >= 1) AND (1 <= 3));
> > >
> > >
> > > HTH,
> > > --
> > > Joel Burton <[EMAIL PROTECTED]>
> > > Director of Information Systems, Support Center of Washington
> &
about 1.5 years ago in plpgsql (a pretty awful, nested, letter-by-letter
parser) was only about twice as slow as the perl
split() replacement. Looks like our little plpgsql is quite a speed demon.
(as always, IANAPB [ I am not a professional benchmarker ], YMMV)
--
Joel Burton <[EMAIL PROTECT
On Wed, 25 Apr 2001, Tom Lane wrote:
> Joel Burton <[EMAIL PROTECTED]> writes:
> > Last night, I was doing some amateurish benchmarking and found that,
> > contrary to my (admittedly uninformed) expectation, sql functions seem
> > *slower* than plsql functions.
>
other reason than it's confusing.
If you wrap them in double-quotes, you can use most reserved words as
system identifiers. But I wouldn't -- some cheesy client implementation
might choke on them, and better to not find that out later.
--
Joel Burton <[EMAIL PROTECTED]>
Dire
e Ref Int tutorial I just submitted at
techdocs.postgresql.org.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all lists at once with t
27;basics of Unix you need to know to be a decent DBA' stuff that's
already on the web, we'd have plenty.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
ws to Linux HOWTO
http://linuxdocs.org/HOWTOs/DOS-Win-to-Linux-HOWTO.html
4) The Linux Reading List HOWTO
http://linuxdocs.org/HOWTOs/Reading-List-HOWTO/index.html
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
-
he RPMs, which if you're using
RedHat, you probably are).
A less distribution-specific way of saying the same thing is
$ service postgres start
But, really, you probably want to do something like
$ pg_ctl
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems,
e trick, but slowly, and it's a pain to write out.
I'd use
BETWEEN 'A' AND 'FZZZ'
(or, to be more precise, >='A' and <'G')
Keep in mind that PostgreSQL is case-sensitive, so if me name were
'Joel deBurton', you woul
ing
> pgacess from a terminal window?
The fact that you're running pgaccess from a term window doesn't mean that
pgaccess connects to PG w/a local socket.
Someone may know how to configure pgaccess to use a local socket. If not,
use the -i switch, and edit $PGDATA/pg_hba.conf to block acces
I find detailed info on using ODBC with Access and PgSQL?
FAQ at www.scw.org/pgaccess
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all list
34 50
> 2 jeff 40 20
>
> Thanks
> Jeff Meeks
> [EMAIL PROTECTED]
SELECT id, name,
(SELECT sum(b) FROM b WHERE b.id=a.id) AS sum_b,
(SELECT sum(c) FROM c WHERE c.id=a.id) AS sum_c
FROM a;
--
Joel Burton <[EMAIL PROTECTED]>
impact on DB performance.
If you find a better way, please let me know.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
yntax.
You could, though, make a plpgsql function that dropped tables,
and took a text parameter that it used as a regex. Then you could
SELECT dev_drop_table('tblfoo.*').
(or, instead of regex-able param, do a like-able param)
--
Joel Burton <[EMAIL PROTECTED]>
Director of Info
s (yes, the -i option is on)... Is there a trick?
>
> Enable it in the postgresql.conf file. (The -i option should work.
> Odd...)
Perhaps the reason that -i didn't work is that the scripts use pg_ctl.
Don't the options for that need to be wrapped in -o, as in '-o &quo
e short story: if you've tried before and given up, try now.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all lists at once wit
ary key(id1,
id2));
create table c (id1 int, id2 int, foreign key (id1, id2) references p);
insert into p values (1,2);
insert into c values (1,1);
insert into c values (1,0);
ERROR: referential integrity violation - key referenced from c
not found in p
--
Joel Burton <[EMAIL PROTECT
ntrib.
And more info about it at techdocs.postgresql.org.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
utine:
create function bool(bit) returns bool as '
begin
if $1 = ''1''::bit
then
return true;
end if;
return false;
end;'
language 'plpgsql' with (isacachable);
should do the trick, albeit more slowly than a built-in or C func
a very nice Open Source database system.
for extra credit, s/Visual Basic/Python/g;
... and just think of the money you'll save!
Seriously though, this is a list for the discussion of issues around
PostgreSQL. Questions about SQL Server are not appropriate here.
--
Joel Burton
nt4 or integer type.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
table--it will have a different
OID, but that shouldn't matter, since all you care about is the LO oid.
However, I'm not a BLOB expert, and haven't used them in any real systems.
Do try this out on a test table before doing it for real.
HTH,
--
Joel Burton <[EMAIL
On Fri, 4 May 2001 [EMAIL PROTECTED] wrote:
> On Fri, May 04, 2001 at 12:47:02PM -0400, Joel Burton wrote:
> >
> > Hmmm... this raises an interesting question.
> >
> > Would it be possible to hook into (via trigger or other mechanism) so that
> > we could ex
On Fri, 4 May 2001 [EMAIL PROTECTED] wrote:
> On Fri, May 04, 2001 at 12:48:24PM -0400, Joel Burton wrote:
> >
> > What is the system call? Could you do this in pl/tclu, the
> > untrusted version of pl/tcl?
> >
>
> I don't know anything about pl/tcl and
82 matches
Mail list logo
