Re: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied.
Try running the initdb program first. Best Wishes, Chris Travers - Original Message - From: "Zengfa Gao" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 9:43 AM Subject: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied. > Hi, > > I download PgSQL source, compiled it, then try to > start pgsql, I got: > > > # su postgres -c '/opt/pgsql/bin/initdb > --pgdata=/var/opt/pgsql/data' > The program > '/opt/pgsql/bin/postgres' > needed by initdb does not belong to PostgreSQL version > 7.3, or > there may be a configuration problem. > > This was the error message issued by that program: > /opt/pgsql/bin/initdb[135]: /opt/pgsql/bin/postgres: > Execute permission denied. > > I check the permission of postgres: > # ls -l /opt/pgsql/bin/postgres > -rwxr-xr-x 1 root bin2994176 Jan 8 > 09:53 /opt/pgsql/bin/postgres > > But same code works fine on my another HPUX 11.11 > system. File permission is same. > > Does anyone have some ideas? > > Thanks! > > Zengfa > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to determine the currently logged on username
Hi all; I will be writing a stored proceedure that will allow a currently logged in user to change his/her password. The function needs to be only able to change the password of the currently logged in user, so it will only take a varchar() argument and needs to look up the username of the currently logged in user. How do I do this? Any ideas? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SECURITY DEFINER changes CURRENT_USER?
Hi all; I found an unexpected behavior while trying to write a function to allow users to change their own passwords. The function is as follows: CREATE OR REPLACE FUNCTION change_password(VARCHAR) RETURNS BOOL AS ' DECLARE username VARCHAR; CMD VARCHAR; password ALIAS FOR $1; BEGIN SELECT INTO username CURRENT_USER; CMD := ''ALTER USER '' || username || '' WITH PASSWORD ''; CMD := CMD || '''''''' || password || ''''''''; EXECUTE CMD; RETURN TRUE; end; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER I would expect this to change the password of the user currently logged in but instead it changes MY password. Evidently when a function is called which is set to SECURITY DEFINER, it changes the context of the current user. The CURRENT_USER then returns the name of the definer rather than the invoker of the function. So this being said-- are there any workarounds that don't allow anyone to change anyone else's password? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to completely move a table to another schema?
Hi all; I have a function which moves a table from one schema to another by updating the relnamespace field of pg_class: CREATE OR REPLACE FUNCTION move_relation(VARCHAR, VARCHAR, VARCHAR) RETURNS BOOL AS ' -- $1 is the table name -- $2 is the source schema -- $3 is the destination schema -- UPDATE pg_catalog.pg_class SET relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $3) WHERE relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $2) AND relname = $1; UPDATE pg_catalog.pg_type SET typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $3) WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $2) AND typname = $1; SELECT TRUE; ' LANGUAGE SQL; Am I missing anything? I have already had a few problems that led me to discover that I needed to put in the second update query. Just figured I would check. Best Wishes, Chris Travers ---(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] INHERITS and Foreign keys
Hi Pedro; I understand that at the moment it is more of a design limitation than a bug. I think we should vote the desired behavior as a feature request, however. Best Wishes, Chris Travers - Original Message - From: "Pedro" <[EMAIL PROTECTED]> > >> Foreign keys, unique and primary key constraints do not meaningfully > >> inherit currently. At some point in the future, that's likely to change, > >> but for now you're pretty much stuck with workarounds (for example, using > >> a separate table to store the ids and triggers/rules on each of the > >> tables > >> in the hierarchy in order to keep the id table in date.) > > hi > > same problem here on 7.4 > can we vote for this bug somewhere ?! > > thanks for your time > Pedro > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(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] Distributed keys
It seems to me that if the inheritance/fireign key behavior was changed so that foreign key constraints could exist within the entire inheritance tree, this problem would be solved. According to previous posts, the behavior will probably change at some point but does not appear to be a priority at the moment. If it were possible to allow FK constraints to work against the inheritance tree rather than a single table therein you could have managers, teachers, and subs as tables inherited tables from employees and the problem would be solved. Currently a workaround I can see is: Hide the actual tables in a shadow schema, and inherit as above. Have each table be represented as a view in the public schema joining the table to another table storing the employee unique identifiers. Place unique constraints on the unique identifiers table. Create rules for inserting, updating, and deleting the records. Have the Employee view search the entire inheritance tree. However, this is assuming that the data you are storing for the employees differs substantially depending on position. If this is not the case, you would do better by having a single employee table and include a field indicating whether the employee is a manager, teacher, or sub. Best Wishes, Chris Travers - Original Message - From: "Michael Glaesemann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 24, 2003 4:42 AM Subject: [SQL] Distributed keys Hello all! An area of the relational database model that is not included in the SQL standard are distributed keys and distributed foreign keys. A quick example (Case 1): employees (id, name); schools (name, location); teachers (employee_id, school_name); subs (employee_id); managers (employee_id, school_name); with constraints unique employees(id) teachers(employee_id) references employees(id) teachers(school_name) references schools(name) subs(employee_id) references employees(id) managers(employee_id) references employees(id) The idea is that employees must be in one (and only one) of either teachers, subs, or managers. Currently, this might be represented in something like (Case 2) employees (id, name, employee_type, school_name); schools (name, location); employee_types (type); with constraints employees(employee_type) references employee_types(type) employees(school_name) references schools(name) where employee_types includes "teacher", "sub", and "manager" Or it might be represented with a number of rules or triggers that perform all of the necessary checking. employees(school_name) can't have a not null constraint because if the employee is a sub, they aren't associated with a school. Using the terms "distributed key" and "foreign distributed key", in the first case employee_id is a "distributed key" in that it must occur in only one of the tables teachers, subs, or managers. Distributed keys are similar in concept to primary keys—they must be unique. This guarantees an employee_id in teachers is not found in subs or managers, an employee_id in subs is not found in managers or teachers, and an employee_id in managers is not found in subs or teachers. employees(id) is a foreign distributed key in teachers, subs, and managers (as employee_id). Foreign distributed keys are similar in concept to foreign keys in that employees(id) must be referenced by a single tuple in one of teachers, subs, or managers. Another use would be in this situation (something I'm working on right now): I want to link comments by employees by employee_id, but comments from non-employees by name (as they don't have an id). comments(id, comment); comments_nonemployees(comment_id, name); comments_employees(comment_id, employee_id); with constraints comments_nonemployees(comment_id) references comments(id) comments_employees(comment_id) references comments(id) and comments(id) must be listed in either comments_nonemployees(comment_id) or comments_employees(comment_id) I haven't looked very far into how to implement distributed keys and foreign distributed keys in PostgreSQL beyond briefly looking at the pg_constraint system table, thinking a distributed key would be something making employee_id unique in teachers(employee_id) UNION subs(employee_id) UNION managers(employee_id). A distributed key is distributed over a number of tables, rather than a single one, so there'd have to be a list of relid-attnum pairs, rather than a single relid-attnum pair, such as conrelid and conkey in pg_constraint. Here's a brief sketch of the idea: pg_distributed distname name the name of the distributed key constraint distrelid oid the relid of one of the tables involved in the distributed keys distkey int2[] a list of the attnum of the columns of the table with oid distrelid involved in the distributed key distforkey bool true if foreign distributed key distfrelid oid if a foreig
Re: [SQL] Anti log in PostgreSQL
Definition of log (base n) is that log n(x) = y where n^y = x for all values of x and y. n is the base. So a base 10 log would be reversed by doing 10^x=y. If we know x, we use the exponential operation; if we know y we use log(y) = x. For ln (natural logs, base e, e is approx. 2.818), use e^x=y. Hope this explains things. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sort by on two columns
Title: Message SELECT * FROM customers ORDER BY last_name, first_name Works for me. - Original Message - From: Andy Lewis To: [EMAIL PROTECTED] Sent: Saturday, January 03, 2004 8:15 AM Subject: [SQL] sort by on two columns Hi All, Is it possible to sort by two columns? Using the query below? SELECT table1.name, table2.name, FROM table1, table2 WHERE table1.id = table2.id ORDER BY I want to be able to sort the names select from two different tables and two different colums(same data type). Is this possible? Thanks, Andy
Re: [SQL] Calendar Scripts - Quite a complex one
Hi all; If I understand Kumar's post correctly, he is having some question relating to the issue of even recurrance. I would highly suggest reading the ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject. HERMES (my app with appointment/calendar functionality) doesn't yet support appointment recurrance, and I have not formalized my approach to this. However, here is the general approach I have been looking at: 1: Have a separate table of recurrance rules (1:1 with appointments) or have a recurrance datatype. 2: Build some functions to calculate dates and times when the appointment would recurr. You can also have a "Recur Until" field so you can limit your searches this way. 3: Use a view to find recurring appointments on any given day. This avoids a very nasty problem in the prepopulation approach-- that of a cancelled recurring meeting. How do you cancel ALL appropriate instances of the meeting while leaving those that occured in the past available for records? Kumar-- if you are working with PHP, I would be happy to work with you in this endevor so that the same functionality can exist in my open source (GPL'd) application. I think that the source for this would likely be one of those things that might be best LGPL'd if added to my app. Best Wishes, Chris Travers - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 1:06 PM Subject: Re: [SQL] Calendar Scripts - Quite a complex one > Hi, > > The complexity comes while scheduling the appointments. Let us say, I have > scheduled so many meetings in my calendar of various schedules like daily, 3 > days once, weekly, bi weekly. monthly, bi monthly, etc. > > While I open the calendar for end of this year (say Dec 2004), I need to > show those meetings in my calendar, but I have data until Jan 2004. > > What is the best way to show it. Populating the records from Jan 2004 to Dec > 2004 in the pgsql function and display it in the calendar, or just write a > query to generate temporary records only for that Dec 2004 and not storing > them at the database. > > Please shed some idea. > > Regards > Kumar > > - Original Message - > From: "Josh Berkus" <[EMAIL PROTECTED]> > To: "Peter Eisentraut" <[EMAIL PROTECTED]>; "Kumar" <[EMAIL PROTECTED]>; > "psql" <[EMAIL PROTECTED]> > Sent: Wednesday, January 07, 2004 3:43 AM > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > Peter, > > > You can probably lift out the complete calendar functionality from an > > existing groupware solution, say, www.egroupware.org. I'm not sure > > whether it's practical to do the calendar things in the database, since > > you will also need a significant amount of intelligence in the client > > to display reasonable calendar graphics, for instance. > > But all of the appointments, holidays, etc can and should be stored in the > database, and by using function programming one can automate generating all > of the raw data for the calendar graphics. We do this with our legal > calendaring app. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Calendar Scripts - Quite a complex one
Hi Kumar and others; I have never worked with functions to return references to cursors. Is there a reason why it has to be done this way rather than returning a setof appointments? In that case: create function app_today returns setof appointment (date) as ' declare new_appoint appointment; appoint_recur recurrance begin for appointment in [SELECT query] loop -- do calculations if [condition] then return next; end if; end loop; end; ' language plpgsql; Note that the function is off my head and not even guaranteed to be exactly what you need. Best Wishes, Chris Travers - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "Chris Travers" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 7:39 PM Subject: Re: [SQL] Calendar Scripts - Quite a complex one > Hi, > yes yes. U understood in a very correct way, as i have 2 tables - > appointments and recurrences. And we are not gonna use PHP. > > For future dates, I am not gonna populate, instead I am gonna check for the > recurrences tables for ever appointments and based on the conditions, I am > gonna say how many time that appointment recure in that month and the > timestamp. > > To process that I have get all the appointment data and its recurrence > pattern data into the cursor. Is there a way to get the records one by one > from the cursor and calculate it patterns. > > CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp) > RETURNS refcursor AS > 'DECLARE > cal_daily_date ALIAS FOR $1; > ref REFCURSOR; > > BEGIN > OPEN ref FOR > SELECT > > RETURN ref; > > END;' > LANGUAGE 'plpgsql' VOLATILE; > > How to open the cursor here so that I could check its recurrences pattern. > > Please shed some light. > > Regards > kumar > > - Original Message - > From: "Chris Travers" <[EMAIL PROTECTED]> > To: "Kumar" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Peter Eisentraut" > <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> > Sent: Wednesday, January 07, 2004 1:19 PM > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > > Hi all; > > > > If I understand Kumar's post correctly, he is having some question > relating > > to the issue of even recurrance. I would highly suggest reading the > > ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject. > > HERMES (my app with appointment/calendar functionality) doesn't yet > support > > appointment recurrance, and I have not formalized my approach to this. > > However, here is the general approach I have been looking at: > > > > 1: Have a separate table of recurrance rules (1:1 with appointments) or > have > > a recurrance datatype. > > > > 2: Build some functions to calculate dates and times when the appointment > > would recurr. You can also have a "Recur Until" field so you can limit > your > > searches this way. > > > > 3: Use a view to find recurring appointments on any given day. > > > > This avoids a very nasty problem in the prepopulation approach-- that of a > > cancelled recurring meeting. How do you cancel ALL appropriate instances > of > > the meeting while leaving those that occured in the past available for > > records? > > > > Kumar-- if you are working with PHP, I would be happy to work with you in > > this endevor so that the same functionality can exist in my open source > > (GPL'd) application. I think that the source for this would likely be one > > of those things that might be best LGPL'd if added to my app. > > > > Best Wishes, > > Chris Travers > > > > - Original Message - > > From: "Kumar" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" > > <[EMAIL PROTECTED]> > > Sent: Wednesday, January 07, 2004 1:06 PM > > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > > > > > Hi, > > > > > > The complexity comes while scheduling the appointments. Let us say, I > have > > > scheduled so many meetings in my calendar of various schedules like > daily, > > 3 > > > days once, weekly, bi weekly. monthly, bi monthly, etc. > > > > > > While I open the calendar for end of this year (say De
Re: [SQL] Is it possible in PostgreSQL?
Moving thread over to SQL list as it belongs
there.
Bronx: This certainly is possible, but IMO,
not in one query. Actually doing it will be relatively complex. For
purposes of maintenance, I am thinking that doing this would be better handled
by wrapping at least one view.
CREATE VIEW sales_pre_proc AS
SELECT name, quantity, to_char("date", '') AS
year, to_char("date", 'MM') FROM sales;
This is needed for the group by statement
below to function properly:
CREATE VIEW sales_month_summary AS
SELECT name, sum(quantity) AS quantity, year, month
from sales_pre_proc
GROUP BY name, year, month;
This will give you a view that will have the
sum information. Now we just have to create the statement which will
create the pivot effect. I understand that there is something under
contrib/tablefunc for this, but I do not have it on my system (cygwin), at
the moment. Perhaps someone else can help.
Failing that, you can write your own function to
return each row. I was working on a quick proof of concept but it was not
working properly.
Best Wishes,
Chris Travers
- Original Message -
From:
Bronx
To: [EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 6:58
AM
Subject: [ADMIN] Is it possible in
PostgreSQL?
Hi,
I've got problem with one specific query.
I've got the table
with many of rekords like
these:
name
| quantity | date
---
aaa
2
2003-04-01
bbb
4
2003-04-12
ccc
5
2003-05-12
aaa
3
2003-01-14
aaa
1
2003-12-09
bbb
9
2003-08-08
and so on ...
Does
anybody know how make query which return grouped
records by month of year and name
(also sum of quantity).
It is possible to make a query whitch return
something like that:
name | 01 | 02 | 03 | 04 | ... | 12
(months)
aaa
x x x
x ... x
bbb
x x x
x ... x
ccc
x x x
x ... x
where x means sum of quantity in month.
It is possible to make it in one query?
I know that in Access is construction : PIVOT.
Thanks
Adam
Re: [SQL] Database diagram
There is a free Perl script which is called something like pgautodoc which creates DIA diagrams from databases. Take a look for it on Freshmeat. - Original Message - From: "Ganesan Kanavathy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 1:38 PM Subject: [SQL] Database diagram > I have a postgres database with many tables. > > How do I create database diagram? Are there any free tools available to > create database diagram from pgsql database? > > Regards, > Ganesan > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to retrieve N lines of a text field.
Hi all;
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
I had thought about using something like:
select substring(test from '#"' || repeat('%\n', $1) || '#"%' for '#') from
multiline_test;
However, this always selects every line but the final one (because %\n seems
to be interpreted to be the largest possible string, while I want it to be
the smallest possible string).
Is there a workaround? Any other help? Or do I need to write a UDF?
Best Wishes,
Chris Travers
---(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]
You can also use PGexecParams() (see the libpq documentation). It can be a little more cumbersome to use, though. Best Wishes, Chris Travers - Original Message - From: MUKTA To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 8:08 PM Subject: [SQL] Hi I have an urgent problem I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i can not insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); executing above statement with plain integers does fine and inserts them into table.. Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Josh Berkus wrote: Folks, I have a wierd business case. Annoyingly it has to be written in *portable* SQL92, which means no arrays or custom aggregates. I think it may be impossible to do in SQL which is why I thought I'd give the people on this list a crack at it. Solver gets a free drink/lunch on me if we ever meet at a convention. Might be possible. Would certainly be ugly. The Problem: for each "case" there are from zero to eight "timekeepers" authorized to work on the "case", out of a pool of 150 "timekeepers". This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132113 | 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447 | 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL "advanced feature") If it can be done, it might be extremely ugly. I am thinking a massive set of left self joins (since there could be between 0 and 8). Something like: select case_id FROM authorized_timekeeper t0 LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper GROUP BY case_id) t1 ON case_id LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper WHERE timekeeper_id <> t1.timekeeper GROUP BY case_id) t2 ON case_id LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper WHERE timekeeper_id NOT IN (t1.timekeeper, t2.timekeeper) GROUP BY case_id) t3 etc If this is not an option, instead I would create a series of views. Something like: CREATE VIEW t1 AS select case_id, min(timekeeper_id) AS tk_id from authorized_timekeepers group by case_id; CREATE VIEW t2 AS select case_id, min(timekeeper_id) AS tk_id from authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) group by case_id; CREATE VIEW t3 AS select case_id, min(timekeeper_id) AS tk_id FROM authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) AND tk_id NOT IN (SELECT tk_id FROM t2) GROUP BY case_id; Etc. Then you do a left join among the views. Hope that this helps. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Way to stop recursion?
Jonathan Knopp wrote: Sorry, I should have mentioned that there is a lot more to the design that makes this replication necessary, including another two levels to the tree plus the ability to have orphaned children. My first thought was "Dude, use a VIEW" In database design, the SPOT principle applies. *Always* enforce a Single Point Of Truth. If that doesn't seem to be possible, rethink how the data is used and look at how to ensure that there is only ONE authoritative storeage for each piece of transactional data. (Yes, sometimes we get away from this with OLAP installations but the data is not generally being updated there.) In this case, I would create a view (with appropriate rules) which would automatically populate the common fields from the parent if it exists. The issue should not be one of storage but of presentation. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Coalesce() in outer join between views
Hi everyone.
I am trying to create a view that fills in missing values from a
secondary source. I am using PostgreSQL 8.0.3 on Fedora Linux Core 3.
I have two important views and two important tables. Everything works
find by itself but when I try to create an outer join between views
(that hit the same table) coalesce is giving bad results.
The first view is day_source_pre:
View "reporting.day_source_pre"
Column | Type | Modifiers
+--+---
day| date |
amount | double precision |
source | text |
View definition:
( SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount,
payment_types.id AS source
FROM acc_trans, payment_types
WHERE (acc_trans.chart_id IN ( SELECT chart.id
FROM chart
WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~
(('%source='::text || payment_types.id) || '%'::text)
GROUP BY acc_trans.transdate, payment_types.id
UNION
SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount,
'over/under' AS source
FROM acc_trans
WHERE (acc_trans.chart_id IN ( SELECT chart.id
FROM chart
WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~
'%Over/under%'::text
GROUP BY acc_trans.transdate)
UNION
SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount,
'Reset' AS source
FROM acc_trans
WHERE (acc_trans.chart_id IN ( SELECT chart.id
FROM chart
WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~
'%Reset%'::text
GROUP BY acc_trans.transdate;
This works as expected by itself.
The second view is:
View "reporting.day_inc_source"
Column | Type | Modifiers
+--+---
day| date |
sum| double precision |
source | text |
View definition:
SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS sum,
acc_trans.source
FROM acc_trans
WHERE acc_trans.source IS NOT NULL
GROUP BY acc_trans.transdate, acc_trans.source;
This works OK by itself.
The third view (which is where tthe problem is) is defined thuswise:
View "reporting.day_source"
Column | Type | Modifiers
+--+---
day| date |
source | text |
amount | double precision |
View definition:
SELECT day_inc_source."day", day_inc_source.source,
COALESCE(day_source_pre.amount, day_inc_source.sum * -1::double
precision) AS amount
FROM reporting.day_source_pre
RIGHT JOIN reporting.day_inc_source ON day_source_pre.amount =
day_inc_source.sum AND day_source_pre."day" = day_inc_source."day"
WHERE (day_inc_source.source IN ( SELECT payment_types.id
FROM payment_types))
ORDER BY day_inc_source."day";
The problem seems to be somehow assuming that all amount columns in
day_source_pre are null. Is there something wrong in how this view is
working, or is it (more likely) my SQL syntax?
That I want to do is fill in a value from day_inc_source if and only if
it is not found in day_source_pre with the same date and amount.
Best Wishes,
Chris Travers
Metatron Technology Consulting
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] difference between all RDBMSs
Obviously on this list you will mostly get info on PostgreSQL. With regard to PostgreSQL, I would highly suggest familiarizing yourself with the online documentation. I won't cover the weaknesses of MySQL here, but will give you a quick overview on how PostgreSQL is different from other RDBMS's so you can refine your search a bit. PostgreSQL is designed to be extremely extensible. This means that one can easily write code to add data types, procedural languages, and more with very little work. Other database managers may allow for data types to be added, but I am not aware of any others that allow you to define your own procedural langauges in any arbitrary way (Even the recent enhancements to MS SQL to give it access to .Net are not this advanced). The PostgreSQL development team has made data integrity and stability (assuming working hardware) a top priority. It is certainly a higher priority than any other open source RDBMS I have ever worked with. If you want to understand other factors that make PostgreSQL different than other RDBMS's you may want to look into differences regarding: ISO compliance (which features of SQL-99 are supported), the trigger vs. rule systems in PostgreSQL (warning MS SQL uses something they call rules but it is something different), and features like inherited tables. Again, read the online documentation. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] solved: Coalesce() in outer join between views
Hi all; I found the problem (stupid human error ;-) ) Basically it was a broken join condition. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: 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] Trigger on select?
Kyle Bateman 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. When I do an insert,update,delete on the primary data table, another trigger fires that removes the applicable data from the summary table. This way, I only cache the information I need in the summary table, right before I need it. But it can stay there as long as the base information doesn't change so I don't have to redo the expensive operation of summarizing it any more often than necessary. Its kind of like an index in a way, but it is not maintained at insert/update time. Rather, it is updated as it is needed. Anyone have any ideas about how I can accomplish this? something like create view wrapper_table as select * from original table where (select pseudo_trigger_function()) IS TRUE; The above example is off the top of my head. It may require some editing. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to secure PostgreSQL Data for distribute?
Premsun Choltanwanich wrote: Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise. If your customer can access the data, they can access the data. If they have control over the system, they can access the system. I guess you could build some sort of encryption into your client, but that seems pretty easy to circumvent. The short answer is that there is no good way to do this. If you are worried about this, the technology isn't going to save you. No technology will save you. Instead, I would highly suggest discussing the matter with an attourney and see if there is a legal remedy that might provide adequate protection. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] booleans and nulls
Matt L. wrote: Out of curiousity, 1. Does a boolean column occupy 1byte of disk whether or not the value is null or not? I believe so. 2. Is matching on IS NULL or = 0 more efficient? Hmm... = 0 is the same as IS FALSE. Not the same as IS NULL. So I guess it is apples v. oranges 3. If I ix'd columns w/ null does postgres know whatevers not indexed is null or would their be no point? I currently utilize null fields as 'not a value' has meaning in a program i've been working on as I don't want to put false in every column when i only need a couple with a true/false value. I'm not joining tables on NULLS, just filtering w/ them. Sounds like a partial index would be your best bet. Something like: CREATE index ON my_table WHERE my_bool IS NOT NULL Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 1: 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] nullif('','') on insert
Matt L. wrote:
I need to test whether or not a value is null on
insert.
Example: insert into table (column) values
nullif('',''));
ERROR: column "column" is of type boolean but
expression is of type text.
Your problem is that NULL's are typed in PostgreSQL.
Try this:
SELECT NULL;
SELECT NULL::BOOL;
SELECT NULL::BOOL::TEXT;
to see what I mean. This is an exact illustration of your problem.
It works in MSSQL (probably against not standards) but
nonetheless I need to make it work.
I assume it's returning 'NULL' w/ quotes?
Nope. It is returning a text string which is valued at NULL. It cannot
convert a text string to a BOOL (even if the string is a NULL) so it
gives you an error.
I don't know
where to look to alter it. I looked into functions but
all I see is how to write "AS queries" or point to
various snippets. I'd rather just alter the nullif
function.
SELECT NULLIF('' = '', TRUE);
Does this work? You could write a wrapper function if necessary.
Best Wishes,
Chris Travers
Metatron Technology Consulting
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] insert only if conditions are met?
Henry Ortega wrote: What I am trying to do is * Insert a record for EMPLOYEE A to TABLE A IF the sum of the hours worked by EMPLOYEE A on TABLE A is not equal to N Is this possible? Yes, but we will need to see your database schema to provide examples. A simple example might be INSERT INTO table_a SELECT firstname, lastname FROM table_b WHERE table_b IN (SELECT id FROM (SELECT id, sum(labor) as total_hours from table_c group by id) WHERE total_hours <> n); Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: 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] Problem -Postgre sql
Correct me if I am wrong, but isn't COALESCE standard in this way? Best Wishes, Chris Travers Metatron Technology Consulting Michael Glaesemann wrote: [Please do not email me directly. Please post to the list so others may help and benefit from the discussion.] On Oct 19, 2005, at 14:30 , Vikas J wrote: IsNull in sql server has syntax like isnull(column,substitute) if "column" is null it shows value of "substitute". That can be achieved with CASE clause in postrgre but I want alternate function. If you look at the doc links I provided below, you will find that COALESCE does exactly this. Can you tell me how to write function like MAX() that will work directly on colmuns. I want to create my own function that will not need table name as paramter. it shld work similarly to max() function in postgre. [Again, it's PostgreSQL or Postgres. It is *not* spelled "postgre".] In my experience, the max() aggregate function does not require table names as parameters and work on columns directly. http://www.postgresql.org/docs/8.0/interactive/functions-aggregate.html I suggest you take some time to look at the docs. They're quite extensive and helpful. http://www.postgresql.org/docs/8.0/interactive/index.html Michael Glaesemann grzm myrealbox com I'm not quite sure what the ISNULL() function does in SQL Server, but it sounds like it might be similar to either COALESCE or the IS NULL expression. These pages might help you: COALESCE http://www.postgresql.org/docs/8.0/interactive/functions- conditional.html#AEN12056 IS NULL http://www.postgresql.org/docs/8.0/interactive/functions- comparison.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg, mysql comparison with "group by" clause
Greg Stark wrote: "Anthony Molinaro" <[EMAIL PROTECTED]> writes: Greg, You'll have to pardon me... I saw this comment: "I don't see why you think people stumble on this by accident. I think it's actually an extremely common need." Which, if referring to the ability to have items in the select that do not need to be included in the group, (excluding constants and the like) is just silly. Well the "constants and the like" are precisely the point. There are plenty of cases where adding the column to the GROUP BY is unnecessary and since Postgres makes no attempt to prune them out, inefficient. And constants aren't the only such case. The most common case is columns that are coming from a table where the primary key is already included in the GROUP BY list. I sort of see what you are saying but you have yet to convince me In the case of columns coming from a table where the primary key is already in the GROUP BY list it's possible for the database to deduce that it's unnecessary to group on that column. Well The question is really whether two things should be true: 1) whether you want to assume that the programmer is going to know about Single/Multi Value Dependency issues per column. IMO, this is more of a DB design issue than a client app issue. And I would *not* want to make that assumption because for higher normal forms where this is likely to be a consideration, you are likely to have denormalized access via VIEWs anyway. 2) Whether you are willing to rely on looking at the data first to determine whether the query is valid Alternatively we are back to the ability to get the wrong answer with ease and in very difficult to debug ways. I suspect that MySQL places an implicit MIN() around columns not included in the group by statement. I fail to see why this is not an appropriate answer to his concern. But it's also possible to have cases where the programmer has out of band knowledge that it's unnecessary but the database doesn't have that knowledge. The most obvious case that comes to mind is a denormalized data model that includes a redundant column. select dept_id, dept_name, count(*) from employee_list Ok. You have a few choices: SELECT MIN(dept_id), dept_name, count(*) FROM employee_list GROUP BY dept_name; SELECT dept_id, dept_name, count(*) FROM employee_list GROUP BY dept_name, dept_id; SELECT dept_id, MIN(dept_name), count(*) FROM employee_list GROUP BY dept_id; And yes, it is bad design in every case I can think of.// Why is this a problem? Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Blank-padding
Tom Lane wrote: "Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: I remember that discussion, and I was for the change. However, upon doing some testing after reading the above, I wonder if the blank-stripping isn't too aggressive. I have a CHAR(6) field (say, named Z) that has "abc " in it. Suppose I want to append "x" to Z, with any leading spaces in Z PRESERVED. (You meant trailing spaces, I assume.) Why exactly would you want to do that? You decided by your choice of datatype that the trailing spaces weren't significant. I once built a telecom billing app where this might be important (fixed length fields). Lets say you have fixed length fields defined as char(n) datatypes. You may want to build a query to generate billing records like: select field1 || field2 || field3 || field4 || field5 ... AS bill_record FROM lec_billing_entries; It seels to me that I would expect trailing spaces to be preserved in these cases. Having an implicit rtrim function is asking for problems. Personally I would rather have to call rtrim explicitly than have the backend treat the concatenation differently than if I do it on the client. This gripe seems to me exactly comparable to complaining if a numeric datatype doesn't remember how many trailing zeroes you typed after the decimal point. Those zeroes aren't semantically significant, so you have no case.\ My only gripe here is that the implicit rtrimming is going to cause problems in cases where you are trying to do things with fixed-length fields, which is really where one is likely to use bpchar anyway. It is not a showstopper, but I can see why some people don't like it. But can't please everyone :-) Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
