Re: [SQL] Update and trigger
Thanks...but a difference seems to be that the rule is not specific to
update on a particular col but any col of a row getting updated...
Thanks
On Tue, Jun 10, 2008 at 10:21 PM, A. Kretschmer <
[EMAIL PROTECTED]> wrote:
> am Tue, dem 10.06.2008, um 18:45:51 -0700 mailte Medi Montaseri folgendes:
> > Hi,
> >
> > I need to increament a counter such as myTable.Counter of type integer
> > everytime myTable.status a boolean column is updated. Can you help me
> complete
> > this...
> >
> > create trigger counter_trigger after update on myTable.counter
> > execute procedure 'BEGIN statement; statement; statement END'
>
> much simpler, use a RULE instead a TRIGGER like my example:
>
> Suppose, i have a table called foo, it contains now:
>
> test=# select * from foo;
> i
> ---
> 1
> 2
> (2 rows)
>
>
> I create a sequence and a RULE:
>
> test=*# create sequence foo_counter;
> CREATE SEQUENCE
> test=*# create or replace rule foo_update as on update to foo do also
> select nextval('foo_counter');
> CREATE RULE
>
>
> And now i do a update on foo:
>
>
> test=*# update foo set i=2;
> nextval
> -
> 1
> (1 row)
>
> test=*# update foo set i=3;
> nextval
> -
> 2
> (1 row)
>
>
> test=*# select currval('foo_counter');
> currval
> -
> 2
> (1 row)
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
[SQL] Update and trigger
Hi, I need to increament a counter such as myTable.Counter of type integer everytime myTable.status a boolean column is updated. Can you help me complete this... create trigger counter_trigger after update on myTable.counter execute procedure 'BEGIN statement; statement; statement END' Q1- how do I narrow the event to "update on a column not a row" Q2- can I just provide an inline procedure for the execute Thanks Medi
Re: [SQL] Conceptual Design Question
Assuming common semantics for a given field then the question of breaking it to many parts is also a function of its size as related to I/O. We know that memory allocation and I/O read/writes are not granular to bytes and are rather blocks of bytes as it travels from VM (virtual memory) all the way down to sectors on disk. Hence a common field of say 2000 bytes will most likely cause multiple I/O requests where application layer did not have any use for 80% of it, 80% of the times. Having said that, 1 Gig of RAM is about $25 at your local Cosco with a free slice of pizzaperformance tuning paradigms are in big time flux and are really uncle Bob's war stories cheers On Tue, Jun 10, 2008 at 11:35 AM, Steve Midgley <[EMAIL PROTECTED]> wrote: > At 10:52 AM 6/10/2008, [EMAIL PROTECTED] wrote: > >> Date: Tue, 10 Jun 2008 05:05:24 -0700 >> From: Bryan Emrys <[EMAIL PROTECTED]> >> To: pgsql-sql@postgresql.org >> Subject: Conceptual Design Question >> Message-ID: <[EMAIL PROTECTED]> >> >> Hello Everyone, >> >> In a text-heavy database, I'm trying to make an initial design decision in >> the following context. >> >> There is a lot of long text that I could break down into three different >> categories: >> > [snip] > >> The conceptual question is what are the trade-offs between having one >> textual table compared with multiple text tables? Any help on pointing out >> practical considerations would be appreciated. >> >> Thanks. >> >> Bryan >> > > Hi Bryan, > > Firstly, I might investigate the GiST index and TSearch2 in this regard. > I'm not an expert on them, and it maybe is cart before the horse, but if > those tools are applicable and are easier to implement/maintain with one > design approach or the other, I might use their design "preferences" as my > guide for picking the "right" relationships. > > Beyond that advice, it does seem to me that a polymorphic relationship > (where one table holds multiple entities) *could* describe laws and > treaties, though they are kind of different in their relations. Commentaries > seem pretty distinct from these two things. > > My overall opinion would also depend on the architecture. Will you have a > unified middleware/ORM layer that can manage the business rules for the > polymorphic data retrieval? Or will developers be going directly into the > database to pull items directly? > > If you have a unified ORM that stores the business rules, you can be more > aggressive about using polymorphism, b/c the complexity can be hidden from > most developers. > > All in all, I think your model is really describing three distinct data > entities, and should be stored in three separate tables, but that's a very > high level and uninformed opinion! I'd let TSearch2 drive your design if > that's a relevant consideration. Of course TSearch2 is very flexible so it > might not really care much about this. :) > > In general, I find that a data model that "looks like" the real data is the > one that I'm happiest with - the systems I've seen with too much UML > optimization and collapsing of sets of data into single tables tend to be > harder to maintain, etc. > > Just some random opinions for you there. I'm sure others have different > perspectives which are equally or more valid! > > Best, > > Steve > > > -- > 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] Query question
Thanks Stephan, My real DDL include a forign key reference to T2.id and since I am ok with NULL value then the "left outer join" indeed have solved the problem. Thanks again Medi On Thu, May 22, 2008 at 2:50 PM, Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Thu, 22 May 2008, Medi Montaseri wrote: > > > Hi, > > I can use some help with the following query please. > > > > Given a couple of tables I want to do a JOIN like operation. Except that > one > > of the columns might be null. > > > > create table T1 ( id serial, name varchar(20) ); > > create table T2 ( id serial, name varchar(20) ); > > create table T1_T2 ( id serial, t1_id integer not null , t2_id integer ); > > > > Now I'd like to show a list of records from T1_T2 but reference T1 and T2 > > for the names instead of IDs. But T1_T2.t2_id might be null > > > > select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2 > > where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id > > What would you want it to do if T1_T2.t2_id has a value that isn't in T2? > And should it do it for both T2 and T1? If using a NULL name is okay for > both, you can look at outer joins, something like: > > select T1_T2.id, T1.name, T2.name from > T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) > left outer join T2 on (T1_T2.t2_id = T2.id) > > T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) will for example give > you a row even if there's not a row in T1 with T1.id being the same as > T1_T2.t1_id. In that case, you'll get the fields from T1_T2 and NULLs for > the fields from T1. The same between that table and T2 occurs with the > second outer join. > >
[SQL] Query question
Hi, I can use some help with the following query please. Given a couple of tables I want to do a JOIN like operation. Except that one of the columns might be null. create table T1 ( id serial, name varchar(20) ); create table T2 ( id serial, name varchar(20) ); create table T1_T2 ( id serial, t1_id integer not null , t2_id integer ); Now I'd like to show a list of records from T1_T2 but reference T1 and T2 for the names instead of IDs. But T1_T2.t2_id might be null select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2 where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id Basically since t2_id might be null, the condition will fail and the query will fail thanks Medi
Re: [SQL] Data layer migration from MSSQL
I think the grammer should help the parser to determine what you mean when the token ORDER is seen. for example in a select statement... Syntax: SELECT expression [, ...] ... [ FROM from_item [, ...] ] [ WHERE condition ] ... [ ORDER BY expression [ ASC | DESC | USING operator ] keywords (or reserved words) should not be placed in quotes because the parser relies on them to steer ... Cheers Medi On Thu, Feb 21, 2008 at 9:47 AM, Sebastian Rychter <[EMAIL PROTECTED]> wrote: > Hi, I'm working on migrating a data layer from MS SQL Server to > PostgreSQL 8.2 and found that some reserved words should only be written > between quotes and thus are case sensitive (actually only happened with a > table field named "order"). Is there any way to bypass this case > sensitivity or at least determine that I am going to use certain reserved > words as table fields (or any other possible solutions) ? > > > > Thanks, > > > > Sebastian >
Re: [SQL] accounting schema
Thanks Steve... This is all well and good...I am getting it...but I need to chew on it moregnucash was a good one...didn't think of thatgot to get passed all the GUI stuff...but...excellent ref... I suppose instead of AR and AP tables, I can just have one table called Entry (thanks Joe) with an attribute indicating AR vs AP. I am also in favor of Double Entry and accrual postingwhat do I know, I hear corporations do it this way... I also like the "audit trail" built-in feature...actually this was something I was baffled about...I was originally thinking about editing capability, but what you are suggesting is that in practice, you don't change or erase anything...to make a correction, you debit and then credit (keeping the sum happy) and yet you have the audit trail...very cool...I am liking this stuff... And finally you mentioned that bank accounts are tricky...can you expand on this please. After all I am under the impression that "bank accounts" are a corner stone of this whole book keeping...I mean...bank accounts have debits and credits just like any account...eg interest earned is a credit and bank fees are debits...what worries you about bank accounts... Thanks guys...this is very very nice Medi On Feb 6, 2008 6:35 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: > At 05:09 PM 2/6/2008, [EMAIL PROTECTED] wrote: > >Date: Wed, 6 Feb 2008 17:08:54 -0800 > >From: "Medi Montaseri" <[EMAIL PROTECTED]> > >To: pgsql-sql@postgresql.org > >Subject: accounting schema > >Message-ID: > ><[EMAIL PROTECTED]> > > > >Hi, > > > >I am learning my way into Accounting and was wondering how Accounting > >applications are designed. perhaps you could point the way > > > >On one hand, accountants talk about a sacret equation A = L + OE > >(Asset = > >Libility + Owner Equity) and then under each categories there are one > >or > >many account. On the other hand a DBA thinks in terms of tables and > >relations. Instead of getting theoritical, allow me to setup an > >example > > Hi Medi, > > You might read some source code and docs for open source accounting > software and see "how it's done." Here's one example that might be > interesting and simple enough to follow: > > http://www.gnucash.org/ > > In general, I think there are many different accounting methods, so you > have to get clear about which one you're using. "Double-entry" > accounting is common. Cash vs. accrual posting methods matter (i.e. > when does an expense or receivable "charge" against the assets > balance?) > > My most basic understanding is that in general you track assets as they > come in, to an "Accounts Receivable" ledger (i.e. table) and > liabilities to an "Accounts Payable" ledger. Then you reconcile these > two "books" into a "General Ledger" table which gives you something > like an audit trail of all activity (and a running balance). I'm sure > Wikipedia will define these three terms and lots more with greater > clarity. > > But my (limited) experience with accounting schema is that they often > involve these three tables (AR/AP/GL) at their core. > > As you add bank accounts, complex investment instruments, depreciation > etc, things get considerably more complex of course. > > I'll readily admit my limited experience, and I'm sure others on this > list have far better information. I hope this gets you started anyway. > > Sincerely, > > Steve > >
[SQL] accounting schema
Hi, I am learning my way into Accounting and was wondering how Accounting applications are designed. perhaps you could point the way On one hand, accountants talk about a sacret equation A = L + OE (Asset = Libility + Owner Equity) and then under each categories there are one or many account. On the other hand a DBA thinks in terms of tables and relations. Instead of getting theoritical, allow me to setup an example Say you have have construction project (like a room addition) or one of those flip this house deals Owner brings the land (equity) of say worth $100K Expenses begin to mount ( that is a minus against OE) Account Payble begins to mount (that is a liability) And one day you experience a sale As a DBA, (and keeping it simple) I am thinking I need a table for every account which migh look like id, description, credit, debit, validated, created_on, created_by, modified_on, modified_by Is that pretty match it ? Please let me know if you have seen some accounting or DB book that addresses this problem domain. Thanks Medi
Re: [SQL] UTF8 encoding and non-text data types
Here is my traces from perl CGI code, I'll include two samples one in ASCII
and one UTF so we know what to expect
Here is actual SQL statement being executed in Perl and DBI. I do not quote
the numerical value, just provided to DBI raw.
insert into t1 (c1, cost) values ('tewt', 1234)
this works find
insert into t1 (c1, cost) values ('شد',
۱۲۳۴)
DBD::Pg::db do failed: ERROR: syntax error at or near ";" at character 59,
And the PG log itself is very similar and says
ERROR: syntax error at or near ";" at character 59
Char 59 by the way is the first accurance of semi-colon as in 䕱 which
is being caught by PG parser.
Medi
On Jan 14, 2008 12:18 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:
>
> On Jan 13, 2008 8:51 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:
> At 02:22 PM 1/13/2008, [EMAIL PROTECTED] wrote:
> >Date: Sat, 12 Jan 2008 14:21:00 -0800
> >From: "Medi Montaseri" < [EMAIL PROTECTED]>
> >To: pgsql-sql@postgresql.org
> >Subject: UTF8 encoding and non-text data types
> >Message-ID:
> >< [EMAIL PROTECTED]>
> >
> >I understand PG supports UTF-8 encoding and I have sucessfully
> >inserted
> >Unicode text into columns. I was wondering about other data types such
> >as
> >numbers, decimal, dates
> >
> >That is, say I have a table t1 with
> >create table t1 { name text, cost decimal }
> >I can insert UTF8 text datatype into this table with no problem
> >But if my application attempts to insert numbers encloded in UTF8,
> >then I
> >get wrong datatype error
> >
> >Is the solution for the application layer (not database) to convert
> >the
> >non-text UTF8 numbers to ASCII and then insert it into database ?
> >
> >Thanks
> >Medi
>
> Hi Medi,
>
> I have only limited experience in this area, but it sounds like you
> sending your numbers as strings? In your example:
>
> >create table t1 { name text, cost decimal };
>
> insert into t1 (name, cost) values ('name1', '1');
>
> I can't think of how else you're sending numeric values as UTF8? I know
> that Pg will accept numbers as strings and convert internally (that has
> worked for me in some object relational environments where I don't
> choose to cope with data types), but I think it would be better if you
> simply didn't send your numeric data in quotations, whether as UTF8 or
> ASCII. If you don't have control over this layer (that quotes your
> values), then I'd say converting to ASCII would solve the problem. But
> better to convert to numeric and not ship quoted strings at all.
>
> I may be totally off-base and missing something fundamental and I'm
> very open to correction (by anyone), but that's what I can see here.
>
> Best regards,
>
> Steve
> At 11:01 AM 1/14/2008, Medi Montaseri wrote:
> Thanks Steve,
>
> Actually I do not insert text data into my numeric field.
> As I mentioned given
> create table t1 { name text, cost decimal }
> then I would like to insert numeric data into column "cost" because then I
> can later benefit from numerical operators like SUM, AVG, etc
>
> More specifically, I am using HTML, Perl and PG. So from the HTML point of
> view a textfield is just some strings. So my user would enter 12345 but
> expressed in UTF8. Perl would get this and use DBI to insert it into PG
>
> What I am experiencing now is that DB errors that I am trying to insert an
> incorrect data into column "cost" which is numeric and the data is coming in
> from HTML in UTF8
>
> Mybe I have to convert it to ASCII numbers in Perl before inserting them
> into PG
>
> Thanks
> Medi
>
>
> Hi Medi,
>
> I agree that you should convert your values in Perl before handing to DBI.
> I'm not familiar with DBI but presumably if you're sending it UTF8 values
> it's attempting to quote them or do something with them, that a numeric
> field in Pg can't handle. Can you trap/monitor the exact sql statement that
> is generated by DBI and sent to Pg? That would help a lot in knowing what it
> is doing, but I suspect if you just convert your numbers from the HTML/UTF8
> source values into actual Perl numeric values and then ship to DBI you'll be
> better off. And you'll get some input validation for free.
>
> I hope this helps,
>
> Steve
>
Re: [SQL] UTF8 encoding and non-text data types
Thanks Steve,
Actually I do not insert text data into my numeric field.
As I mentioned given
create table t1 { name text, cost decimal }
then I would like to insert numeric data into column "cost" because then I
can later benefit from numerical operators like SUM, AVG, etc
More specifically, I am using HTML, Perl and PG. So from the HTML point of
view a textfield is just some strings. So my user would enter 12345 but
expressed in UTF8. Perl would get this and use DBI to insert it into PG
What I am experiencing now is that DB errors that I am trying to insert an
incorrect data into column "cost" which is numeric and the data is coming in
from HTML in UTF8
Mybe I have to convert it to ASCII numbers in Perl before inserting them
into PG
Thanks
Medi
On Jan 13, 2008 8:51 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:
> At 02:22 PM 1/13/2008, [EMAIL PROTECTED] wrote:
> >Date: Sat, 12 Jan 2008 14:21:00 -0800
> >From: "Medi Montaseri" <[EMAIL PROTECTED]>
> >To: pgsql-sql@postgresql.org
> >Subject: UTF8 encoding and non-text data types
> >Message-ID:
> ><[EMAIL PROTECTED]>
> >
> >I understand PG supports UTF-8 encoding and I have sucessfully
> >inserted
> >Unicode text into columns. I was wondering about other data types such
> >as
> >numbers, decimal, dates
> >
> >That is, say I have a table t1 with
> >create table t1 { name text, cost decimal }
> >I can insert UTF8 text datatype into this table with no problem
> >But if my application attempts to insert numbers encloded in UTF8,
> >then I
> >get wrong datatype error
> >
> >Is the solution for the application layer (not database) to convert
> >the
> >non-text UTF8 numbers to ASCII and then insert it into database ?
> >
> >Thanks
> >Medi
>
> Hi Medi,
>
> I have only limited experience in this area, but it sounds like you
> sending your numbers as strings? In your example:
>
> >create table t1 { name text, cost decimal };
>
> insert into t1 (name, cost) values ('name1', '1');
>
> I can't think of how else you're sending numeric values as UTF8? I know
> that Pg will accept numbers as strings and convert internally (that has
> worked for me in some object relational environments where I don't
> choose to cope with data types), but I think it would be better if you
> simply didn't send your numeric data in quotations, whether as UTF8 or
> ASCII. If you don't have control over this layer (that quotes your
> values), then I'd say converting to ASCII would solve the problem. But
> better to convert to numeric and not ship quoted strings at all.
>
> I may be totally off-base and missing something fundamental and I'm
> very open to correction (by anyone), but that's what I can see here.
>
> Best regards,
>
> Steve
>
>
[SQL] UTF8 encoding and non-text data types
I understand PG supports UTF-8 encoding and I have sucessfully inserted
Unicode text into columns. I was wondering about other data types such as
numbers, decimal, dates
That is, say I have a table t1 with
create table t1 { name text, cost decimal }
I can insert UTF8 text datatype into this table with no problem
But if my application attempts to insert numbers encloded in UTF8, then I
get wrong datatype error
Is the solution for the application layer (not database) to convert the
non-text UTF8 numbers to ASCII and then insert it into database ?
Thanks
Medi
