Re: [SQL] ORDER records based on parameters in IN clause
On Mon, Jun 27, 2005 at 09:15:15AM -0700, Riya Verghese wrote: > > I have a stmt where the outer-query is limited by the results of the > inner query. I would like the outer query to return records in the same > order as the values provided in the IN clause (returned form the inner > query). If you want a particular order then use ORDER BY. The SQL standard says that without an ORDER BY clause, row order is implementation- dependent; the PostgreSQL documentation also says the same thing: http://www.postgresql.org/docs/8.0/static/queries-order.html "If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen." -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Unique primary index?
I would say that you should learn a bit about relational databases before you start working with them ;-) All unique fields (or combinations of fields that -combined- are unique) can serve as a primary key. In relational databases we call them 'candidate key'. if you have more than one candidate key in a table, there's nothing wrong. You just simply choose. Best choices are: - select the set of the least amount of columns. (preferably only one) - if you still have more than one candidate key, choose a column with an integer (as integers are looked up the fastest, and you want your indexes to be fast) - if you still have more than one candidate key, pick one That one you define as your primary key. By defenition a primary key is unique. But not all unique fields are primary key. The primary index is the index that should be chosen most, because it is your fastest or best index. From my experience it is always the index of your primary key, since the reasons for selecting the primary index are normally about speed. How you can create your indexes best, and how many you create and how you create them are beyond the scope of this mail. I suggest you read the documentation about the indexes. Also get a book about basics of relational databases, it is really useful! Also getting yourself informed about normalisation (usually up to the 3rd form suffices) can help you on your way. - Michiel [EMAIL PROTECTED] wrote: What are the major differences between Unique, primary index & just plain index? When creating tables I nominate one of these, but not sure what the difference is? ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ENUM like data type
On Tuesday 21 Jun 2005 8:50 pm, MRB wrote: > I'm working with a lot of data from MySQL where the MySQL ENUM type > is used. just a thought - it took me five years after migrating from mysql to pg to start thinking like an sql programmer. I used to keep trying to write stuff for pg 'like' i used to for mysql. When i did that - i didnt get any of the benefits of pg - might as well have continued to write in mysql. Especially so when i went into convolutions trying to translate the enum type and the 'set' syntax on insert. In these cases i suggest dumping the mysql way and rewriting in the pg way. -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ERROR: "TZ"/"tz" not supported
When I execute query, I've got error message.
test=> SELECT to_timestamp('00:00:05.601 SAMST Tue Jun 28 2005',
'HH24:MI:SS.MS TZ Dy Mon DD ');
ERROR: "TZ"/"tz" not supported
How can I convert '00:00:05.601 SAMST Tue Jun 28 2005' (varchar type)
to timestamp with time zone?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] ENUM like data type
On 6/21/05, MRB <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have something in mind I'm not certain is do-able.
>
> I'm working with a lot of data from MySQL where the MySQL ENUM type is used.
>
MySQL's ENUM is basically a wrapper for CHECK. You can use a CHECK
constraint like this:
CREATE TABLE test(
testfield TEXT CHECK (testfield IN( 'Bits', 'of', 'data'))
);
> This is not a big problem per se but creating the proper lookup tables
> is becoming a bit tedious so I was hoping to make something better of it.
>
> Here is where I get uncertain as to if this is possible. My idea is to
> create a pseudo type that triggers the creation of it's lookup tables
> the same way the SERIAL type triggers creation of a sequence and returns
> an int with the right default value.
Although you can't create a generic type to handle this, you can
create a DOMAIN to wrap up your constraint for each "enum" type field
that you want:
CREATE DOMAIN fruit AS TEXT CHECK (VALUE IN ('apple','orange','banana'));
CREATE TABLE eat (
food fruit
);
http://www.postgresql.org/docs/8.0/interactive/sql-createdomain.html
Hope that helps.
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] cross-table reference
On Wed, Jun 22, 2005 at 14:56:08 +0530, Mukesh Ghatiya <[EMAIL PROTECTED]> wrote: > Hi, > > I need to perform a query similar to > > SELECT table1.a.b.x table1.a.c.x from table1; > > > In this case "a" is an attribute in 'table1' and is a foreign key to > other table2 which has 'b', and 'c' as attributes, which again are > foreign keys to table3. > > > Is there any simple way of doing this other than using the complicated > nested joins which would include aliases also. > > SELECT table31.x table32.x > FROM table1, table3 AS table31, table3 AS table32 > WHERE table1.a = table2.id > AND table2.b = table31.id > AND table2.c = table32.id This isn't really that complicated. (Though note you left table2 out of the from item list.) You might be able to reasonable performance and simplify the select statement by defining two functions to select from table2 and table3. If this were written in language SQL the query might even end up with essentially the same plan. ---(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] ENUM like data type
On Tue, Jun 21, 2005 at 17:20:19 +0200, MRB <[EMAIL PROTECTED]> wrote: > > Here is where I get uncertain as to if this is possible. My idea is to > create a pseudo type that triggers the creation of it's lookup tables > the same way the SERIAL type triggers creation of a sequence and returns > an int with the right default value. You might want to look at using a domain to do this. ---(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] Unique primary index?
On Thu, Jun 23, 2005 at 05:43:52 -0700, [EMAIL PROTECTED] wrote: > > What are the major differences between Unique, primary index & just > plain index? > > When creating tables I nominate one of these, but not sure what the > difference is? A unique key has an implied constraint that no two nonnull values can be the same. This is implemented using a unique index which is automatically created. A primary key is a unique key that has an implied not null constraint and is the default for foreign key references into the table. You can also create stand alone indexes for performance reasons or to enforce a unique constraint on a subset of a table. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Unique primary index?
index is... an index ! UNIQUE is an index which won't allow duplicate values (except for NULLS) PRIMARY KEY is exactly like UNIQUE NOT NULL, with the bonus that the database knows this column is the primary key so you can use stuff like NATURAL JOIN without telling which column you want to use. What are the major differences between Unique, primary index & just plain index? When creating tables I nominate one of these, but not sure what the difference is? ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ENUM like data type
Here is where I get uncertain as to if this is possible. My idea is to create a pseudo type that triggers the creation of it's lookup tables the same way the SERIAL type triggers creation of a sequence and returns an int with the right default value. Personnally I use one table which has columns (domain, name) and which stores all enum values for all different enums. I have then CHECK( is_in_domain( column, 'domain_name' )) which is a simple function which checks existence of the value in this domain (SELECT 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance. You can also use integers. ---(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] ENUM like data type
El Mar 28 Jun 2005 13:58, PFC escribió: > > >> Here is where I get uncertain as to if this is possible. My idea is to > >> create a pseudo type that triggers the creation of it's lookup tables > >> the same way the SERIAL type triggers creation of a sequence and returns > >> an int with the right default value. > > Personnally I use one table which has columns (domain, name) and which > stores all enum values for all different enums. > I have then CHECK( is_in_domain( column, 'domain_name' )) which is a > simple function which checks existence of the value in this domain (SELECT > 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance. > You can also use integers. I personally think that the ENUM data type is for databases that are not well designed. So, if you see the need for ENUM, that means you need to re-think your data design. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués | Programador, DBA Centro de Telemática| Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Unique primary index?
On Tue, 2005-06-28 at 12:01, PFC wrote: > > > > What are the major differences between Unique, primary index & just > > plain index? > > > > When creating tables I nominate one of these, but not sure what the > > difference is? > index is... an index ! > > UNIQUE is an index which won't allow duplicate values (except for NULLS) > > PRIMARY KEY is exactly like UNIQUE NOT NULL, with the bonus that the > database knows this column is the primary key so you can use stuff like > NATURAL JOIN without telling which column you want to use. No. natural join joins on columns with the same name. However, primary keys ARE automatically referred to by fk'd columns. Personally, I think that a natural join should use the primary/fk relationship as well, but I guess that's not what the spec says. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] ENUM like data type
On Tue, 2005-06-28 at 13:22, Martín Marqués wrote: > El Mar 28 Jun 2005 13:58, PFC escribió: > > > > >> Here is where I get uncertain as to if this is possible. My idea is to > > >> create a pseudo type that triggers the creation of it's lookup tables > > >> the same way the SERIAL type triggers creation of a sequence and returns > > >> an int with the right default value. > > > > Personnally I use one table which has columns (domain, name) and which > > stores all enum values for all different enums. > > I have then CHECK( is_in_domain( column, 'domain_name' )) which is a > > simple function which checks existence of the value in this domain (SELECT > > 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance. > > You can also use integers. > > I personally think that the ENUM data type is for databases that are not well > designed. So, if you see the need for ENUM, that means you need to re-think > your data design. I would generally agree, however, a pseudo type like serial that created the properly fk'd table with all the options in it would be quite nice to have. ---(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] ENUM like data type
Martín Marqués wrote: I personally think that the ENUM data type is for databases that are not well designed. So, if you see the need for ENUM, that means you need to re-think your data design. You mean like all those instances in the PostgreSQL system catalogs where character(1) has been used as a pseudo-enum of sorts? -Nick Johnson ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] people who buy A, also buy C, D, E
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 >>> The goal of my query is: given a book, what did other people who >>> bought this book also buy? I plan the list the 5 most popular such >>> books. > > SELECT b.product_id, sum(quantity) as rank FROM ordered_products a, > ordered_products b WHERE a.product_id=(the product id) AND > b.order_id=a.order_id AND b.product_id != a.product_id GROUP BY > b.product_id ORDER BY rank DESC LIMIT 6; I don't think this is exactly what the original poster had in mind: we want a ranking of a dynamically generated subset of all possible products (e.g. books). So if someone buys "Harry Potter and the Proprietary Database", then only the books bought by people who also bought /that/ book are considered, ranked, and ordered. There's not a lot of caching that can be effectively done, due to the high number of combinations and large potential for change. > table ordered_products: order_id, product_id, quantity I'm not sure where you are getting "quantity" from: as near as I can tell, this will always be a quantity of 1: one person ordering one item. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506281946 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCweKavJuQZxSWSsgRAkmHAJ9fQ+Degs6jSrGRozEoI35F8nlyBACfYm2u QgawxHOij5FHVd0FopW25IU= =r5eo -END PGP SIGNATURE- ---(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] ORDER records based on parameters in IN clause
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > when I say > select * from table where id IN (2003,1342,799, 1450) > I would like the records to be ordered as 2003, 1342, 799, 1450. Just say: select * from table where id IN (2003,1342,799, 1450) ORDER BY id; If that doesn't work, you will have to be more specific and send us the exact query. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506282010 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCwedPvJuQZxSWSsgRAsC0AKD2UrMtQJ6RRxbeZ8J2n68ewRt+EgCeN2UP Qttr1dX9soeBp5HxIp+vz/c= =cGiG -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
