[SQL] The Momjian Tome / DBA Certification
Hey Bruce,
Your book finally hit the bookshelves of Staten Island.
We have a Barnes and Noble here that actually has a reasonable
database section although it is misnamed 'Networking'. ;-)
I'll be getting my copy on payday - I found the sql examples
that I downloaded very useful and as I've been running Pg in
production since 6.3.2 I'd like to support the project.
I have a question as well. Any chance you folks or Great Bridge
would offer Pg*DBA certification exams?
Cheers,
Tom
SVCMC - Center for Behavioral Health
----
Thomas Good tomg@ { admin | q8 } .nrnet.org
Database Administrator Phone: 718-354-5528
Staten Island Region Fax: 718-354-5056
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |-- linux The Power To Serve
/* Jeder Jeck ist anders! */
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] any proper benchmark scripts?
On 18 Apr 2001, Clayton Cottingham aka drfrog wrote:
> i will recomp both the newest postgresql and mysql
> not using any optimizing techs at all i'll post the
> config scripts i use
Hiya Clayton,
Try it with a few hundred thousand tuples in a good size table with
say 300 users banging on the box. MySQL is a nice substitute for
Access but that's about it.
Not to mention missing features: views and foreign keys for
example...and have a look at the API...yuck. I always hear people
complain (MySQL fans) about Pg not having DROP COLUMN in its SQL
implementation. To me a VIEW is slightly more important. ;-)
(Trying hard not to be obnoxious here but failing. Comparing MySQL
to Pg is like comparing windows to BSD...and I just can't apologise
for calling it like I see it!)
Cheers!
Tom (the most obnoxious man in Staten Island?)
SVCMC - Center for Behavioral Health
----
Thomas Good tomg@ { admin | q8 } .nrnet.org
Database Administrator Phone: 718-354-5528
Staten Island RegionMobile: 917-282-7359
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |-- linux The Power To Serve
/* We prefer linguistic vigor over mathematical rigor. */
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] MySql 'REPLACE'
On Wed, 25 Apr 2001, Alessio Bragadini wrote:
> I am working in porting the Slash software from MySql to PostgreSQL. I
> stepped in MySql's REPLACE command (a SQL command) that to my knowledge
> is not supported by PostgreSQL and it's not standard SQL. According to
> MySql's manual:
>
> "REPLACE works exactly like INSERT, except that if an old record in the
> table has the same value as a new record on a unique index, the old
> record is
> deleted before the new record is inserted. See section 7.21 INSERT
> syntax."
>
> REPLACE INTO table (column, column...) VALUES (value, value...)
>
> Has anyone had any experience about how to simulate it under PostgreSQL?
> I am using Perl and I can move most of the thing into application
> anyway.
Hi, I did this in moving a query from a deprecated* (INSERT INTO ... SELECT)
form to two separate queries...using perl. My statement handle for the
select cursor grabbed all of the values and then passed them to an insert.
In your case I'd guess you could grab the pkey in a $sth->fetchrow_array()
(or hashref) and do a delete & insert in a $dbh->do() series?
Good luck,
Tom
* deprecated according to SQL In A Nutshell and other SQL3 ref guides.
SVCMC - Center for Behavioral Health
Thomas Good tomg@ { admin | q8 } .nrnet.org
Database Administrator Phone: 718-354-5528
Staten Island RegionMobile: 917-282-7359
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |-- linux The Power To Serve
/* We prefer linguistic vigor over mathematical rigor. */
---(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
Re: [SQL] prob with PERL/Postgres
On Mon, 6 Aug 2001, Kate Collins wrote:
> I use the Perl DBI module to connect to a postgres data base on the local
> system. I don't specify the host name. This is the syntax I use to connect to
> the data base:
>
> use DBI;
>
> $dbname = "foo";
> $connstr = "dbi:Pg:dbname=$dbname";
> $dbh = DBI->connect($connstr);
Kate, he uses a diff module by the same author (Edmund Mergl) but with
a very diff syntax. The advantage of the DBI - Kris, if you're interested -
is that the syntax is much like ESQL/C and the code is much more portable.
For example I use DBI to access both pg and oracle.
Cheers,
Tom
> Kristopher Yates wrote:
>
> > PERL SNIPPET:
> >
> > # build arrays from file (OMITTED)
> >
> > use Pg;
> > $dbhost='127.0.0.1';
> > $dbname='mpact';
> > #$connstr="dbname=$dbname";
> > $connstr="host=$dbhost dbname=$dbname";
> > $conn = Pg::connectdb($connstr);
> >
> > #more code related to date omitted
> >
> > $result=$conn->exec($sql);
> > (PGRES_COMMAND_OK eq $result->resultStatus)
> > or die $conn->errorMessage;
> >
> > WHY DO I GET PQsendQuery() -- There is no connection to the
> > backend. I have tried leaving host blank, using IP 127.0.0.1 and
> > hostname localhost. This script should work - the problem is
> > something with postgres but I dont know what. Any ideas out
> > there? Thanks, Kris
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
>
> --
> =
> Katherine (Kate) L. Collins
> Senior Software Engineer/Meteorologist
> Weather Services International (WSI Corporation)
> 900 Technology Park Drive
> Billerica, MA 01821
> EMAIL: [EMAIL PROTECTED]
> PHONE: (978) 262-0610
> FAX: (978) 262-0700
> http://www.intellicast.com
>
>
>
> ---(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
>
SVCMC - Center for Behavioral Health
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-354-5528
Residential ServicesMobile: 917-282-7359
/* Die Wahrheit Ist Irgendwo Da Draussen... */
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] prob with PERL/Postgres
On Mon, 6 Aug 2001, Tom Lane wrote:
> Thomas Good <[EMAIL PROTECTED]> writes:
> > Kate, he uses a diff module by the same author (Edmund Mergl) but with
> > a very diff syntax. The advantage of the DBI - Kris, if you're
> > interested - is that the syntax is much like ESQL/C and the code is
> > much more portable. For example I use DBI to access both pg and
> > oracle.
>
> DBI is a good alternative, but is unlikely to act much differently as
> far as connection problems go.
You know Thomas, after I build Pg (including 7.1.2) and fire up initdb
there comes a message about starting the db with -D and the location of
the datafiles. Nothing about using -i...it might be good to include,
no?
Cheers,
Tom
SVCMC - Center for Behavioral Health
----
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-354-5528
Residential ServicesMobile: 917-282-7359
/* Die Wahrheit Ist Irgendwo Da Draussen... */
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Referencing named attribute in where clause doesn't workwith 7.1.2?
On Wed, 8 Aug 2001, Andreas Joseph Krogh wrote:
> Hi, this is my first post to this list so please...
> I have problems getting this query to work, any ideas?
>
> select article.title_text_key,
> (select on_text.text_value from on_text where
> on_text.text_key = title_text_key
> AND NOT title_text_key is NULL
> AND on_text.lang_id = (select code.id from code, code_group where
>code.code_group_id = code_group.id
> AND code_group.description = 'lang' AND code.code_key = 'lang.NO'
> )
> )
> as title_text_value from article where NOT title_text_value is NULL;
>
> ERROR: Attribute 'title_text_value' not found
Hallo Andreas,
Relocating your AS clause to the topmost string? BTW, table aliasing
would make for less typing.
Best of luck!
> Issuing the same query without the where-clause does work tho, but
> return tupples with null in them which I don't want.
>
> --
> Andreas Joseph Krogh <[EMAIL PROTECTED]>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
SVCMC - Center for Behavioral Health
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-354-5528
Residential ServicesMobile: 917-282-7359
/* Die Wahrheit Ist Irgendwo Da Draussen... */
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Diferent databases on same query...
On Fri, 26 Oct 2001, Josh Berkus wrote:
> Horst,
>
> > There are many more good reasons to do so. Example from my own
> > domain,
> > medicine:
> > Thus, we have multiple databases on multiple servers all depending on
> > each
> > other.
>
> Your reasons all make sense. What you need is not inter-database
> queries but a robust middleware layer, such as J2EE. Your sort of
> situation is why middleware exists.
Josh,
I'm pretty sure Horst does this already - check out GNU Med.
(Hallo Horst, Viele Grüsse aus New York. ;-)
I guess the problem is that the procedural language mentioned in
the original post has a few hurdles yet to manage.
BTW, I do this (within the context of very small scripts) using
perl DBI. It is cake with Perl. And - your code is relatively
portable. Aside from certain Oracle idiosyncracies of course
(NVL, DECODE, SYSDATE...)
> If GreatBridge was still around, I'd reccommend you contact them, as I
> knwo they implemented a solution for your sort of situation. However, I
> don't know who'd do it now.
What about RedHat? Are they working on this?
Dieter (Simader) of SQL Ledger also does this sort of thing within
his product (which uses perl dbi): www.sql-ledger.org
Cheers
Saint Vincent Catholic Medical Centers
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-818-5528
Behavioral Health Services Fax:718-818-5056
Residential ServicesMobile: 917-282-7359
/* Rekordmeister ist nur der FC Bayern München! */
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] pg_dump's octal strings -> oracle data load
Hi, I am working on the oracle port of my pg application and my data load script contains \012 characters generated by pg. Anyone have any ideas on what to change these to so that: 1) oracle doesn't barf during the load (^M causes this... I'm loading from a -D flagged pg_dump sql script not sql loader) 2) I get clean line breaks in perl generated html (^L does not do this.) ? Obviously would do it but I'm hoping there is a better candidate for search and replace. Thanks! ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Formatting current_time output
Hi. Anyone have any tips on how I can approximate the following: SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL Returned: 10:58 AM I've found lpad(current_time,5); which gets me 1/3 of the way. Is there a function I haven't found? TIA ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Formatting current_time output
On Thu, 3 Oct 2002, Josh Berkus wrote: > Thomas, > > > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL > > > > Returned: 10:58 AM > > > > I've found lpad(current_time,5); which gets me 1/3 of the way. > > Is there a function I haven't found? > > Um, what's wrong with: > SELECT to_char(current_time, 'HH12:MI AM'); Not a thing! Except that I think you mean 'current_date'. ;-) Cheers Josh (and thanks alot)... BTW the JOIN you sent the other day worked fine. Thanks again, --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] from postgres to oracle
On Thu, 24 Oct 2002, Carlos Sousa wrote: > can some one tell how to transfer the contents from a postgres database into > a oracle database Carlos, I use pg_dump to create full CREATE strings, with no double quotes. pg_dump -s -n -D -x dbname > dbname.sql Then I use pg_dump to create full INSERT strings, with no double quotes. pg_dump -a -n -D -x dbname > dbname.df Then I edit the dbname.sql file to remove things oracle objects to like TEXT datatypes (change to whatever you prefer, LONG, VARCHAR2(4000), etc.) SEQUENCE syntax has to be modified slightly, GRANTS (removed with the -x flag) should not exist, etc. I prepend my usual Oracle stuff (SET NLS_DATE_FORMAT = 'whatever you used with Pg';, SET SCAN OFF; SPOOL load.log;, etc) to the file and then test it. With sql*plus as the loader you'll want to stick an EXIT; at the EOF if you run the script using indirection (sqlplus user/pass @dbname.sql) HTH I have examples, if you want em. ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [SQL] Database Design tool
On Tue, 5 Nov 2002, Michael Ansley (UK) wrote: > DBVisualizer is pretty good. Is there a page at PostgreSQL.org where all these links are listed? I've been saving each bit of mail that goes by on this thread...but a page on Pg would be very nice. ;-) Cheers, --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] escape single quote in INSERT command
On Wed, 27 Nov 2002 [EMAIL PROTECTED] wrote:
> Why dont' you use prepare and execute in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "..");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();
> I'd like to put escape characters in my
> > insert command to accommodate for '
$dbh->quote() will do the escaping for DBI but be careful with dates
as the variable binding does not always behave as expected.
You can esc the single with another single, ala ANSI SQL: ''
This works in Oracle, PG and MySQL for sure.
In perl: $name =~ s/\'/\'\'/g;
$query = qq |insert into x values ('$name')|;
and so on...
Now, can some kind soul tell me how to do an 'insert into x select y;'
where x is a numeric(19,2) and y is a money type???
---
Thomas Good e-mail: [EMAIL PROTECTED]
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
--Geistiges Eigentum ist Diebstahl! --
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] escape single quote in INSERT command
On Wed, 27 Nov 2002 [EMAIL PROTECTED] wrote:
> Why dont' you use prepare and execute in case you are using DBI
> same program is like this.
>
> $dbh = DBI -> connect ( "..");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();
> I'd like to put escape characters in my
> > insert command to accommodate for '
$dbh->quote() will do the escaping for DBI but be careful with dates
as the variable binding does not always behave as expected.
You can esc the single with another single, ala ANSI SQL: ''
This works in Oracle, PG and MySQL for sure.
In perl: $name =~ s/\'/\'\'/g;
$query = qq |insert into x values ('$name')|;
and so on...
Now, can some kind soul tell me how to do an 'insert into x select y;'
where x is a numeric(19,2) and y is a money type???
---
Thomas Good e-mail: [EMAIL PROTECTED]
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
--Geistiges Eigentum ist Diebstahl! --
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Casting Money To Numeric
Hi All. Having perused all the online docs I can find it appears there is no SQL solution for casting the dread money type to numeric. Is this true? select rent::numeric(9,2) from x; ERROR: Cannot cast type 'money' to 'numeric' I guess my cash ain't nothing but trash... ;-) TIA! ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] ALTER TABLE x DROP CONSTRAINT fkey
Pardon stupidiy, what is the right syntax for dropping a foreign key? Struggling here! TIA --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Function for adding Money type
On Sun, 26 Jan 2003, D'Arcy J.M. Cain wrote: > This year, my team is planning on improving the MONEY type. Of course, we can > always make it a user defined type if PostgreSQL doesn't want it. We will at > least put it into contrib. However, if people think that it is useful and > want to leave it in the main tree that's good too. What we want to do is a) > switch to a 64 bit integer from a 32 bit integer in order to hold amounts of > any reasonabe size and b) allow it to be cast to and from more types. > Perhaps we can also add the ability to specify the number of decimal places > on output but I am not sure if that would affect the primary benefit of using > it, speed. Hey good news D'Arcy! I prefer using MONEY as well. ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Concatenation Snafu
SELECT code || ' ' || diag, code FROM dsm4 WHERE axis = 1 ORDER BY code; This worked on 6.3-7.3.1 now it dies with: 'unable to identify an operator || for types 'character' and 'character varying' What happened? ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 // Krieg ist selbst Terror - Für Frieden und globale Gerechtigkeit! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Concatenation Snafu
On Tue, 25 Mar 2003, Chad Thompson wrote: > The assumtion that char and varchar can be compared is gone. Any comparison > or in this case concatination between the two types needs to be explicitly > cast. > > try > SELECT code::varchar || ' ' || diag::varchar, code > FROM dsm4 > WHERE axis = 1 > ORDER BY code; Hi Chad, yes I did a CAST(code AS varchar) and it works...I suppose this is an "improvement" to Pg as it prevents the risk of standardising our concatenation syntax with Oracle. ;-) This sort of thing is irritating when you have to support more than one type of backend! Thanks for the post, Tom ------- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 // Krieg ist selbst Terror - Für Frieden und globale Gerechtigkeit! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] JOIN condition confusion
Hi, I am wondering if there is a way to set conditions on a left joined table without hosing the join altogether: query = qq |SELECT p.*, a.user_id FROM patient_dosing p LEFT JOIN patient_assignment a ON p.patient_id = a.patient_id WHERE p.dose_type = 'Missed (AWOL)' AND (p.dose_date >= $start_date AND p.dose_date <= $end_date) -- the next two conditions hose the left join -- AND a.end_date IS NULL -- AND lower(a.assign_type) = 'primary' ORDER BY a.user_id, p.patient_id| if ($dbtype ne "oracle"); Thanks much! Tom ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
