[SQL] integrity of column used to order rows
hello, I was hoping someone might be able to help me with this problem... I have a table that is essentially joined back to a parent table. I have a column in the child table called "rank" that is a simple integer data type, used to indicate the order that the child elements should be displayed in. What I want to be able to do is make sure that at all times the child records linked to a parent record have values for the "rank" field that are consecutive starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7). Can someone offer the best suggestion to do this? Should I write a PL/pgsql function and add it as a column constraint to check to make sure the numbers are consecutive? Can I use some kind of trigger that will execute a function and "automatically" give the fields the correct number? Would this seriously impact the performance since it would have to go through what might become a large table only to work on a small part of it (ie, records with the same parent_id)? I have a lot of experience with mySQL but a lot of these more sophisticated pgSQL features are a little tough for me to get a handle on immediately... thanks very much. -Cliff ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Dummy Needs Help
* Alder <[EMAIL PROTECTED]> menulis:
> I'm pretty new to SQL and can't figure out how to write what should be a
> simple query of two tables. Could someone here possibly help me out?
>
> Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE
> Table 2 also has two fields of interest:TITLE_NO and PAIDTO_DATE
>
> TITLE_NO is unique in Table 1, meaning each TITLE will have a unique
> EFFECT_DATE. Table 2 represents an accounting history of each TITLE, so for
> each TITLE_NO there may be one or more PAIDTO_DATE values. In both Tables
> the _DATE fields are stored as 9-character strings in the fomat MMDD.
It's probably better to store them as type date instead. That way you
can use functions like date_part() to extract the month and day.
You could use string functions to extract, say, the last 4 characters to
get the MMDD value, but that can get messy if someone accidentally
stores a date in YYMMDD format.
> In all cases, the MM and DD values in Table 2 should be identical with those
> in Table 1.
>
> My intention is to produce a list that will contain the TITLE_NO of each
> TITLE where the MMDD value for EFFECT_DATE differ from any of the
> PAIDTO_DATE values for that TITLE_NO. The list must contain ONLY the
> PAIDTO_DATE values that differ, and the corresponding TITLE_NO.
>
> Sorry I can't explain this a little more technically, but if anyone can
> advise me, that would be fabulous.
If you change your tables to store the dates as type date instead of
type string, then you can do something like the following:
SELECT table2.title_no, table2.paidto_date
FROM table1, table2
WHERE table1.title_no=table2.title_no
AND (date_part('month', table1.effect_date) !=
date_part('month', table2.paidto_date)
OR date_part('day', table1.effect_date) !=
date_part('day', table2.paidto_date));
(At least, I think that will work, but I never trust any SQL I write
without thoroughly testing it first..;)
--
Cliff Crawford
He who sacrifices his conscience to ambition
burns a picture to obtain the ashes. - Chinese proverb
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] SQL Dummy Needs Help
* Cliff Crawford <[EMAIL PROTECTED]> menulis:
>
> SELECT table2.title_no, table2.paidto_date
> FROM table1, table2
> WHERE table1.title_no=table2.title_no
> AND (date_part('month', table1.effect_date) !=
> date_part('month', table2.paidto_date)
> OR date_part('day', table1.effect_date) !=
> date_part('day', table2.paidto_date));
Ooops, I keep confusing C and SQL operators...the "!=" above should be "<>".
--
Cliff Crawford
He who sacrifices his conscience to ambition
burns a picture to obtain the ashes. - Chinese proverb
---(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] Dateadd
* Ligia Pimentel <[EMAIL PROTECTED]> menulis: > I need to know if there is a sql function implemented in postgres that gives > me a date plus any number of days, months or years (the traditional dateadd > function) or how to do it in sql? cepat=# select '01-01-00'::date + '2 months 10 days'::interval; ?column? 2000-03-11 00:00:00-05 (1 row) -- Cliff Crawford http://www.sowrong.org/ birthday party cheesecake jellybean BOOM ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Orac le ROWNUM = PSQL ???
* Robert Beer <[EMAIL PROTECTED]> menulis: > Oracle has a ROWNUM pseudo column that works like this ... > TEST>select callid, rownum from cs_calls where rownum < 5; > > CALLID ROWNUM > -- -- > 7806 1 > 7807 2 > 7809 3 > 6443 4 > > 4 rows selected. > > ... which can be quite handy. > > Is there something like this in PSQL? You can use the LIMIT clause: SELECT callid FROM cs_calls LIMIT 4; See <http://postgresql.readysetnet.com/users-lounge/docs/7.1/postgres/sql-select.html> for more info. > By the way, having used Oracle since 1987 it's a pleasure to use PSQL. > Someone actually thinks about the implemented features. > For example, Oracle's implementation of ROWNUM gives them in the order the > rows were BEFORE the ORDER BY, which is not of much use as adding an ORDER > BY jumbles them up. Duh! LIMIT in PostgreSQL applies *after* ORDER BY, so you won't have this problem :) -- Cliff Crawford http://www.sowrong.org/ birthday party cheesecake jellybean BOOM ---(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] skip weekends
On Thu, 2002-06-20 at 23:08, Rudi Starcevic wrote: > Hello, > > Nice reply Josh. > I wouldn't call your solution 'ugly' at all. > > It's an excellent example of a real world need for Postgresql functions. > I've also been looking at other functions at > http://www.brasileiro.net/postgres/cookbook/. > I noticed your name amongst the author's -- nice one -- keep up the good > work. > My only problem is trying to decide on whether to use PL/pgSQL or PLPerl. You might also consider PL/Python. I haven't used it, but I use Python a lot outside of PG and it's an excellent language. http://developer.postgresql.org/docs/postgres/plpython.html Regards, Cliff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [ADMIN] Incredible..
On Fri, 21 Jun 2002 09:57:20 -0500 Luis Andaluz P, wrote: > Hello, > see this interesting file. > Bye. > And see this interesting URL: http://www.brocku.ca/its/helpdesk/virusalerts/viruses.phtml?vid=75 -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 x308 (800) 735-0555 x308 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Home-brewed table syncronization
On Wed, 2003-07-09 at 14:14, Michael A Nachbaur wrote: > So, I'm looking at syncronizing 4 tables from one master database to several > child databases. I'm thinking of doing the following with DBD::Multiplex: > > DELETE FROM TableA; > INSERT INTO TableA (..) VALUES (...); > > > on all the child databases, but I'm not sure what kind of impact this would > have on my servers. My impression is that this would hammer the indexes, and > might blow any memory optimization out the window. Only a few records in my > dataset will change from time-to-time, but just the process of determining > what is different may take more effort than simply rebuilding. Keep a timestamp associated with each record. Only update the records with timestamps later than your last sync. -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 (800) 735-0555 ---(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
[SQL] INSERT INTO...RETURNING vs SELECT
I was doing some experimenting and was wondering why the following does not work: CREATE TABLE accounts ( id SERIAL PRIMARY KEY NOT NULL, accounts_id INTEGER REFERENCES accounts, name TEXT ); INSERT INTO accounts (accounts_id, name) VALUES ( (INSERT INTO accounts (accounts_id, name) VALUES (NULL, 'test 1') RETURNING id), 'test 2'); ERROR: syntax error at or near "INTO" Of course, the following works fine: INSERT INTO accounts (accounts_id, name) VALUES (NULL, 'test 1'); INSERT INTO accounts (accounts_id, name) VALUES ( (SELECT id FROM accounts WHERE name='test 1'), 'test 2'); As far as I can see, INSERT INTO...RETURNING is semantically equivalent to SELECT...FROM with a side-effect, so it seems this construct should work. Can someone shed some light? Regards, Cliff -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] INSERT INTO...RETURNING vs SELECT
On Mon, 2010-04-05 at 09:10 +0200, Andreas Kretschmer wrote: > Cliff Wells wrote: > > > As far as I can see, INSERT INTO...RETURNING is semantically equivalent > > to SELECT...FROM with a side-effect, so it seems this construct should > > work. Can someone shed some light? > > Well, at the moment you can't reuse the RETURNING-values, you have to > wait for 9.1, writeable CTE. Thanks, I just wanted to make sure I wasn't misunderstanding something. Regards, Cliff -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] INSERT INTO...RETURNING vs SELECT
I was doing some experimenting and was wondering why the following does not work: CREATE TABLE accounts ( id SERIAL PRIMARY KEY NOT NULL, accounts_id INTEGER REFERENCES accounts, name TEXT ); INSERT INTO accounts (accounts_id, name) VALUES ( (INSERT INTO accounts (accounts_id, name) VALUES (NULL, 'test 1') RETURNING id), 'test 2'); ERROR: syntax error at or near "INTO" Of course, the following works fine: INSERT INTO accounts (accounts_id, name) VALUES (NULL, 'test 1'); INSERT INTO accounts (accounts_id, name) VALUES ( (SELECT id FROM accounts WHERE name='test 1'), 'test 2'); As far as I can see, INSERT INTO...RETURNING is semantically equivalent to SELECT...FROM with a side-effect, so it seems this construct should work. Can someone shed some light? Regards, Cliff -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
