[SQL] De-duplicating rows
The Subject: is somewhat imprecise, but here's what I'm trying to do. For some reason, my brain is locking up over it. I'm moving a 7.2 (yes) database to 8.4. In the table in question, the structure is along the lines of: serial_number SERIAL, PRIMARY KEY email TEXT create_date TIMESTAMP attr1 type attr2 type attr3 type ... (The point of the "attr" fields is that there are many more columns for each row.) The new structure removes the "serial_number" field, and uses "email" as the primary key, but is otherwise unchanged: email TEXT, PRIMARY KEY create_date TIMESTAMP attr1 type attr2 type attr3 type ... Now, since this database has been production since 7.2 days, cruft has crept in: in particular, there are duplicate email addresses, some with mismatched attributes. The policy decision by the client is that the correct row is the one with the earliest timestamp. (The timestamps are widely distributed; it's not the case that there is a single timestamp above which all the duplicates live.) Thus, ideally, I want to select exactly one row per "email", picking the row with the earliest timestamp in the case that there is more than one row with that email. Any suggestions on how to write such a SELECT? Of course, I could do this with an application against the db, but a single SELECT would be great if possible. TIA! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Aug 7, 2008, at 2:39 PM, [EMAIL PROTECTED] wrote: In this case, the first database I tried was Oracle, and it complained of too much transactional data; I forget the exact wording now. You might try it on PostgreSQL. While it might have to spill the result of the subquery to disk, it shouldn't actually fail unless disk is very constrained. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Aug 7, 2008, at 4:37 PM, [EMAIL PROTECTED] wrote: And I suggest you go back and read where I said I had to do this on several databases and am trying to avoid custom SQL for each one. I would much rather this were postgresql only, but it's not. Then it does appear you have an Oracle debugging problem, more than a Postgres SQL formulation problem. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] vacuum in single-user mode
On Aug 8, 2008, at 12:02 PM, Mark Fenbers wrote: I haven't the foggiest idea what this means but googled the error and found a site that seems to suggest that I need to run the vacuum in "single-user" mode before running VACUUM FULL, but I cannot find out how to do that, either. It's in the documentation for the postgres server application: http://www.postgresql.org/docs/8.3/interactive/app-postgres.html -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
Hello, I've got a problem with UNION and SELECT IN I have a first table: CREATE TABLE products ( idProduct INT4, nameVARCHAR(32) ); and two others tables: CREATE TABLE orders ( id INT4, ts TIMESTAMP ); CREATE TABLE preorders ( id INT4, ts TIMESTAMP ); (I reduced the tables to be as simple as possible) I want to retrieve id of products from the tables orders and preorders; and order it by the name of the product. So I tried: SELECT idProduct FROM products WHERE idProduct IN (SELECT id FROM orders WHERE ts>'2000-10-01 17:04:00' UNION SELECT id FROM preorders WHERE ts>'2000-10-01 17:04:00') ORDER by name; and I've got a parse error near UNION or SELECT depending of the presence of () between the SELECTs. I definitively need some help to solve this problem :-| I asked to a friend to test it with Oracle and that seems to work so I really don't know what to do... Christophe.
Re: [SQL] SELECT FROM t1 WHERE id IN (SELECT id FROM t2 UNION SELECT id FROM t3)
Tom Lane wrote : > Current releases don't handle UNION in sub-selects. 7.1 will. Thanks very much for this "fast as speed light" answer ! Is there an idea about the 7.1 release date ? Just to know if this is will be in weeks, months or years... Christophe.
[SQL] pg_dumpall and password access
I'm trying to run pg_dumpall to backup all my users' bases but since I have configure pg_hba.conf to "passwd" pg_dumpall always fails: su-2.03$ pg_dumpall >backup.dat # I'm the postgres unix user. Password: psql: Password authentication failed for user 'postgres' Password: psql: Password authentication failed for user 'postgres' Password: psql: Password authentication failed for user 'postgres' # I type my password on the next line Password: Password: psql: Password authentication failed for user 'postgres' Connection to database 'sympa' failed. fe_sendauth: no password supplied pg_dump failed on sympa, exiting I need to have a password identification since I don't want the "common" users to be able to connect to any database. Beside I cannot leave the "local" to the "trust" auth scheme because every users are able to run programs on the machine hosting the database. Is there any configuration tips or workaround known for my problem ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Integrity and Inheritance
I wanted to build the following schema : - one "generic" document table with a column doc_id ; - a couple of "specific" document tables inheriting from doc ; - a table refering a document by it's id with and integrity constraint on it. In SQL : CREATE TABLE doc ( doc_id serial PRIMARY KEY, ); CREATE TABLE lexique ( ) INHERITS (doc); CREATE TABLE word_doc ( id serial PRIMARY KEY, doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE, ); What I tried to do next is to insert a new "lexique" entry, and then a bunch of "word_doc" rows refering this entry. The last part fails with a constraint violation this is quite normal since the trigger in backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". What should I do ? Should I consider another way to do what I want, rewrite a trigger to replace the system one ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Integrity reference and inheritance
I wanted to build the following schema : - one "generic" document table with a column doc_id ; - a couple of "specific" document tables inheriting from doc ; - a table refering a document by it's id with and integrity constraint on it. In SQL : CREATE TABLE doc ( doc_id serial PRIMARY KEY, ); CREATE TABLE lexique ( ) INHERITS (doc); CREATE TABLE word_doc ( id serial PRIMARY KEY, doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE, ); What I tried to do next is to insert a new "lexique" entry, and then a bunch of "word_doc" rows refering this entry. The last part fails with a constraint violation this is quite normal since the trigger in backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". What should I do ? Should I consider another way to do what I want, rewrite a trigger to replace the system one ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(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] Integrity reference and inheritance
Sorry for the double post, I messed up in my addresses. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pg_ctl start hangs
When restarting postgres this morning I notice the pg_ctl start hangs forever while the database seems to be up and working. Eventually I found out that the start process involves a "psql -l" which waits for me to enter a password since I have "local all password" in my pg_hba.conf. I change back to trust but since I have users with shell access on the server I'd would like to switch back to "password". I used the following command to start the database: /usr/local/bin/pg_ctl start -s -w -l /usr/local/pgsql/errlog -o "-i" My configuration: FreeBSD 4.3, Postgresql 7.1.2 -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(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] LIMIT 1; The Integer part only
On Sep 3, 2009, at 5:49 PM, bilal ghayyad wrote: 1) When writing the function (I mean sql function) in the postgresql, I noticed the use for LIMIT 1, but did not understand what does it mean and why we use it? It restricts the number of rows returned by the SELECT. You can find the documentation here: http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-LIMIT 2) Also in the function (the sql function) in the postgresql, if I need to take the integer part of the number, which math function can do this for me? floor or trunc, depending on the behavior you want for negative numbers: http://www.postgresql.org/docs/8.4/interactive/functions-math.html -- -- Christophe Pettus [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Union Question
On Dec 3, 2010, at 9:04 AM, Shaun McCloud wrote: > I’m trying that, but I am getting an error that says “ERROR: function > dblink_connect(unknown, unknown) does not exist” dblink is a contrib module, and needs to be installed before use: http://www.postgresql.org/docs/9.0/interactive/dblink.html -- -- Christophe Pettus [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Union Question
On Dec 3, 2010, at 9:08 AM, Shaun McCloud wrote: > That would be nice to see in the documentation for dblink It's true of all contrib modules; that's mentioned at the start of the contrib section: http://www.postgresql.org/docs/9.0/interactive/contrib.html -- -- Christophe Pettus [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Inheritance
I have a table (A) with a few "sibbling" tables (B and C for instance). When I make the following query : select id from A* where [condition] pgsql returns ids taken from A, B or C (which is what I want). Is there a way to know to what table the id actually belong ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF
Re: [SQL] if else query help
hi Brian, BCD> I need to write a query that will pull information from table2 if BCD> information in table1 is older then xdate. BCD> My laymen example: BCD> SELECT table2.date, count(table2.name) as count BCD> WHERE table1.startdate > 2 weeks BCD> AND table2.submitdate > 2 weeks BCD> ; BCD> So i Guess my real questions is how do I determine the age of an entry to BCD> another table? Try this : Select table2.date,count(table2.name) as count from table1 as t1, table2 as t2 where t1.itemid=t2.itemid -- to link the tables and t1.startdate>now()-14 and t2.submitdate>now()-14; Someone corrects me if I'm wrong, I come from the Oracle world... Dates (or I should say TimeStamps) are stored as floating point values : the integer part is the number of days since a certain date (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the portion of the day (although I don't know --yet-- how to convert date2-date1 to an integer, trunc does not work). HTH -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Oracle, Perl
[SQL] Help with GROUP BY
Hi, I have a (quite) big table with ~3M rows. Every row has a NBPOINTS (int4) and a REASON (int4) select sum(nbpoints) from points; it takes about 10s select sum(nbpoints) from points group by reason it takes about 210s Same table structure, same data on Oracle gives respectively 8 and 22 seconds. Is there a way to optimize this ? (REASON is indexed though I can see no interest in this query.) TIA! -- Jean-Christophe Boggio [EMAIL PROTECTED] Independant Consultant and Developer Delphi, Linux, Oracle, Perl
[SQL] Strange DISTINCT !
Hello, can someone explain to me why this query is so slow : select distinct t.idmembre,p.datecrea from tmp_stat t,prefs p where p.idmembre=t.idmembre limit 5; And this one is so fast : select t.idmembre,p.datecrea from (select distinct idmembre from tmp_stat) as t, prefs p where p.idmembre=t.idmembre limit 5; (I currently have idmembre as an index on tmp_stat and prefs) How does DISTINCT work ? Is this a bug or a misconfigured index ? -- Jean-Christophe Boggio [EMAIL PROTECTED] -o) Independant Consultant and Developer /\\ Delphi, Linux, Perl, PostgreSQL _\_V ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
