[SQL] how to "enumerate" rows ?
I sent this to pgsql-general first but eventually figured this list is the more appropriate venue for asking for help. If this happens to be a FAQ item please briefly point me where to go or what search terms to use in the archive. > First of all, yes I know that result rows don't have any > intrinsic ordering that I can expect to not change. > > I have a table recording vaccinations for patients roughly > like this: > > table vaccinations > pk, > patient, > date_given, > disease > > Data in that table would look like this: > > 1, > 1742, > 2003-11-27 > tetanus > > 3, > 1742, > 2000-10-24 > flu > > 12, > 1742, > 2003-1-17 > tetanus > > Now, I need to enumerate the vaccinations per patient per > disease. They are intrinsically ordered by their date of > vaccination but I need to attach a number to them such that I > have: > > #1 > tetanus > 1, > 1742, > 2003-11-27 > > #2 > tetanus > 12, > 1742, > 2003-1-17 > > #1 > flu > 3, > 1742, > 2000-10-24 > > My plan was to select sub-sets by > > select > from vaccination > where patient=a_patient_id and disease=a_disease > order by date_given > > and then somehow cross (?) join them to a sub-set of the > integer table according to Celko's auxiliary integer table > technique (where I create the integer sub-set table by > > select val > from integers > where val <= > select count(*) > from vaccination > where > disease=a_disease and > patient=a_patient > > ) > > But I just can't figure out how to correctly do this... > > Note that I try to construct a view and thus don't have > constant values for a_disease and a_patient. > > Can someone please point me in the right direction ? > > Thanks, > Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Tip: a function for creating a remote view using dblink
> Hello, >I'm posting a function here in the hope others may find it useful > and/or correct my mistakes/make improvements :) > > This creates a view of a remote table, using dblink: ... > Is there any existing site (a wiki for example) for posting PostgreSQL > specific tips? The PG cookbook ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Python connection
a) this is the wrong mailing list for your question b) obviously, it doesn't like the "," part (and possibly the "host" part but that remains to be seen), try separating by ":" or just space or whatever libpq docs say c) any particular reason you don't use the Python DB API ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Equivalant of SQL Server's Nchar and NVARCHAR
> Dear friends, > Is there [...] > Please shed some light. http://www.postgresql.org/docs/7.4/static/index.html > Thanks You are welcome. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Unique Constraint with foreign Key
> > or Can i have varchar types of size 50 as primary keys in Postgres. > Yes. But unless the 50 character limit comes from a business rule, you > might want to use the type 'text'. And if that limit does come from a business rule you might want to think twice whether using columns constrained by business rules are good candidates for primary keys. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] Very slow search using basic pattern matching
> WHERE
> da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.'
> OR
> da_records.TITLE_CY ~* '.*Aberystwyth*.'
> limit 100
>
> Is there a better way of matching the string? (Must be case
> insensitive)
Are you sure you can't anchor the search pattern ?
eg ~* '^' || lower('Aberystwyth') || '.*'
That'd allow for use of (functional) indices.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Find out whether a view's column is indexed?
> and I can see whether a sequential scan or an index scan is > performed, but parsing the output of EXPLAIN programmatically > is nearly impossible. Anyway the words 'Index Scan' and 'Seq > Scan' can change without notice, maybe even from one locale to > another. I think you are operating under the faulty assumption that 'Index Scan' in EXPLAIN output signifies that a column is *indexed*. What it really tells you is whether an index is actually *used* when getting data from a column. That of course requires an index to be there. However, an index being there doesn't guarantee it being used. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Need indexes on inherited tables?
> Is this a possible area for future enhancements? Yes. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] surrogate key or not?
Josh, I reckon you are the one in the know so I'll take advantage of that and ascertain myself of your advice. I am the primary designer for the database schema of GnuMed (www.gnumed.org) - a practice management application intended to store medical data. Obviously we wouldn't want ambigous data. I have until now used surrogate primary keys on all table like so: create table diagnosis ( pk serial primary key, fk_patient integer not null references patient(pk) on update cascade on delete cascade, narrative text not null, unique(fk_patient, narrative) ); Note that fk_patient would not do for a primary key since you can have several diagnoses for a patient. However, the combination of fk_patient and narrative would, as is implied by the unique() constraint. For fear of having the real primary key change due to business logic changes I have resorted to the surrogate key. Short question: Is this OK re your concerns for using surrogates, eg. using a surrogate but making sure that at any one time there *would* be a real primary key candidate ? This would amount to: > Streets > IDStreet Name Location > 345 Green StreetWest Side of City > 2019 Green StreetIn Front of Consulate > 5781 Green StreetShortest in Town Key: ID UNIQUE: Key, Location Is that OK ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] surrogate key or not?
Josh, sad, > create table diagnosis ( > pk serial primary key, > fk_patient integer > not null > references patient(pk) > on update cascade > on delete cascade, > narrative text > not null, > unique(fk_patient, narrative) > ); > > This was obviously created so that a patient could have multiple diagnoses. > However, there is no information in the table to indicate *why* there are > multiple diagnoses. Because there is no information to be had on this fact. The patient IS afflicted by such or she is not. There is no why. > And you are using a real key based on a long text > field; Yes, but for various concerns I am not using it as the primary key, just making sure it is unique. I was just trying to ascertain myself that this is OK to do from a database insider point of view. > always hazardous, as there are many ways to phrase the same > information and duplication is likely. But that is at the discreetion of the user/doctor and nothing that can be enforced at the DB level (no, don't start thinking about coding systems/classifications). > To do it in english, your postulates look like: > > PATIENT 67 was given a diagnosis of WATER ON THE KNEE. > PATIENT 456 was given a diagnosis of ACUTE HYPOCHONDRIA. Hm, I don't see anything wrong with that (I'm a doctor). The plain information that Patient 456 is known to have suffered bouts of ACUTE HYPOCHONDRIA is invaluable when dealing with an agitated, psychically decompensated, hyperventilating patient 456. > But this is a bit sketchy. Who made these diagnoses? I may or may not care. Our actual schema does, of course, carry that information. > When did they make them? We'd be happy if we always knew. >Why? That's of marginal concern, actually, and the answer just flows from the narrative of the medical record. But even if there's no narrative there the "fact" alone helps. > create table diagnosis ( > pk serial primary key, > fk_patient integer references patient(pk), > fk_visit integer references visits(pk), > fk_complaint integer references complaints(pk) Nope, this doesn't belong here AT ALL from a medical point of view. Diagnoses and complaints don't have any rational relationship. This is life. > fk_staff integer references medical_staff(pk) > narrative text, > unique(fk_patient, fk_visit, fk_complaint, fk_staff) > ); And in fact our real tables ARE pretty much like that :-) > PATIENT 67 was given a diagnosis by STAFF MEMBER 12 on his VISIT #3 > in response to NOT BEING ABLE TO WALK of WATER ON THE KNEE > PATIENT 456 was given a diagnosis by STAFF MEMBER 19 on his VISIT #192 > in response to THE CREEPY-CRAWLIES of ACUTE HYPOCHONDRIA That'd by a psychosis ;-) > It also allows you to establish a much more useful key; it's reasonable to > expect that a single staff member on one visit in response to one complaint > would only give one diagnosis. Entirely false and a possible sign of inappropriate care. > Otherwise, you have more than database > problems. And it prevents you from having to rely on a flaky long text key. Flaky long text is what kept people reasonably well in health for the last, what, five thousand years ? I rely on it countless times every single day. BTW, our full schema is here: http://www.hherb.com/gnumed/schema/ Lot's of it isn't in the state yet where we want it but we are getting there - or so I think. Karsten Hilbert, MD, PhD Leipzig, Germany -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] surrogate key or not?
Josh, > > In other words, with surrogate keys, you eliminate the chance > > that your original design was flawed due to lack of important > > initial knowledge. > > Well, you don't *eliminate* it, but you do decrease it. > > I'd say, yes, this is an important 4th reason: > > 4) Your spec may be incorrect and surrogate keys make it easier to make design > changes in production. thanks so much - this is exactly the kind of assurance I was looking for. BTW, I will come back to your generous offer in August. Thanks again. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] surrogate key or not?
On Fri, Jul 23, 2004 at 10:07:48AM -0400, Tom Lane wrote: > The other standard reason for using a made-up value as primary key is > that it's under your control and you can guarantee it isn't going to > change: one record will have the same primary key for its entire life, > which vastly simplifies handling of foreign keys and so forth. That is pretty much the main reason why our schema does so. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] surrogate key or not?
Just for the record, the GnuMed schema docs are done nightly with PostgreSQL Autodoc in HTML mode: http://www.rbt.ca/autodoc/ Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] surrogate key or not?
> This reminds me of a project I worked on many years ago, I was pretty much > fresh out of university writing a system for a large medical practice - > itwas principally for accounting purposes. I made lots of suggestions like > Josh's, only to get replies like Karsten's. I the progammer wanted to codify > everything so as to enable data analysis (ie linking complaints and > diagnosis, etc) but the doctors wern't interested. Likely it wasn't their intent with the system. That doesn't mean it can't be done ... (not that I think that with todays tools it sufficiently can but ... :-> ICPC probably comes closest to that). Anyway, I am highly interested in increasing the data quality in my records. However, to be able to care for a patient *when needed* I won't allow software I write to *force* coding upon me. Trust me I have and am still researching coding, classifying, structuring of medical data at an ongoing basis which funnily constantly improves my daily abilities as a doctor. Now, back to GnuMed, we *do* allow to code arbitrary pieces of narrative with arbitrarily many codes from arbitrarily many coding systems. Same for classifying (rather typing) data. > They just wanted to write free text comments. And the reason for > it (as far as I can tell) is the distinction between > general practice and reseach (such as epidemiology). Rather it is the difference between reality and theory. In reality you are dealing with tens of patients with 1-5 problems almost neither of which are "quite right" if you go by the textbooks. Nevertheless everyone expects you'll never forget/do wrong a thing. > So (GPs) are not so much searching for new knowlege in their patients records, > as applying the knowlege gained from research (done by researchers) to treat > individual patients. One thing we ARE looking for in our records is the ability to find groups of patients by arbitrary criteria since one day I'll have to find all my patients whose father took a statine, whose second-born child suffered a bout of neutropenia 2 weeks after birth and who started being on the pill at age 14. Because they'll have a 3fold increased risk of lung embolus. Unless monitored for clotting factors every 6 months. Which I will have to do from now on. Get my point ? :-) > Karsten might beg to differ, I don't know, but the "long flaky text" comment > triggered some old (and fond) memories ;-) I totally understand what you are saying... We do cling to old trusted things. Also, I used a bit of hyperbole to get my point across. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] surrogate key or not?
> retrievable. I imagine a system whereby you define keywords and attributes > for them (attributes would be an episode date, or dosage, etc). This is pretty much exactly what we are working on. We are factoring out data into dedicated tables where that is possible due to the structured nature of the data and store the remaining narrative as TEXT with associated attributes of all kinds. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] surrogate key or not?
> simpler, as a first stage and easily implemented, give him some way he can > tag words and phrases he feels important. save these in a table along with a > foreign key identifying the source. as a second stage keep analysing the > words and phrases chosen and empirically build up a database of significant > words and phrases relevant to that specific installation (or doctor), and as > a third stage, highlight these as he types in the data Even simpler. Most notes only ever contain those words that are relevant :-) So what we are doing is matching different parts of the progress note, eg. when the doctor types "cough, congested nose, sore throat" as a complaint we pull in the drugs he ususally prescribes for that condition and the dosages she ususally uses ... Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] need "row number"
p.age_due_max, vvs4p.min_interval, vvs4p.vacc_comment, vvs4p.pk_regime, vvs4p.pk_indication, vvs4p.pk_recommended_by from v_vaccs_scheduled4pat vvs4p where vvs4p.is_booster is true and vvs4p.min_interval < age ( (select max(vpv4i13.date) from v_pat_vacc4ind vpv4i13 where vpv4i13.pk_patient = vvs4p.pk_patient and vpv4i13.indication = vvs4p.indication )) ; comment on view v_pat_missing_boosters is 'boosters a patient has not been given yet according to the schedules a patient is on and the previously received vaccinations'; Thanks, Karsten Hilbert, MD, PhD GnuMed i18n coordinator http://www.gnumed.org -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [GENERAL] need ``row number``
> You are going to need a set returning function. It will have > to look up the expected boosters, the expected time elapsed, > and return them with their sequence numbers if they exists. > There is no easy way to do it in a view that I can think of. I am not convinced I'll need a SRF. I am not trying to calculate something that isn't there yet. I am just trying to join two views appropriately. I might have to employ some variant of Celko's integer helper table but I'm not sure how to proceed. Karsten > <<< Karsten Hilbert <[EMAIL PROTECTED]> 9/23 1:56p >>> > Hello all, > > yes, I know, "row number" isn't a concept that fits into the > relational model and I will only be asking for something > similar. > > explanation (actual views below) > > > I have a view that holds the vaccinations scheduled for a > patient (v_vaccs_scheduled4pat) depending on what vaccination > regimes that patient is on. There are typically between 1 to 5 > vaccinations per disease (indication/regime) which is expressed > in the vaccination sequence number. Some regimes also have > booster shots scheduled. Those boosters are to be given > regularly after a set interval. Those have the sequence number > field set to NULL. > > There is a second view that lists all the vaccinations > actually given to a patient per regime (v_pat_vacc4ind). > This view has dates when the shot was given but no sequence > number. > > I now want to create a view which correlates the two showing > me which actual vaccination corresponds to which scheduled > vaccination. This is what I cannot get my head wrapped around > although it is probably fairly straightforward. > > The conceptual solution would be to order actual vaccinations > by date per regime and number them (remember the "row number" > in the subject line ?). One would then join on that with the > sequence numbers from the scheduled vaccinations view and treat > any actual vaccinations where "row number" > max(sequence > number) as being boosters (medically this is correct, btw). > Yes, there can and will be several boosters for some regimes. > > raw data > > full schema here: > http://hherb.com/gnumed/schema/ > > full schema defs in CVS here: > > http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/gmClinicalViews.sql > > relevant excerpt: > > --- == > --- vaccination stuff > --- - > \unset ON_ERROR_STOP > drop view v_vacc_regimes; > \set ON_ERROR_STOP 1 > > create view v_vacc_regimes as > select > vreg.id as pk_regime, > vind.description as indication, > _(vind.description) as l10n_indication, > vreg.name as regime, > coalesce(vreg.comment, '') as comment, > vreg.fk_indication as pk_indication, > vreg.fk_recommended_by as pk_recommended_by > from > vacc_regime vreg, > vacc_indication vind > where > vreg.fk_indication = vind.id > ; > > comment on view v_vacc_regimes is > 'all vaccination schedules known to the system'; > > --- - > \unset ON_ERROR_STOP > drop view v_vacc_defs4reg; > \set ON_ERROR_STOP 1 > > create view v_vacc_defs4reg as > select > vreg.id as pk_regime, > vind.description as indication, > _(vind.description) as l10n_indication, > vreg.name as regime, > coalesce(vreg.comment, '') as reg_comment, > vdef.is_booster as is_booster, > vdef.seq_no as vacc_seq_no, > vdef.min_age_due as age_due_min, > vdef.max_age_due as age_due_max, > vdef.min_interval as min_interval, > coalesce(vdef.comment, '') as vacc_comment, > vind.id as pk_indication, > vreg.fk_recommended_by as pk_recommended_by > from > vacc_regime vreg, > vacc_indication vind, > vacc_def vdef > where > vreg.id = vdef.fk_regime > and > vreg.fk_indication = vind.id > order by > indication, > vacc_seq_no > ; > > comment on view v_vacc_defs4reg is > 'vaccination event definitions for all schedules known to the system'; > > --- - > \unset ON_ERROR_STOP > drop view v_vacc_regs4pat; > \set ON_ERROR_STOP 1 > > create view v_vacc_regs4pat as > select > lp2vr.fk_patient as pk_patient, > vvr.ind
Re: [SQL] bibliographic schema
> > we're looking for a SQL database schema for bibliographical references. > > the goal is to extract all the bibliographical references contained in > > our various existing pgsql scientific databases in only one specific > > database and to interconnect them with external keys and perl scripts. Your best bet might be to take a look at OSS library management solutions and see whether that fits your needs or is suitable as a starting point. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] postgreSQL 8beta
> does postgresql have a datatype 'other' which in hsqldb is an Object? I > am trying to convert the table below into postgreSQL 8: > > create table TIMERS ( > TIMERID varchar(50) not null, > TARGETID varchar(50) not null, > INITIALDATE timestamp not null, > INTERVAL bigint, > INSTANCEPK other, > INFO other, > constraint timers_pk primary key (TIMERID) For INFO use either TEXT or BYTEA depending on what you want to store. Regarding INSTANCEPK you should think again whether OTHER is an appropriate datatype in the first place. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UPDATE/INSERT on multiple co-dependent tables
> Is it possible for an UPDATE/INSERT query string to function in such a > way that it requires two like fields in different tables to be equal > to/'in sync with' one another: > > Example: I have two tables: registration & schedules > they both record a class_id, start_date, end_date... I want to make > sure that if the schedule_id field is updated in the registration table; > that class_id, start_date & end_date fields automatically change to > match the schedules.id record in the schedules table Sounds like you want a foreign key with ON UPDATE CASCADE. If that doesn't work for some reason or other you might be able to achieve what you need with an explicit trigger on update of registration.schedule_id. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] inserting values into types
> CREATE TYPE qwerty_UDT AS (abc INT); > > CREATE TABLE t (col1 qwerty_UDT); > > INSERT INTO t (col1) VALUES (qwerty_UDT(123)); > > ERROR: function qwerty_udt(integer) does not exist > HINT: No function matches the given name and argument types. You may need to > add explicit type casts. Well, doesn't the error message say it ? "function ... does not exist". The question would be why are you doing "qwerty_UDT(123)" in the first place ? It seems you'd want to be casting ? PostgreSQL, in any case, thinks you want to call a function qwerty_UDT(integer) which it can't find. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Returning a bool on DELETE in a proc.
> >So what variable/function is the correct SQL-equivalent to ROW_COUNT and > >can it be used in the following statement ? > >like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? > > SQL doesn't support that (although I suppose it could be made to with > some pg_rows_affected() function). If someone could only post that pg_rows_affected() function - this would end all further requests for "row number in result set" inquiries with the simple advice "use pg_rows_affected() and join that with an integer table". I'd know a bunch of uses right off the top of my head. I suppose it ain't hard at all but my C skills are way to limited to try anything like that. My understanding, too, likely. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Information about the command SQL " create synonym".
> Much like yourself, I have been searching for a way to create synonyms > in Postgres. I think that I have found a hack / solution to our > problem. ... > What I did, was to make use of the Postgres inheritance feature. > This in turn effectively creates an alias: > CREATE TABLE foo (bar int not null); > > but you need a synonym called "gerald", then you could do the following ... > > CREATE TABLE gerald () INHERITS (foo); > I would love to hear from the developers, or other gurus, if there are > any serious issues with doing this. Primary keys and foreign keys will likely show unexpected behaviour (as in being out of sync between foo and gerald and any other "alias" child tables of foo). Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Trigger on select?
On Tue, Aug 02, 2005 at 05:54:59PM -0700, Chris Travers wrote: > >Hey, anyone know if it is possible to fire a trigger before a select? > >I'm considering creating some tables which contain data summarized > >from other tables as kind of a cache mechanism. The hope is I can > >speed up some queries that get executed a lot (and are kind of slow) > >by maintaining data (like sums of big columns, for example). I was > >all ready to go and then I discovered that trigger-before-select is > >not supported. (Is it in any DB?) > > > >The idea is this: > > > >Any time I execute a query that would access the summary data, the > >"before select" trigger fires and goes out and builds any summary data > >missing from the summary table. > > No. You must instead generate a view. And then it needs to be a materialized view of some sort to achieve the caching he's after. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Function Dependency
On Sun, Feb 05, 2006 at 07:18:33PM -0700, Tony Wasson wrote: > > I am maintaining an application that has over 400 procedures and functions > > written in plsql, and around 100 tables. > > I want to generate a function dependency chart to depict the following: > > > > 1. Inter function/procedure dependencies > > 2. function-tables dependencies > > 3. function-sequences depencies ... > I too would be interested in a standardized tool to do this. I had a > similar situation and I ended up writing a perl script to parse my SQL > and make a graphviz dot file. I then used graphviz to make a function > dependency chart. I can't promise it would catch every single case, > but I can provide you with the code if you wish to give it a whirl. Tony, what do you think about sending a copy of this code to the pg_autodoc author ? I'm sure they'd be interested to have a look at this. http://www.rbt.ca/autodoc/index.html I have CC'ed this to the author. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] newbie question
On Fri, Mar 03, 2006 at 10:43:09AM +0100, ivan marchesini wrote: > I have fastly created a table in a postgresql database.. > some columns where edited by hand (columns A, B, C), and some others > (columns D, E, F) have been calculated as a result of mathematical > equation (where the factors are the A, B, C columns) > now I simply need to change some values in the A, B, C columns and I > would like to obtain the correct values in the D, E, F column... > I know that this is a tipical problem of a spreadsheet but how can I > solve it with a DBMS?? Use triggers or a view. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] newbie question
On Fri, Mar 03, 2006 at 12:30:20PM +0100, ivan marchesini wrote: > another question... > is it possible to copy a table to a view and then back the view to a > table??? You need to read a basic textbook about what a view is. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Check/unique constraint question
On Sun, Mar 05, 2006 at 12:02:58PM +0300, Nikolay Samokhvalov wrote: > Unfortunately, at the moment Postgres doesn't support subqueries in > CHECK constraints, so it's seems that you should use trigger to check > what you need The OP could also use a check constraint with a function if everything (the context) but the value to check is known beforehand or can be derived from it. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] column type for pdf file
On Wed, May 18, 2011 at 10:46:23PM +0200, Julien Cigar wrote: > Unless you've good reasons to do so it's best to store the file on > the file system Why ? If you suggest reasons are needed for storing the PDF in the database I'd like to know the reasons for *not* doing so. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On Wed, May 18, 2011 at 11:21:43PM +0200, Julien Cigar wrote: > >>Unless you've good reasons to do so it's best to store the file on > >>the file system > >Why ? > > > >If you suggest reasons are needed for storing the PDF in the > >database I'd like to know the reasons for *not* doing so. > > It increases the load, consume connections, but the biggest drawback > is probably the memory consumption .. Thanks. At least now the OP knows some of the reasoning for not doing so :-) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On Thu, May 19, 2011 at 09:39:54AM -0400, Emi Lu wrote: > All right, it seems that everyone thinks saving a pdf into postgresql > is not a good idea. No. > As a summary, disadvantages are: > == > . Memory issue when read/save/retrieve the file > . Increase load Those can be a consideration, yes. > . Consume connections You will need a connection to the database anyway. There's no need to use another one. > . during transaction lo may be lost? Huh ? > . file systems do better than DB That's not a fact but rather an assertion. And a partial one at that. You may want to search the archives a bit as this has been discussed at length various times. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Mon, Jun 18, 2012 at 11:36:14AM +0300, Achilleas Mantzios wrote: > > >> Not talking about going to something after 8.3.19, just updating to > > >> the latest 8.3 version. On most systems it's a simple: > > >> > > >> sudo apt-get upgrade > > >> > > >> or similar and sit back and watch. > > > > > > Thanx, unfortunately we dont have TCP/IP connectivity to (most of) the > > > ships, and AFAIK apt-get does not yet work over advanced > > > UUCP/minicom/kermit or other equivalent hich-tech dial up connection. > > > just joking :) You might consider shipping .debs over the dialup and either "dpkg -i"ing them or even setup a local repository on the ships from which to "apt-get upgrade". Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] help with version checking
In GNUmed we have created a function gm_concat_table_structure() in http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?rev=1.6&root=gnumed&view=log which returns a reproducable, human-readable TEXT concatenation of all the relevant parts of the schema. We then do select md5(gm_concat_table_structure()); and compare the output to known hashes for certain schema versions. That way we don't simply "believe" what is in a table "current_version" but rather actually *detect* (within reasonable limits) the version. It works well so far, no noticably delay even during client startup (which does the check and complains on mismatches). It may not scale particularly well to very large schemata, possibly. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] What are the (various) best practices/opinions for table/column/constraint naming?
Speaking on behalf of the GNUmed schema. The type of thing comes first for consistency. primary key: pk Some might argue pk_ is preferrable such that in joins one ist not forced to use column aliases. We do in such cases. The "... where table.pk = ..." just seems soo intuitive. foreign key: fk_ This then affords fk__ should that ever be needed (likely an indication of bad design). indices: idx__ sequences: so far we relied on PG giving us a name constraints: named by what they *do*, such as ensure_..._consistency functions: f_ trigger functions: trf_ trigger: tr_ > And other naming conventions suggest using mixed/camel case > (quoted-identifiers) We don't use mixed case as that would *require* quoting which is liable to being forgotten. Not much help, just our convention. Karsten -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Select default values
On Wed, Jul 23, 2008 at 12:35:08PM +0200, Maximilian Tyrtania wrote: > With your way (insert into f(a,b) values(default, default) returning *) i > need to know everything about the given table. > > Hmm. Any ideas? Do look at the information schema. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Inherits is not encouraged?
On Mon, Mar 09, 2009 at 07:55:35PM -0400, Tom Lane wrote: > Emi Lu writes: > > (1) Is possible that I could change the column of a table that is > > defined by Inherits? > > > e.g., t1(col1, col2, col3); create table tx(colx) Inherits (t1) > > I'd like to get tx shown as (col1, colx, col2, col3). > > No, you don't really have a lot of control over that. "Explicit select list" would be one way (and the one that's encouraged as good practice): select col1, colx, col2, col3 from tx; If that doesn't cut it (say, generic viewer which doesn't know what is going to be viewed) - use a view over tx. Maybe even "hide" the table tx as "table_tx" and name the view itself "tx". Care needs to be taken then to either setup appropriate rules or to make insert/update/delete go to "table_tx". Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
