[SQL] Duplicate information in parent and child tables
I have a parent table and a child table. In order to detect duplicate information I need to consider both tables. This means that a "duplicate record" consists of one parent record and one or more child records. Is there any standard approach to solve this issue? Respectfully, Jorge Maldonado
[SQL] How to html-decode a html-encoded field
I have a table with a varchar field, such a field is HTML ENCODED. So, for example, the string "PLAIN WHITE T'S" is saved as "PLAIN WHITE T''S" (double quotes are not part of the string, I use them for clarity only). I need to perform a SELECT statement on this table and get the values HTML DECODED and I wonder if there is a function that I can include in such a statement for this purpose, for example "SELECT htmldecode(fld1) FROM table1". I will appreciate anu comments about my issue. Respectfully, Jorge Maldonado
[SQL] Query with LIMIT clause
I have the following records that I get from a query, the fields are date type in day/month/year format: - Initial Final DateDate - 27/08/2012 04/09/2012 29/08/2012 09/09/2012 28/08/2012 09/09/2012 30/08/2012 09/09/2012 30/08/2012 09/09/2012 27/08/2012 09/09/2012 31/08/2012 09/09/2012 28/08/2012 10/09/2012 05/09/2012 16/09/2012 As you can see, this result is ordered by Final Date. What I need is to get the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I have tried an ORDEY BY DESC but the result is the same. I will very much appreciate your comments. Respectfully, Jorge Maldonado
Re: [SQL] Query with LIMIT clause
Firstly, who should I reply to, you or the list? Your solution is working pretty fine, I appreciate your advice. Now, I am sure that an index is a good idea in order to make the query fast. What would be a good criteria to define an index? Will an index for final date and another for initial date is the choice, or one index composed of both initial and final date? Regards, Jorge Maldonado On Sun, Sep 9, 2012 at 12:45 PM, David Johnston wrote: > From: pgsql-sql-ow...@postgresql.org [mailto: > pgsql-sql-ow...@postgresql.org] > On Behalf Of JORGE MALDONADO > Sent: Sunday, September 09, 2012 1:26 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Query with LIMIT clause > > I have the following records that I get from a query, the fields are date > type in day/month/year format: > > - > Initial Final > DateDate > - > 27/08/2012 04/09/2012 > 29/08/2012 09/09/2012 > 28/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 27/08/2012 09/09/2012 > 31/08/2012 09/09/2012 > 28/08/2012 10/09/2012 > 05/09/2012 16/09/2012 > > As you can see, this result is ordered by Final Date. What I need is to get > the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I > have tried an ORDEY BY DESC but the result is the same. I will very much > appreciate your comments. > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>. > > First, you should really order by both columns, not just "final date". The > ties on 9/9/12 are unordered. > > Second, you will indeed need to reverse the sort order and then take the > first 5 records; figuring out and limiting on the last 5 isn't worth the > effort. > > SELECT initial_date, final_date > FROM date_source > ORDER BY final_date DESC, initial_date DESC > LIMIT 5 > > You can put the above into a sub-query and re-order if the final result is > needed in ascending order. > > If this doesn't seem to work you will want to provide the exact > query/queries you are trying so that someone may spot what you are doing > wrong. > > Dave > > >
[SQL] Joining several rows into only one
I have a SELECT statement that gives me a result like this: SONG ARTIST --- Song1 Artist1 Song1 Artist2 Song1 Artist3 Song2 Artist2 Song3 Artist4 Song4 Artist1 Song5 Artist4 As you can see, the same song may belong to more than one artist, and this is fine. What I would like to do is NOT to get several rows, one for each different artist, but to join all of the artists in one column and display only one row. Is this possible? Respectfully, Jorge Maldonado
[SQL] Help with a select statement design
I have a record with the following fields: id1, id2, id3, id4, price1, price2, price3, price4 I would like to write a SELECT statement in order to get 4 records: id, price (record that comes from id1 and price 1) id, price (record that comes from id2 and price 2) id, price (record that comes from id3 and price 3) id, price (record that comes from id4 and price 4) I will very much appreciate any suggestion. Respectfully, Jorge Maldonado
[SQL] Query execution based on a condition
I have a query similar to the one shown below but, depending on the value of a field, only the first SELECT statement shoud execute and the other 3 should be ignored. Is there a way to achieve this situation? SELECT fields FROM tblTable WHERE condition UNION SELECT fields FROM tblTable WHERE condition UNION SELECT fields FROM tblTable WHERE condition UNION SELECT fields FROM tblTable WHERE condition Respectfully, Jorge Maldonado
[SQL] Setting a default value for a select statement without results
I have an UPDATE query with the following general structure: UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1) Is it possible to assign a default value in case no results are returned by the SELECT statement? Respectfully, Jorge Maldonado
Re: [SQL] Setting a default value for a select statement without results
This solution gave me the result I need, but it seems the process takes longer when COALESCE is added. What do you mean with the comment of "and you are happy with its performance" ? Does it have to do with performance? Regards, Jorge Maldonado On Tue, Feb 5, 2013 at 10:07 PM, Jasen Betts wrote: > On 2013-02-06, JORGE MALDONADO wrote: > > --f46d0401fb2fcb805e04d50354b1 > > Content-Type: text/plain; charset=ISO-8859-1 > > > > I have an UPDATE query with the following general structure: > > > > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY > > order_field LIMIT 1) > > assuming you mean this, and you are happy with its performance. > > UPDATE table1 SET field1 = (SELECT field FROM table2 WHERE conditions > ORDER BY > order_field LIMIT 1) > > > > Is it possible to assign a default value in case no results are returned > by > > the SELECT statement? > > use coalesce. > > UPDATE table1 SET field1 = coalesce( (SELECT field FROM table2 WHERE > conditions ORDER BY > order_field LIMIT 1) , default_value ) > > > > > -- > ⚂⚃ 100% natural > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Conditional expression in an UPDATE statement
Can I use a conditional expression in an UPDATE query like this: UPDATE table_1 SET field_1 = CASE WHEN (condition) THEN (COALESCE(query_1, -1)) ELSE (COALESCE(query_1, -2)) END With respect, Jorge Maldonado
[SQL] UPDATE query with variable number of OR conditions in WHERE
I am building an UPDATE query at run-time and one of the fields I want to include in the WHERE condition may repeat several times, I do not know how many. UPDATE table1 SET field1 = "some value" WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n) I build such a query using a programming language and, after that, I execute it. Is this a good approach to build such a query? Respectfully, Jorge Maldonado
[SQL] Advice for index design
I have a table of artists with fields like the ones below: * Name * Birthday * Sex (male/female) Our application offers a catalog of artists where a user can select a range of birthdays and/or sex. For example, a user can get an artists catalog for those male artists who were born between May 1, 1970 and May 1, 1990 ordered by birthday and, within each birthday date, ordered by name. I can think of defining one index for birthday, one index for name, and one index for sex. Also, I can think of defining a compound index for birthday + name. Also there could be a compound index for sex + name. Another option could be a compound index for birthday + sex + name. There are many possible combinations. What is a good index design approach? Maybe, setting simple separate indexes (one for each field) would work fine if I need to retrieve data in different combinatios, but I am not sure. Maybe compound indexes is better. I will very much appreciate your advice. Respectfully, Jorge Maldonado
[SQL] Table indexes in a SELECT with JOIN´s
Let´s suppose that I have a SELECT statement that joins more than one table and such a statement is order by fields that belong not only to the table in the FROM but also by fields in the tables that are part of the JOIN´s. How does indexes should be considered in a case like this? For example: SELECT artist_name, author_name, producer_name, song_name FROM tbl_songs INNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistid INNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authorid INNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_producerid ORDER BY song_name Respectfully, Jorge Maldonado
[SQL] Order of execution
I have an UPDATE query which performs several opertions in one table. UPDATE table01 SET field1 = (query1 may contain any field), field2 = (query1 may contain any field), field3 = (query1 may contain any field) WHERE (condition) query1, query2 and query3 perform a logic to determine which records will be updated. Does field1 updates first, then field2 and, lastly, field3? or What is the order in which updates are executed? If I need fields to be updated in a certain order, should I use 3 UPDATE commands instead? Respectfully, Jorge Maldonado
[SQL] Select statement with except clause
I have one SELECT statement as follows: SELECT lpt_titulo as tmt_titulo, tmd_nombre as tmt_nombre, tmd_album as tmt_album, SUM(lpt_puntos) AS tmt_puntos FROM listas_pre_titulos INNER JOIN cat_tit_media ON lpt_titulo = tmd_clave " WHERE condition The above statement must have an EXCEPT clause which includes another SELECT statement almost identical, the difference is in the WHERE condition and also in one of the fields; "SUM(lpt_puntos) AS tmt_puntos" should be "SUM(lpt_puntos) * -1 AS tmt_puntos". I only need to convert such a field to a negative value. How does the EXCEPT work? Do fields should be identical? I need the difference to be on the first 3 fields. Respectfully, Jorge Maldonado
Re: [SQL] Select statement with except clause
Firstly, I want to thank you for responding. Secondly, I wonder if I should only reply to the mailing list (I clicked Reply All); if this is the case, I apologize for any inconvenience. Please let me know so I reply correctly next time. I will describe my issue with more detail. I need to perform 2 very similar queries as follows: *** QUERY 1 *** SELECT fldA, fldB, fldC, SUM(fldD) AS fldD FROM tableA WHERE condition1 GROUP BY fldA, fldB, fldC *** QUERY 2 *** SELECT fldA, fldB, fldC, SUM(fldD)*(-1) AS fldD FROM tableA WHERE condition2 GROUP BY fldA, fldB, fldC As you can see, both reference the same table and the same fields. The differences between the queries are: a) The last SELECTED field is multiplied by (-1) in the second query. b) The WHERE conditions. What I finally need is to exclude records generated by QUERY1 from QUERY2 when fldA, fldB and fldC are equal in both results. With respect, Jorge Maldonado On Thu, May 23, 2013 at 1:36 PM, David Johnston wrote: > JORGE MALDONADO wrote > > How does the EXCEPT work? Do fields should be identical? > > I need the difference to be on the first 3 fields. > > Except operates over the entire tuple so yes all fields are evaluated and, > if they all match, the row from the "left/upper" query is excluded. > > If you need something different you can use some variation of: > IN > EXISTS > NOT IN > NOT EXISTS > > with a sub-query (correlated or uncorrelated as your need dictates). > > For example: > > SELECT col1, col2, col3, sum(col4) > FROM tbl > WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not > correlated > GROUP BY col1, col2, col3 > > SELECT col1, col2, col3, sum(col4) > FROM tbl > WHERE NOT EXISTS ( > SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table > if it matches the outer reference > (tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3) > ) -- correlated; reference "tbl" within the query inside the where clause > GROUP BY col1, col2, col3 > > I do not follow your example enough to provide a more explicit > example/solution but this should at least help point you in the right > direction. > > David J. > > > > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Advice on re-writing a SELECT query.
I have a query like this: SELECT lpt_titulo AS tmt_titulo, tmd_nombre AS tmt_nombre, tmd_album AS tmt_album SUM(lpt_puntos) AS tmt_puntos, lpt_fuente AS tmt_fuente FROM listas_pre_titulos, temp_lista_titulos WHERE listas_pre_titulos.lpt_tipo = 3 AND listas_pre_titulos.lpt_titulo <> temp_lista_titulos.tmt_titulo AND listas_pre_titulos.tmd_album <> temp_lista_titulos.tmt_album AND listas_pre_titulos.lpt_fuente <> temp_lista_titulos.tmt_fuente GROUP BY lpt_fuente, lpt_titulo, tmd_album ORDER BY tmt_puntos ASC Is it valid to re-write the FROM and WHERE statements as follows? FROM listas_pre_titulos INNER JOIN temp_lista_titulos ON (listas_pre_titulos.lpt_titulo, listas_pre_titulos.tmd_album, listas_pre_titulos.lpt_fuente) NOT IN (temp_lista_titulos.tmt_titulo, temp_lista_titulos.tmt_album, temp_lista_titulos.tmt_fuente) WHERE listas_pre_titulos.lpt_tipo = 3 With respect, Jorge Maldonado
[SQL] Advice with an insert query
I need to insert records into a table where one value is fixed and 2 values come from a SELECT query, something like the following example: INSERT INTO table1 fld1, fld2, fl3 VALUES value1, (SELECT fldx, fldy FROM table2) Is this valid? Respectfully, Jorge Maldonado
[SQL] Select clause in JOIN statement
Is it valid to specify a SELECT statement as part of a JOIN clause? For example: SELECT table1.f1, table1.f2 FROM table1 INNER JOIN (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 = table_aux.f1 Respectfully, Jorge Maldonado
[SQL] Advice on key design
I have 2 tables, a parent (tbl_persons) and a child (tbl_languages_per_person) as follows (a language table is also involved): -- tbl_persons -- * per_id * per_name * per_address -- tbl_languages_per_person -- * lpp_person_id * lpp_language_id * lpp_id As you can see, there is an obvious key in the child table which is "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a unique key which is a field that contains a consecutive number of type serial. My question is: what should I configure as the primary key, "lpp_person_id + lpp_language_id" or "lpp_id"? Is the role of a primary key different from that of a unique index? With respect, Jorge Maldonado
Re: [SQL] Advice on key design
>> In your case it would be lpp_id as PK, and >> lpp_person_id,lpp_language_id as unique constraint >> >> Thanks, >> Anton Is there a reason to do it the way you suggest? Regards, Jorge Maldonado On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk wrote: > Hi Jorge, > > In your case it would be lpp_id as PK, and > lpp_person_id,lpp_language_id as unique constraint > > Thanks, > Anton > > On Jul 23, 2013, at 23:45, JORGE MALDONADO wrote: > > > I have 2 tables, a parent (tbl_persons) and a child > (tbl_languages_per_person) as follows (a language table is also involved): > > > > -- > > tbl_persons > > -- > > * per_id > > * per_name > > * per_address > > > > -- > > tbl_languages_per_person > > -- > > * lpp_person_id > > * lpp_language_id > > * lpp_id > > > > As you can see, there is an obvious key in the child table which is > "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a > unique key which is a field that contains a consecutive number of type > serial. > > > > My question is: what should I configure as the primary key, > "lpp_person_id + lpp_language_id" or "lpp_id"? > > Is the role of a primary key different from that of a unique index? > > > > With respect, > > Jorge Maldonado > > > > > > > > > > > > >
[SQL] Unique index and unique constraint
I guess I am understanding that it is possible to set a unique index or a unique constraint in a table, but I cannot fully understand the difference, even though I have Google some articles about it. I will very much appreciate any guidance. Respectfully, Jorge Maldonado
[SQL] Criteria to define indexes
I have a table with fields as follows: * sag_id * sag_header * sag_comments * sag_date * sag_section_id (**) * sag_artist_id (**) * sag_author_id (**) * sag_producer_id (**) As you can see, fields mark with (**) are identifiers that reference another table (catalog of sections, catalog of artists, etc). Firstly, I need an index on "sag_date" because our application has a search option under this criteria. However, there are also search criterias on "sag_section", "sag_artist", "sag_author" and "sag_producer" because, for example, a user may need to get the records of certain artist only. Furthermore, our application offers a user to select several cominations of criterias: * Artist + Author * Artist + Producer * Artist + Author + Producer * Section + Artist. And so on. What I see is that it is not a good decision to set a key for every possibility because it will have an impact on performance due to index maintenance. What would be a good way to define indexes in a case like this? With respect, Jorge Maldonado
[SQL] Unique index VS unique constraint
I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. Respectfully, Jorge Maldonado
[SQL] Advice on defining indexes
I have a table with fields that I guess would be a good idea to set as indexes because users may query it to get results ordered by different criteria. For example: -- Artists Table -- 1. art_id 2. art_name 3. art_bday 4. art_sex 5. art_country (foreign key, there is a table of countries) 6. art_type (foreign key, there is a table of types of artists) 7. art_email 8. art_comment 9. art_ bio "art_id" is the primary key. Users query the table to get results ordered by fields (2) to (6). Is it wise to define such fields as indexes? I ask this question because our database has additional tables with the same characteristics and maybe there would be many indexes. With respect, Jorge Maldonado
Re: [SQL] Advice on defining indexes
I really appreciate your fast and very complete answer. If a table has a foreign key on 2 fields, should I also create an index composed of such fields? For example: --- Table Sources --- 1. src_id 2. src_date 3. Other fields . . . Here, the "primary key" is "src_id + src_date". One "src_id" can exist for several "src_date". --- Table Lists --- 1. lst_id 2. lst_source (points to src_id) 3. lst_date 4. Other fields . . . Here, the "foreign key" is "lst_source + lst_date". Regards, Jorge Maldonado On Fri, Oct 4, 2013 at 5:09 PM, David Johnston wrote: > JORGE MALDONADO wrote > > I have a table with fields that I guess would be a good idea to set as > > indexes because users may query it to get results ordered by different > > criteria. For example: > > > > -- > > Artists Table > > -- > > 1. art_id > > 2. art_name > > 3. art_bday > > 4. art_sex > > 5. art_country (foreign key, there is a table of countries) > > 6. art_type (foreign key, there is a table of types of artists) > > 7. art_email > > 8. art_comment > > 9. art_ bio > > > > "art_id" is the primary key. > > Users query the table to get results ordered by fields (2) to (6). Is it > > wise to define such fields as indexes? > > > > I ask this question because our database has additional tables with the > > same characteristics and maybe there would be many indexes. > > > > With respect, > > Jorge Maldonado > > Some thoughts: > > Indexes for sorting are less useful than indexes for filtering. I probably > would not create an index if it was only intended for sorting. Note that > in > many situations the number of ordered records will be fairly small so > on-the-fly sorting is not going to be that expensive anyway. > > Indexes decrease insertion/update performance but generally improve > selection performance. The relative volume of each is important. > > Index keys which contain a large number of rows are generally ignored in > favor of a table scan. For this reason gender is seldom indexed. > > You have the option of a partial index if a single key contains a large > number of records. Simply index everything but that key. Smaller indexes > are better and any searches for the ignored key would end up skipping the > index in many cases anyway. > > Consider create full-text search indexes on the comment/bio column and you > can probably also add in the other fields into some form of functional > index > so that performing a search over that single field will in effect search > all > of the columns. > > I'd probably index country and type to make the foreign key lookups faster > and then create a functional full-text index on the different text fields. > I would then add an index on art_bday and call it done. You can then write > a view/function that performs a full-text search against the functional > index (or just create an actual column) for most text searches and have > separate criteria filters for country/type/birthday. > > David J. > > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773424.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > 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] Unique index VS unique constraint
So, let´s say that I have the following simple example table: 1. cus_id 2. cus_name 3. Other fields . . . Where "cus_id" is the primary key. And let´s also say that I want "cus_name" to be unique. I have the option to create a unique constraint or a unique index. What would be the best decision and why? Regards, Jorge Maldonado On Fri, Oct 4, 2013 at 5:38 PM, David Johnston wrote: > JORGE MALDONADO wrote > > I have search for information about the difference between "unique index" > > and "unique constraint" in PostgreSQL without getting to a specific > > answer, > > so I kindly ask for an explanation that helps me clarify such concept. > > A constraint says what valid data looks like. > > An index stores data in such a way as to enhance search performance. > > Uniqueness is a constraint. It happens to be implemented via the creation > of a unique index since an index is quickly able to search all existing > values in order to determine if a given value already exists. > > PostgreSQL has chosen to allow a user to create a unique index directly, > instead of only via a constraint, but one should not do so. The uniqueness > property is a constraint and so a "unique index" without a corresponding > constraint is an improper model. If you look at the model without any > indexes (which are non-model objects) you would not be aware of the fact > that duplicates are not allowed yet in the implementation that is indeed > the > case. > > Logically the constraint layer sits on top of an index and performs its > filtering of incoming data so that the index can focus on its roles of > storing and retrieving. Extending this thought the underlying index should > always be non-Unique and a unique filter/constraint would use that index > for > validation before passing the new value along. However, practicality leads > to the current situation where the index takes on the added role of > enforcing uniqueness. This is not the case for any other constraint but > the > UNIQUE constraints case is so integral to PRIMARY KEY usage that the > special > case behavior is understandable and much more performant. > > Conceptually the index is an implementation detail and uniqueness should be > associated only with constraints. > > David J. > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Question about index/constraint definition in a table
I have a table as follows: Table Artist Colaborations * car_id (integer field, primary key) * car_song (integer field, foreign key, foreign table is a catalog of songs) * car_artist (integer field, foreign key, foreign table is a catalog of artists) So, I added 2 indexes to improve JOIN in queries: 1. An index for car_song which accepts duplicates. 2. An index for car_artist which accepts duplicates. Now, the combination of "car_song + car_artist" cannot be duplicated so I think that adding a constraint on these 2 fields is the solution. My question: Is this the correct way to go? Respectfully, Jorge Maldonado
