Hello everybody.
I'm trying to migrate our data mine from a MS Access backend to a
postgresql backend, and I'm bumping into problems with the conversion.
I've got the basic db and tables set up. I want to transfer data from
one table to another with insert into ... select from. The from table
Hey guys. This is probably a n00b question, but here goes anyway.
I have a set of csv files that I COPY t o a number of import tables
(same field layout as live tables, but with all datatypes 'text') then
use an INSERT INTO ... SELECT FROM statement to transfer the rows over
to the live table
hope this helps somebody, I know I could have used this information
about 20 minutes ago :-)
Tom Hart wrote:
Hey guys. This is probably a n00b question, but here goes anyway.
I have a set of csv files that I COPY t o a number of import tables
(same field layout as live tables, but with all
andy wrote:
Tom Hart wrote:
[snip]
OK, enough of the background, here's my issue. For some lovely
reason, even though my script reports running an UPDATE query 1563
times (out of 1566 rows), only 316 rows have is_ok set to TRUE. I've
tried a few times, changing this and that, and
Hey everybody. I'm running postgresql 8.2 on a windows 2k3 server
machine. I have a table (two tables actually, in table and table_import
format). The _import table has all text type fields, while the main
table has datatypes defined.
I wrote a PHP script that checks the various type fields (e
I'm sure you guys have heard this about 100 times, and I've done some
research on Google and found out some things, but I still have a couple
questions.
As I'm sure you may have guessed from the subject, I'm trying to
schedule (under windows) pg_dumpall to run each night/morning/full
moon/wha
Steve Atkins wrote:
On Nov 9, 2007, at 8:52 AM, Tom Hart wrote:
I'm sure you guys have heard this about 100 times, and I've done some
research on Google and found out some things, but I still have a
couple questions.
As I'm sure you may have guessed from the subject
Tom Lane wrote:
Tom Hart <[EMAIL PROTECTED]> writes:
Do scheduled tasks
have a problem writing to files with > ? I tried an "echo this is a test
test.txt" and that didn't run either.
I know zip about Windows, but try using pg_dump's -f switch inst
[EMAIL PROTECTED] wrote:
One other oddity I ran into recently on one system only, is that a
perl program (.pl extension) worked fine from the command-line,
double-clicked on, etc. but in the scheduled job, I had to put "perl
xxx.pl" for it to work. Maybe if all else fails, try "cmd.exe
xxx.ba
Martin Gainty wrote:
Hi Tom
In windows you would need to include the runas command so your bat file
needs to execute the command via runas as in
runas /user:postgres "bin\pg_dumpall.exe -U foo_postgres"
HTH
Martin
Actually, schtasks.exe and the windows task scheduler both have this
functionali
Tom Hart wrote:
[EMAIL PROTECTED] wrote:
One other oddity I ran into recently on one system only, is that a
perl program (.pl extension) worked fine from the command-line,
double-clicked on, etc. but in the scheduled job, I had to put "perl
xxx.pl" for it to work. Maybe if all
Sam Mason wrote:
On Fri, Nov 16, 2007 at 03:13:48PM -0500, Tom Hart wrote:
column "isactive" of relation "membermailingaddress" does not exist
Table "public.membermailingaddress"
Column | Type | Modifiers
-+-
Hey everybody. I've got a strange one today. I'm trying to convert an
extremely messy access sql query into something that can be used with
our postgresql database (the pgsql db is being built to replace the
access db). I had barely begun trying to convert it when I was
confronted with this err
Hello everybody. I'm having a bit of trouble automating pg_dumpall to do
nightly backups. I have a batch file whose contents are below
SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf
"C:\Program Files\PostgreSQL\8.2\bin\pg_dumpall.exe" -U foo_postgres >
C:\foo\bar\PG_BACKUP\db.out
Hey guys. I have a long piece of sql that I'm trying to take out of an
existing Access db and modify to work with Postgresql. I've started
trying to convert it, but I've come across a problem that I don't even
know how to describe, let alone google. Here's the function
INSERT INTO MemberMailin
Peter Eisentraut wrote:
Tom Hart wrote:
Specifically I'm looking at these two lines
isactive and (mb_mail_cd=0 or mb_mail_cd=1) as ismail,
ismail and (mb_stat_cd=0 or mb_stat_cd=2) as ispromomail,
which appear to use other fields it's preparing to insert as variables
in the det
Tom Hart wrote:
Peter Eisentraut wrote:
Tom Hart wrote:
Specifically I'm looking at these two lines
isactive and (mb_mail_cd=0 or mb_mail_cd=1) as ismail,
ismail and (mb_stat_cd=0 or mb_stat_cd=2) as ispromomail,
which appear to use other fields it's preparing to insert as variab
Joshua D. Drake wrote:
in the least.
If you post publicly something that is that ugly, then it is going to
get critiqued. It is that simple. You don't like it, don't post.
I have more than once taken my beatings on this list. It is time for
you to either grow a thicker skin or unsubscribe.
Jo
Shane Ambler wrote:
Tom Hart wrote:
Hey guys. I have a long piece of sql that I'm trying to take out of
an existing Access db and modify to work with Postgresql. I've
started trying to convert it, but I've come across a problem that I
don't even know how to describe, let
Reply from 127.0.0.1: bytes=32 time=51ms TTL=241
Josh Harrison wrote:
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---(end of broadcast)--
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql. I've googled enough to find out that pgsql doesn't
have a built in enum type (it's of course possible that what I read was
outdat
Hi again everybody. I've got kind of a weird one today (the more
complicated my db gets, the stranger the questions). I've built a data
mine backend to replace our access data mine (eww, I know), but we'd
like to still be able to use access to get to the tables, and write
queries/reports/etc. a
Bruce Momjian wrote:
Tom Hart wrote:
On a side note, I was just reading through the 8.3 changelog, (where I
read about the enum datatype) and I noticed this line
*
Widen the MONEY data type to 64 bits (D'Arcy Cain)
This greatly increases the range of supported
Peter Eisentraut wrote:
Tom Hart wrote:
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql.
If it's an option, upgra
Tom Hart wrote:
Peter Eisentraut wrote:
Tom Hart wrote:
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql.
If it's
st=0.00..30953.68 rows=69239
width=434) (actual time=0.146..2974.609 rows=68913 loops=1)
Filter: is_ok
Total runtime: 179091.119 ms
(3 rows)
The query is inserting ~70,000 rows into a table with ~1.8 million rows
already in it. Anybody have any idea how I can keep this query fr
)
SELECT
iq_numeric("tr_acct_num"),
"tr_acct_typ",
iq_numeric("tr_atm_rec"),
iq_numeric("tr_audit_seq"),
iq_numeric("tr_branch_cd"),
iq_numeric("tr_cash_amt"),
...
cast("tr_tran_time" as time),
iq_numeric("tr_t
Tom Hart wrote:
Martin Gainty wrote:
2 things
tr_tran_time needs to be already in 'time format'
is_ok needs to be indexed (preferably bitmapped index)
HTH/
Martin
The data is COPY'ed from csv's that our internal software creates, and
we don't have control over
.pgpass file. There's a way to set a
file that contains the password (pgAdmin will create one autmoatically)
that pgsql will look for before it asks for your password. It's stored in ~/
The solution I use is a bat file that redefines an environment variable
(PGPASSFILE) that points to
topic
thanks
josh
You can start here:
http://en.wikipedia.org/wiki/Extract%2C_transform%2C_load
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
---(end of broadcast)---
TIP
exclusively and I'm
on this list as well as other postgres lists constantly (even if as a
reader most of the time). If they have this big of an issue, why not ask
for help?
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (
Bill Moran wrote:
In response to Tom Hart <[EMAIL PROTECTED]>:
Let me just sneak in a quick rant here, from somebody who really doesn't
matter.
We run drupal for our corporate intranet (currently being built) and we
use postgreSQL as the backend. Some of the modules and t
he UTF-8 db.
If you haven't gathered yet, I'm pretty in the dark regarding encoding
issues, especially when applied to pg, so any help here would be
appreciated.
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
out this page:
http://www.acunetix.com/websitesecurity/sql-injection.htm
and this page: http://www.acunetix.com/websitesecurity/sql-injection2.htm
for more information.
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
--
his? Also I
was thinking perhaps it was possible to do an ETL type setup, where I
can SELECT from the ASCII db and INSERT into the UTF-8 db.
If you haven't gathered yet, I'm pretty in the dark regarding encoding
issues, especially when applied to pg, so any help here would be
appreci
Tommy Gildseth wrote:
Tom Hart wrote:
Hello everybody. I hope your week's going well so far.
I built our data mine in postgreSQL around 3 months ago and I've been
working with it since. Postgres is great and I'm really enjoying it,
but I've hit a bit of a hitch. Ori
Tommy Gildseth wrote:
Tom Hart wrote:
Hello everybody. I hope your week's going well so far.
I built our data mine in postgreSQL around 3 months ago and I've been
working with it since. Postgres is great and I'm really enjoying it,
but I've hit a bit of a hitch. Ori
-
From: "Tom Hart" <[EMAIL PROTECTED]>
To: "Postgres General List"
Sent: Wednesday, January 23, 2008 3:32 PM
Subject: [GENERAL] retry: converting ASCII to UTF-8
I think you may have misunderstood. I realize that encoding is a
database setting, which is why I originall
Ok, that did it. Thank you for the help.
Tomasz Ostrowski wrote:
On Wed, 23 Jan 2008, Tom Hart wrote:
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence
for encoding "UTF8": 0xc52f
Try editing your dump-file and change the line which reads "SET
windows. What do you guys think?
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
Joshua D. Drake wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tue, 29 Jan 2008 12:53:25 -0500
Tom Hart <[EMAIL PROTECTED]> wrote:
Hey everybody. I was just informed that our organization has a credit
at amazon.com and asked if I had any books I wanted. I've been
thi
people. The key here is that when it's up to you to
"connect the dots" then you learn what the dots are, how they relate to
each other, and what each of them is for. That gives you a lot better
understanding then "Just run SELECT count(*) FROM a LEFT JOIN...".
Of cours
ence stuff from online
docs/google but the really tricky questions were only answered here, and
amazingly enough, quickly and with good humor. Perhaps what we really
need is somebody to comb through the archives looking for common
problems or exceptional solutions and compile them into a
Joshua D. Drake wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 13:55:12 -0500
Tom Hart <[EMAIL PROTECTED]> wrote:
I definitely think that the lists are one of the shining stars for
postgresql support. I've learned some good reference stuff from
fer. Sorry it's not a BMW :-)
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Ivan Sergio Borgonovo wrote:
On Wed, 30 Jan 2008 16:10:07 -0500
Tom Hart <[EMAIL PROTECTED]> wrote:
Hello everybody. What started as a question about "Practical
PostgreSQL" has ballooned into a project to create another
[snip]
I generally tend to write stuff that was
th no return on their investment except pride in their work
and a better overall product for everybody to use. I'm not a talented
enough programmer to contribute to the code, but in this way I can do
something to give back to the pg community.
--
Tom Hart
IT Specialist
Cooperative F
need is one person to get this going, and then the
community can have at it
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
---(end of broadcast)---
TIP 2: Don'
oject: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
Are we still waiting on this, or did the discussion move off list?
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 47
hese sorts of errors and fix
them? I'm really not great with character encodings and I'm not sure
where to go on this. Any help?
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)
--
Kynn Jones wrote:
Is there a simple way to copy a table from one database to another
without generating an intermediate dump file?
TIA!
Kynn
You're looking for ETL.
http://en.wikipedia.org/wiki/Extract%2C_transform%2C_load
--
Tom Hart
IT Specialist
Cooperative Federal
723 Westco
Richard Huxton wrote:
Tom Hart wrote:
Hello everybody. I recently converted my db from ASCII encoding to
UTF8 (we have a lot of spanish-speaking members, and need the extra
character support). Everything was working great, but I noticed this
error, while trying to COPY one of our tables from
52 matches
Mail list logo
