Re: [SQL] About primary keys -- made some progress
Tim Andersen <[EMAIL PROTECTED]> writes: > I still have a question about how to get the > information about length and precision of a column > from pg_attributes.atttypmod. are there built-in > functions for PostgreSQL to extract this information? Best is to rely on the format_type() function. Also, have you thought about using pg_get_indexdef() in place of all that hacking about in pg_index? http://www.postgresql.org/docs/7.3/static/functions-misc.html In general, your code is less likely to break if you can use the "catalog information functions" rather than poking around in the catalogs directly. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Optional join
On Thursday 14 August 2003 16:40, Slawek Jarosz wrote: > Hi, > > I trying to write a query that will join 2 tables. Here's the concept: > Table 1: table1, primary key pk1 > Table 2: table2, primary key pk2 > > One of the fields (f2) in table2 contains either the primary key of table1 > or a NULL value. So normally a pretty basic query: > > SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = > table1.pk1; > > BUT what I would like to do is show all records of Table 1 even if there is > no match in Table 2. Meaning that the reults could be table1... > table2.pk2 > table1... NULL > > Doable? You need an OUTER JOIN, see e.g. http://www.postgresql.org/docs/7.3/static/tutorial-join.html Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Do it exist?
PostgreSQL Version 7.3.0 I hope I am not Cross Posting..with psql-novice..If so..sorry.. We have a situation where in our application the user provides the Order Number for a delivery document. When the Order Number is entered we need to determine if the Order Number already exists and raise a flag if it does exist. In another language I would do: find first Order where Order.Order_num = "". if avaialble Order then it_exists = true. In PostgreSQL ?: SELECT DISTINCT Order_num from Order_header where Order_num = ''; Is this close? If so, how to raise flage that row exists? How we do somthing like this in SQL? -- Hal Davison Davison Consulting ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Do it exist?
> In another language I would do: > find first Order where Order.Order_num = "". > if avaialble Order then it_exists = true. > > In PostgreSQL ?: > SELECT DISTINCT Order_num from Order_header where Order_num = ''; > > Is this close? If so, how to raise flage that row exists? > > > How we do somthing like this in SQL? Since you don't care about a value, just pull TRUE and use LIMIT to restrict to a single entry (much faster than DISTINCT, but not as portable). SELECT TRUE FROM Order_header where Order_num = '' LIMIT 1; Do a count of the number of rows returned. 1 row means it exists, 0 rows means it does not exist. It seems to me like you intend to insert a row with that number shortly after? You should be aware that this process will introduce a race condition (may not exist during test, but does exist shortly after during insert). If Order_num is Distinct on that table (or another), just try the insert. If it fails, increment the number and try again. If you don't mind gaps in the numbers, then a sequence will fare much better (easier and faster). signature.asc Description: This is a digitally signed message part
