Re: [SQL] pg & Delphi
On Tue, 28 Aug 2007, Kitter Georgiy wrote: which has so rectriction, that client should be written on Delphi (CBuilder). Please, prompt me how to adjust Delphi to use Postgres. We use pgExpress driver from http://vitavoom.com It is commercial, but cheep and works well. You can try ODBC as well, but it is too slow. Daniel ---(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] pg & Delphi
Kitter Georgiy wrote: Good day. I want to enter Postges into project, which has so rectriction, that client should be written on Delphi (CBuilder). Please, prompt me how to adjust Delphi to use Postgres. You should be able to connect via ODBC. Googling "PostgreSQL Delphi" seems to give some useful links. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Request into several DBMS simultaneously on DDL and DML
Kitter Georgiy wrote: (1)Let's allow to __connect into several DBMS__ at once, last of them becomes DBMS 'by default'. ... It's necessary to not signalize about break of foreign key, if remote database will not connected in next time. But that's the vital bit. Without being able to extend relational integrity checks across database boundaries you're not really getting something that's general-use. Without that it's not really worth extending syntax - dblink() offers a solution to the limited cases available. If you manage to come up with an efficient multi-master replication system though, please let us know :-) -- Richard Huxton Archonet Ltd ---(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] fetch first rows of grouped data
On Mon, Aug 27, 2007 at 06:36:47PM +0200, Claudia Kosny wrote: > Is there a way to do this with one query? > I am using PostgreSQL 7.4. you can try to use "distinct on". depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] Database normalization
Hello, > > I have a fairly basic question about database design where im not sure > which approach is considered correct. > > I have two different entities: Clients and Services. Both allow users to > add progressive updates about the two entities. > > The update/message format is exactly the same for both. Should I make two > different tables: > > client_updates and service_updates > or > one table with extra columns : is_client, client_id, service_id, where > either client_id or service_id would be null depending on the is_client > boolean? > > The major problem is simply relating the foreign key in the updates table > back to the correct entity, client or service. > > Regards, > Sebastian > >
Re: [SQL] fetch first rows of grouped data
On Tue, Aug 28, 2007 at 08:00:42AM -0500, Michael Glaesemann wrote: > >you can try to use "distinct on". > I considered that as well, but couldn't think of a way to return more you're right and i was wrong. i simply missed the word "two" in original question. sorry. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Database normalization
Hi I would create one table to log updates with type field and one id filed that contains either client id or service id according to type. On such a table i would forget about foreign keys (thay are better to be avoided anyway if you have millions of records in tables). That way you can share code that displays update history or even create some generic components. In my experience normalized databases have ended up with hundreds of tables and code and screen generation that is very far from what users actually need. Denormalizing and refactoring these databases reduces number of tables by magnitude. Regards, Asko On 8/28/07, Sebastian Ritter <[EMAIL PROTECTED]> wrote: > > Hello, > > > > > I have a fairly basic question about database design where im not sure > > which approach is considered correct. > > > > I have two different entities: Clients and Services. Both allow users to > > add progressive updates about the two entities. > > > > The update/message format is exactly the same for both. Should I make > > two different tables: > > > > client_updates and service_updates > > or > > one table with extra columns : is_client, client_id, service_id, where > > either client_id or service_id would be null depending on the is_client > > boolean? > > > > The major problem is simply relating the foreign key in the updates > > table back to the correct entity, client or service. > > > > Regards, > > Sebastian > > > > >
Re: [SQL] fetch first rows of grouped data
On Aug 28, 2007, at 4:14 , hubert depesz lubaczewski wrote: On Mon, Aug 27, 2007 at 06:36:47PM +0200, Claudia Kosny wrote: Is there a way to do this with one query? I am using PostgreSQL 7.4. you can try to use "distinct on". I considered that as well, but couldn't think of a way to return more than one row per "distinct on condition". I'd be interested to hear if you've thought of a way to use DISTINCT ON in this situation. Michael Glaesemann grzm seespotcode net ---(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] Database normalization
On Tue, Aug 28, 2007 at 12:47:45PM +0100, Sebastian Ritter wrote: > > The update/message format is exactly the same for both. Should I make two > > different tables: > > one table with extra columns : is_client, client_id, service_id, where > > either client_id or service_id would be null depending on the is_client > > boolean? Is the rest of the data the same? If so, then one table is right. If not, then more than one table. In either case, I really hate the idea of two columns, one of which is always null. But if you're going to do that, make sure you add a CHECK constraint where !(col1 IS NULL and col2 IS NULL). A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Database normalization
Thanks for the information. Both tables would be exactly sames apart from the foreign key relation to clients or services. I agree that it seems strange to have one column that is always null. Im using Django as my Object relational Mapper so im pretty sure I can not add a constraint such as : CHECK constraint where !(col1 IS NULL and col2 IS NULL). Another factor ive been considering is that one of the fields in this table(s) definition(s) is free flowing text which could potentially become very large. Should I take this in to consideration when deciding whether to split the tables? In terms of searching speed that is. Kindest regards. Sebastian On 8/28/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > On Tue, Aug 28, 2007 at 12:47:45PM +0100, Sebastian Ritter wrote: > > > The update/message format is exactly the same for both. Should I make > two > > > different tables: > > > > one table with extra columns : is_client, client_id, service_id, > where > > > either client_id or service_id would be null depending on the > is_client > > > boolean? > > Is the rest of the data the same? If so, then one table is right. > If not, then more than one table. In either case, I really hate the > idea of two columns, one of which is always null. But if you're > going to do that, make sure you add a CHECK constraint where !(col1 > IS NULL and col2 IS NULL). > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > I remember when computers were frustrating because they *did* exactly what > you told them to. That actually seems sort of quaint now. > --J.D. Baldwin > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq >
Re: [SQL] Database normalization
On Aug 28, 2007, at 6:47 AM, Sebastian Ritter wrote: Hello, I have a fairly basic question about database design where im not sure which approach is considered correct. I have two different entities: Clients and Services. Both allow users to add progressive updates about the two entities. The update/message format is exactly the same for both. Should I make two different tables: client_updates and service_updates or one table with extra columns : is_client, client_id, service_id, where either client_id or service_id would be null depending on the is_client boolean? The major problem is simply relating the foreign key in the updates table back to the correct entity, client or service. Are client_id and service_id artificial ids? If so, is it an actual requirement that they have distinct id sequences? I.e. is it necessary that there can be both client_id=1 and service_id=1? If not, you can use one table, say Entities, lose client_id, service_id, and is_clent and replace them with entity_id and entity_type. Then your foreign key in your updates table just needs to reference entity_id, or possibly (entity_id, entity_type) if you want to be really strict about things. If you want to make querying the table simple for either case create Clients and Services views on the table. This also gives you the ability to add other entity types where you may to track whatever kind of updates these are. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Database normalization
Hi, The views option sounds best in my opinion but sadly i cant play with things like inheritance or views as Im using Django as my ORM and since its relatively new, it doesnt yet support such database operations. Id like the ids for each "entity" to be serial and hence there would be overlap between client_ids and service_ids. Cheers, Sebastian On 8/28/07, Erik Jones <[EMAIL PROTECTED]> wrote: > > On Aug 28, 2007, at 6:47 AM, Sebastian Ritter wrote: > > > Hello, > > > > I have a fairly basic question about database design where im not > > sure which approach is considered correct. > > > > I have two different entities: Clients and Services. Both allow > > users to add progressive updates about the two entities. > > > > The update/message format is exactly the same for both. Should I > > make two different tables: > > > > client_updates and service_updates > > or > > one table with extra columns : is_client, client_id, service_id, > > where either client_id or service_id would be null depending on the > > is_client boolean? > > > > The major problem is simply relating the foreign key in the updates > > table back to the correct entity, client or service. > > Are client_id and service_id artificial ids? If so, is it an actual > requirement that they have distinct id sequences? I.e. is it > necessary that there can be both client_id=1 and service_id=1? If > not, you can use one table, say Entities, lose client_id, service_id, > and is_clent and replace them with entity_id and entity_type. Then > your foreign key in your updates table just needs to reference > entity_id, or possibly (entity_id, entity_type) if you want to be > really strict about things. If you want to make querying the table > simple for either case create Clients and Services views on the > table. This also gives you the ability to add other entity types > where you may to track whatever kind of updates these are. > > Erik Jones > > Software Developer | Emma(r) > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > >
Re: [SQL] Database normalization
Hi, On 8/28/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > On Tue, Aug 28, 2007 at 03:37:22PM +0100, Sebastian Ritter wrote: > > Thanks for the information. > > > > Both tables would be exactly sames apart from the foreign key relation > to > > clients or services. > > Hmm. Are the services or clients tables different? A useful rule of > thumb is that, to the extent you can sort things into "kinds of > data", then you should have exactly one space for each one. (I hope > that's clear.) The table definition is exactly the same. The only difference is whether the row refers to a client or service. > Another factor ive been considering is that one of the fields in this > > table(s) definition(s) is free flowing text which could potentially > become > > very large. Should I take this in to > > consideration when deciding whether to split the tables? In terms of > > searching speed that is. > > I'd put it in its own table, probably, unless you're going to use it > frequently. Why would frequency of use change whether or not I use one or two tables? Sebastian
Re: [SQL] Database normalization
On Tue, Aug 28, 2007 at 04:15:01PM +0100, Sebastian Ritter wrote: > > I'd put it in its own table, probably, unless you're going to use it > > frequently. > > Why would frequency of use change whether or not I use one or two tables? If you have a possibly-large field that does not get used very much, you have to pay the I/O for it every time you look at that row, even if it's not used. Also, it sounds like it might not be used by every row? In that case, normalization calls for it to be pushed out too. A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(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] Database normalization
Im using Django as my Object relational Mapper so im pretty sure I can not add a constraint such as ... Then you should seriously consider changing your mapper. >>> "Sebastian Ritter" <[EMAIL PROTECTED]> 2007-08-28 16:37 >>> Thanks for the information. Both tables would be exactly sames apart from the foreign key relation to clients or services. I agree that it seems strange to have one column that is always null. Im using Django as my Object relational Mapper so im pretty sure I can not add a constraint such as : CHECK constraint where !(col1 IS NULL and col2 IS NULL). Another factor ive been considering is that one of the fields in this table(s) definition(s) is free flowing text which could potentially become very large. Should I take this in to consideration when deciding whether to split the tables? In terms of searching speed that is. Kindest regards. Sebastian On 8/28/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Tue, Aug 28, 2007 at 12:47:45PM +0100, Sebastian Ritter wrote: > > The update/message format is exactly the same for both. Should I make two > > different tables: > > one table with extra columns : is_client, client_id, service_id, where > > either client_id or service_id would be null depending on the is_client > > boolean? Is the rest of the data the same? If so, then one table is right. If not, then more than one table. In either case, I really hate the idea of two columns, one of which is always null. But if you're going to do that, make sure you add a CHECK constraint where !(col1 IS NULL and col2 IS NULL). A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Database normalization
On Tue, Aug 28, 2007 at 03:37:22PM +0100, Sebastian Ritter wrote: > Thanks for the information. > > Both tables would be exactly sames apart from the foreign key relation to > clients or services. Hmm. Are the services or clients tables different? A useful rule of thumb is that, to the extent you can sort things into "kinds of data", then you should have exactly one space for each one. (I hope that's clear.) > Another factor ive been considering is that one of the fields in this > table(s) definition(s) is free flowing text which could potentially become > very large. Should I take this in to > consideration when deciding whether to split the tables? In terms of > searching speed that is. I'd put it in its own table, probably, unless you're going to use it frequently. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Database normalization
Thanks guys, Sebastian On 8/28/07, Bart Degryse <[EMAIL PROTECTED]> wrote: > > Im using Django as my Object relational Mapper so im pretty sure I > can not add a constraint such as ... > Then you should seriously consider changing your mapper. > > >>> "Sebastian Ritter" <[EMAIL PROTECTED]> 2007-08-28 16:37 >>> > Thanks for the information. > > Both tables would be exactly sames apart from the foreign key relation to > clients or services. I agree that it seems strange to have one column that > is always null. Im using Django as my Object relational Mapper so im pretty > sure I can not add a constraint such as : CHECK constraint where !(col1 > IS NULL and col2 IS NULL). > > Another factor ive been considering is that one of the fields in this > table(s) definition(s) is free flowing text which could potentially become > very large. Should I take this in to > consideration when deciding whether to split the tables? In terms of > searching speed that is. > > Kindest regards. > Sebastian > > On 8/28/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > > > On Tue, Aug 28, 2007 at 12:47:45PM +0100, Sebastian Ritter wrote: > > > > The update/message format is exactly the same for both. Should I > > make two > > > > different tables: > > > > > > one table with extra columns : is_client, client_id, service_id, > > where > > > > either client_id or service_id would be null depending on the > > is_client > > > > boolean? > > > > Is the rest of the data the same? If so, then one table is right. > > If not, then more than one table. In either case, I really hate the > > idea of two columns, one of which is always null. But if you're > > going to do that, make sure you add a CHECK constraint where !(col1 > > IS NULL and col2 IS NULL). > > > > A > > > > -- > > Andrew Sullivan | [EMAIL PROTECTED] > > I remember when computers were frustrating because they *did* exactly > > what > > you told them to. That actually seems sort of quaint now. > > --J.D. Baldwin > > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faq > > > >
[SQL] Was: fetch first rows of grouped data
Hi there
I have a list of events that take place in a certain city at a
certain date. Now I would like to have the first two (ordered by
date) events for each city.
Is there a way to do this with one query?
I am using PostgreSQL 7.4.
Thanks for any tips.
Claudia
I think I may have come up with a possible solution.
Create a selection that produces a unique identifier for each city
ordered by date then use array_accum to collect the unique identifiers
for each city, then match the first two elements of the array with the
identifiers.
For instance if you had a table :
CREATE TABLE crazy_talk (
ct_id bigserial primary key,
ct_city text,
ct_date date,
ct_data text
) ;
Then you could use :
SELECT
ct_id ,
ct_city ,
ct_date ,
ct_data
FROM
crazy_talk ,
(SELECT
ct_city AS city,
array_accum(ct_id) as match
FROM
crazy_talk
ORDER BY
ct_city ,
ct_date
GROUP BY
ct_city ) AS data_set
WHERE
ct_city = city AND
ct_id IN (match[0],match[1])
ORDER BY
ct_city ,
ct_date
;
I hope this helps, I did not try it, but I think it should work.
PS if you don't have array_accum here it is :
CREATE AGGREGATE array_accum (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] pg & Delphi
--- Kitter Georgiy <[EMAIL PROTECTED]> escribió: > Good day. > > I want to enter Postges into project, > which has so rectriction, that client should be > written on Delphi (CBuilder). > Please, prompt me how to adjust Delphi to use > Postgres. > You can to use: ZeoslibDBO (free, but it hasn´t ssl soport). I test it and work fine. http://downloads.sourceforge.net/zeoslib/ZEOSDBO-6.6.1_beta.zip?modtime=1172471217&big_mirror=0 PostgresDAC (free trial, it has ssl soport). I use it and work very fine-fast. http://www.microolap.com/downloads/postgresdac/pg.zip They are use as the BDE component. Good luck ! SACL ¡Sé un mejor besador! Compartí todo lo que sabés sobre besos en http://ar.yahoo.com/promos/mejorbesador.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Was: fetch first rows of grouped data
Sorry my last post isnt complete.
This would have to be part of a sub select. It would be more lilke:
SELECT * from cities LEFT OUTER JOIN (SELECT c.id as city_id, event.id FROM
cities c LEFT OUTER JOIN events e ON (c.id = e.city_id) WHERE c.id =
cities.id ORDER BY e.date DESC LIMIT 2) as x ON (cities.id = x.city_id)
I think that would work.
Seb
On 8/28/07, Sebastian Ritter <[EMAIL PROTECTED]> wrote:
>
> Hi There,
>
> You can do something like :
>
> SELECT * FROM cities c LEFT OUTER JOIN events e ON (c.id =e.city_id) ORDER
> BY e.date DESC LIMIT 2
>
> The left outer join here would ensure that cities with no events are also
> added to your result set.
>
> Seb
>
> On 8/28/07, Guy Fraser <[EMAIL PROTECTED]> wrote:
> >
> > Hi there
> >
> > I have a list of events that take place in a certain city at a
> > certain date. Now I would like to have the first two (ordered by
> > date) events for each city.
> > Is there a way to do this with one query?
> > I am using PostgreSQL 7.4.
> >
> > Thanks for any tips.
> >
> > Claudia
> >
> >
> > I think I may have come up with a possible solution.
> >
> > Create a selection that produces a unique identifier for each city
> > ordered by date then use array_accum to collect the unique identifiers
> > for each city, then match the first two elements of the array with the
> > identifiers.
> >
> > For instance if you had a table :
> >
> > CREATE TABLE crazy_talk (
> > ct_id bigserial primary key,
> > ct_city text,
> > ct_date date,
> > ct_data text
> > ) ;
> >
> > Then you could use :
> >
> > SELECT
> > ct_id ,
> > ct_city ,
> > ct_date ,
> > ct_data
> > FROM
> > crazy_talk ,
> > (SELECT
> > ct_city AS city,
> > array_accum(ct_id) as match
> > FROM
> > crazy_talk
> > ORDER BY
> > ct_city ,
> > ct_date
> > GROUP BY
> > ct_city ) AS data_set
> > WHERE
> > ct_city = city AND
> > ct_id IN (match[0],match[1])
> > ORDER BY
> > ct_city ,
> > ct_date
> > ;
> >
> > I hope this helps, I did not try it, but I think it should work.
> >
> > PS if you don't have array_accum here it is :
> >
> > CREATE AGGREGATE array_accum (
> > BASETYPE = anyelement,
> > SFUNC = array_append,
> > STYPE = anyarray,
> > INITCOND = '{}'
> > );
> >
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
Re: [SQL] pg & Delphi
On 8/28/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > Kitter Georgiy wrote: > > Good day. > > > > I want to enter Postges into project, > > which has so rectriction, that client should be written on Delphi > > (CBuilder). > > Please, prompt me how to adjust Delphi to use Postgres. > > You should be able to connect via ODBC. Googling "PostgreSQL Delphi" > seems to give some useful links. > Delphi should let you use dlls written in other languages (it did when I used Delphi last, which has been years). So I'd just use libpq.dll. -Josh ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
