Re: [SQL] query optimization question
> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_30_60,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_60_90,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_greater_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_less_30,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-10-07'}
> AND dt.days_old_start_date < {d '2002-09-07'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_30_60,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-09-07'}
> AND dt.days_old_start_date < {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_60_90,
> COUNT(DISTINCT(CASE WHEN
> dt.days_old_start_date >= {d '2002-08-08'}
> THEN lots.lot_id ELSE NULL END )) AS
lot_count_greater_90,
> COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
> (SELECT * FROM deficiency_table
> WHERE assigned_supplier_id = '101690') AS dt,
> (SELECT * FROM deficiency_status
> WHERE is_outstanding) AS ds,
> (SELECT * FROM projects
> WHERE division_id = 'GGH') AS proj,
> lots
> WHERE
> dt.lot_id = lots.lot_id
> AND lots.division_id = proj.division_id
> AND lots.project_id = proj.project_id
> AND dt.deficiency_status_id = ds.deficiency_status_id
> AND NOT EXISTS
>(SELECT 1 FROM menu_group_projects
> WHERE menu_code = 'WA'
> AND division_id = proj.division_id
> AND project_id = proj.project_id
> AND status = 'I')
> ORDER BY proj.project_id ;
What about simply replacing ORDER BY proj.project_id ; by
GROUP BY project_id, marketing_name ;
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [pgsql-sql] Daily Digest V1 #983
---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Weird NULL behavior
Hi: Has anyone encountered this before? SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0)); returns the following error message: Cannot cast type '"char"' to '"numeric"' But the following sql statements returns NULL: select NULL: select NULL * NULL; select cast ( NULL as NUMERIC(2,0)); Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(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] Generating a cross tab (pivot table)
Regarding to Terry's request on multiple aggregates and Shahbaz's request for generating a cross tab ( pivot table ) in September, I've found an excellent example on a german inet page http://www.itrain.de/ I've translated it and think it's useful for many who subscribed (I hope so, maybe it was told before, but I couldn't find anything about this topic in the techdecs). Objective: There is a relation "sales", holding the sales of different products of different vendors. The task is to generate a report which shows the sales of every vendor and every product. Consider the following table populated with some data: CREATE TABLE sales ( product TEXT, vendor TEXT, sales INTEGER ); INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ; INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ; INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ; INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ; INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ; INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ; INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ; The following query generates the report: SELECT product, SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ", SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown", SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green", SUM(sales) AS "sum of sales" FROM sales GROUP BY product ; product | mr. pink | mr. brown | mr. green | sum of sales -+---+---+---+-- butter |17 | 2 | 0 | 19 honey |19 | 0 | 2 | 21 milk|12 | 8 |34 | 54 (3 rows) The example is based on MS SQL Server 7.0 and it appears to be there is a valuable feature called CUBE which completes the report. SELECT CASE WHEN GROUPING(product) = 1 THEN 'sum of sales' ELSE product END, SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ", SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown", SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green", SUM(sales) AS "sum of sales" FROM sales GROUP BY product WITH CUBE ; product | mr. pink | mr. brown | mr. green | sum of sales --+---+---+---+-- butter |17 | 2 | 0 | 19 honey|19 | 0 | 2 | 21 milk |12 | 8 |34 | 54 sum of sales |48 |10 |36 | 94 (4 rows) I would like to hear from the core team whether they think this feature is worthy to be implemented, or even better, is there a similar one or an easy workaround already. It's obvious this approach is most inflexible. As soon as there is a new vendor, one has to re-write the query and add SUM(CASE vendor WHEN 'mr. new' THEN ... , In an advanced example it is shown how to deal with cross tabs in general using a stored procedure. I am going to translate this and re-write it for postgres, too (ok, I will try). Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Weird NULL behavior
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 7 Nov 2002, Ludwig Lim wrote: >> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0)); >> Cannot cast type '"char"' to '"numeric"' > It seems to me that it's trying to decide on a type > for the expression NULL * NULL. It's a NULL, but a > NULL of what type? Yeah, and it's picking "char" (the single-byte datatype), because (a) the NULLs are initially regarded as type UNKNOWN, and (b) if we don't have any other way to make a decision we try assuming that UNKNOWNs are of string category, and (c) the only datatype in string category that has a "*" operator is "char". I am kind of inclined to remove the arithmetic operators on "char" (+,-,*,/) in 7.4 --- they don't seem to have any real-world uses, and as this example illustrates, they are perfectly positioned to capture cases that probably ought to be errors. But as you say, the proper solution for Ludwig's problem is to cast the NULLs themselves to numeric, not the result of the multiplication. regards, tom lane ---(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] query optimization question
Actually, the ORDER BY *must* be replaced by GROUP BY since it is an
aggregate query.
I have implemented it, and the results are startling, I get the same value
repeated for all projects
i.e.:
AS1 AS1-AJAX/SALEM SIDE 3 0 6 7 30 0 216 240
AU3 AU3-RIVERIDGE/AURORA 3 0 6 7 30 0 216 240
AV1 AVALON 3 0 6 7 30 0 216 240
AW1 AW1-AJAX/WESTNEY SIDE 3 0 6 7 30 0 216 240
AWM AW MORTGAGE 3 0 6 7 30 0 216 240
AX1 AX1-ROSE PETAL VALLEY DEV INC 3 0 6 7 30 0 216 240
And this appears to be the correct data row for a row that is the first
(alphabetically) project that has non zero data in it.
This is the final query, can anyone see anything wrong with it?:
SELECT projects.project_id, projects.marketing_name,
COUNT(lots.lot_id) AS def_count,
COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
THEN lots.lot_id ELSE NULL END
) AS def_count_less_30,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-08'}
AND dt.days_old_start_date < {d '2002-09-08'}
THEN lots.lot_id ELSE NULL END
) AS def_count_30_60,
COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
THEN lots.lot_id ELSE NULL END
) AS def_count_greater_60,
COUNT(DISTINCT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
AND dt.deficiency_status_id =
ds.deficiency_status_id
THEN lots.lot_id ELSE NULL END)
) AS lot_count_less_30,
COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d '2002-10-08'}
AND
dt.days_old_start_date < {d '2002-09-08'}
THEN lots.lot_id ELSE NULL END)
) AS lot_count_30_60,
COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
THEN lots.lot_id ELSE NULL END)
) AS lot_count_greater_60,
COUNT(DISTINCT lots.lot_id) AS lot_count
FROM
(SELECT * FROM deficiency_table) AS dt,
(SELECT * FROM deficiency_status WHERE is_outstanding) AS ds,
(SELECT * FROM projects WHERE division_id = 'GGH') AS proj,
(SELECT * FROM lots) AS lots
WHERE proj.division_id = 'GGH'
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.lot_id = lots.lot_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND lots.project_id = 'EM16'
AND NOT EXISTS (SELECT 1 FROM menu_group_projects WHERE menu_code = 'WA'
AND division_id = proj.division_id AND project_id = proj.project_id AND
status = 'I')
GROUP BY projects.project_id, projects.marketing_name
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Christoph Haller
> Sent: Thursday, November 07, 2002 3:57 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] query optimization question
>
>
> > SELECT
> > project_id,
> > marketing_name,
> > COUNT(lots.lot_id) AS def_count,
> > COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_less_30,
> > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
> > AND dt.days_old_start_date < {d '2002-09-07'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_30_60,
> > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
> > AND dt.days_old_start_date < {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_60_90,
> > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END) AS
> def_count_greater_90,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date < {d '2002-10-07'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_less_30,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date >= {d '2002-10-07'}
> > AND dt.days_old_start_date < {d '2002-09-07'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_30_60,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date >= {d '2002-09-07'}
> > AND dt.days_old_start_date < {d '2002-08-08'}
> > THEN lots.lot_id ELSE NULL END )) AS
> lot_count_60_90,
> > COUNT(DISTINCT(CASE WHEN
> > dt.days_old_start_date >= {d '2002-08-0
Re: [SQL] cast lo to oid
I didn't realize there was a contrib/lo, I only created the lo type as described in the psqlodbc faq. I have now installed the contrib/lo and everything is working fine. I am using PG version 7.2 thanks. From: Tom Lane <[EMAIL PROTECTED]> To: "Nekta Katz" <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: [SQL] cast lo to oid Date: Thu, 07 Nov 2002 10:16:21 -0500 "Nekta Katz" <[EMAIL PROTECTED]> writes: > I have the following table > create table scan_docs ( > docid serial, > shipno numeric(10), > scanlo, > type text > ); > when I try to create the following rule > create rule "delete_scan_docs_lo" as > on delete to "scan_docs" > do select lo_unlink (old.scan); Why aren't you using the trigger that type LO provides for this purpose? Seems rather pointless to use a nonstandard type and then ignore the primary (sole) feature it provides... > create rule "delete_scan_docs_lo" as > on delete to "scan_docs" > do select lo_unlink (old.scan::oid); > "psql:scan_docs_rule.sql:3: ERROR: Cannot cast type 'lo' to 'oid' " Curious, as contrib/lo provides a function that's supposed to work for that: -- same function, named to allow it to be used as a type coercion, eg: --CREATE TABLE a (image lo); --SELECT image::oid FROM a; -- CREATE FUNCTION oid(lo) RETURNS oid AS 'MODULE_PATHNAME', 'lo_oid' LANGUAGE 'C'; What PG version are you using? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] cast lo to oid
Hi, I have the following table create table scan_docs ( docid serial, shipno numeric(10), scanlo, type text ); when I try to create the following rule create rule "delete_scan_docs_lo" as on delete to "scan_docs" do select lo_unlink (old.scan); I get the error message "psql:scan_docs_rule.sql:3: ERROR: Function 'lo_unlink(lo)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts" So I add a type cast create rule "delete_scan_docs_lo" as on delete to "scan_docs" do select lo_unlink (old.scan::oid); but I get the following error message "psql:scan_docs_rule.sql:3: ERROR: Cannot cast type 'lo' to 'oid' " Is there away around this? _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(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] Generating a cross tab (pivot table)
On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote: > Regarding to Terry's request on multiple aggregates and > Shahbaz's request for generating a cross tab ( pivot table ) > in September, I've found an excellent example on a german inet page > http://www.itrain.de/ > I've translated it and think it's useful for many who subscribed > (I hope so, maybe it was told before, but I couldn't find > anything about this topic in the techdecs). Very useful. Also note there are some examples of how to produce crosstab results in the table-functions contrib directory in 7.3 -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] how to get the source table & field name of a view field
Hi, Could you tell me how to get view field's source table and field name? another word, how could I know the view field come from? Regards, Ho ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query optimization question
No offence taken, however it is incorrect, my SQL is pretty good. I
received no other responses... And I later realized the solution to my
question:
(EXPERTS READ ON: If anyone can show me how to use a group by or otherwise
optimize I would be grateful)
This subquery:
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status
AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id =
ds.deficiency_status_id
AND ds.is_outstanding
#PreserveSingleQuotes(variables.base_query)#
) AS def_count,
Actually does return a deficiency count, where there could be more then 1
deficiency per lot. In order to get my lot_count, (number of lots with 1 or
more deficiencies) I just needed to add a DISTINCT clause in my count()
aggregate, ie SELECT count(DISTINCT lots.lot_id)... I forgot one could do
that:
(SELECT count(DISTINCT lots.lot_id) AS
lot_count
FROM deficiency_table AS dt, lots,
deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >=
#CreateODBCDate(DateAdd("d", -
int(ListLast(variables.aging_breakdown_list, ",")), now() ))#
AND dt.deficiency_status_id =
ds.deficiency_status_id
AND ds.is_outstanding
#PreserveSingleQuotes(variables.base_query)#
) AS
lot_count_greater_#ListLast(variables.aging_breakdown_list, ",")#,
Note the #PreserveSingleQuotes(variables.base_query)# is dynamic code that
further selects deficiencies by various criteria, eg just for a particular
supplier.
This query is actually dynamic, if all I had to do was the above 2 clauses
then I most certainly COULD do a group by.
However, for the total deficiencies I am then splitting up the total into
aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
looks like the below. But before I paste it in, I would like to optimize
it, if I could do so with a group by clause I most certainly would, but I
don't see how I can BECAUSE OF THE AGING BREAKDOWN:
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date < {d '2002-10-07'}
AND dt.deficiency_status_id = ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_less_30,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots, deficiency_status
AS ds
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.days_old_start_date >= {d '2002-10-07'}
AND dt.days_old_start_date < {d '2002-09-07'}
AND dt.deficiency_status_id =
ds.deficiency_status_id
AND ds.is_outstanding
AND dt.assigned_supplier_id = '101690'
) AS def_count_30_60,
(SELECT count
[SQL] primary keys
Hi, I want to create a table which has 2 columns, and both columns have to be primary key (or: together they are the primary key). How can I do this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. Thanks Huub ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] primary keys
A table can only have ONE primary key. It can have additional indexes with a UNIQUE restriction, thereby forcing the second field to be as good as a primary key. That is probably what you are looking for, however, you can also in PG Admin II select multiple fields as being the member of the primary key. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Huub > Sent: Wednesday, November 06, 2002 9:19 AM > To: [EMAIL PROTECTED] > Subject: [SQL] primary keys > > > Hi, > > I want to create a table which has 2 columns, and both > columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. > > Thanks > > Huub > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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] primary keys
Hi, For example you can do something like that: CREATE TABLE "try" ( "field1"TEXT NOT NULL, "field2"INT4 NOT NULL, "field3"TEXT, PRIMARY KEY (field1, field2)); ** [EMAIL PROTECTED] Laboratoire de prehistoire du Lazaret 33 bis bd Franck Pilatte 06300 Nice tel:04-92-00-17-37/fax:04-92-00-17-39 Windows a bug's life On Wed, 6 Nov 2002, Huub wrote: > Hi, > > I want to create a table which has 2 columns, and both columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. > > Thanks > > Huub > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to get the source table & field name of a view field
> Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? Within psql, use \d to learn about the view's column names and types and the view definition. If you were thinking about querying system tables to get this information, start psql with the "-E" option to see how this \d command is implemented. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] primary keys
On Wed, Nov 06, 2002 at 15:18:38 +0100, Huub <[EMAIL PROTECTED]> wrote: > Hi, > > I want to create a table which has 2 columns, and both columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. You can use a PRIMARY KEY table constraint. You can the SQL command documentation for the CREATE TABLE command. ---(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] primary keys
How do you select these sub Primary Keys? I tried using Shift-Select, Ctrl_Selec, but doesn't work. Thanks Sangeetha -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 8:54 AM To: 'Huub'; [EMAIL PROTECTED] Subject: Re: [SQL] primary keys A table can only have ONE primary key. It can have additional indexes with a UNIQUE restriction, thereby forcing the second field to be as good as a primary key. That is probably what you are looking for, however, you can also in PG Admin II select multiple fields as being the member of the primary key. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Huub > Sent: Wednesday, November 06, 2002 9:19 AM > To: [EMAIL PROTECTED] > Subject: [SQL] primary keys > > > Hi, > > I want to create a table which has 2 columns, and both > columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. > > Thanks > > Huub > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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 ---(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] primary keys
On Wed, 6 Nov 2002, Huub wrote: > Hi, > > I want to create a table which has 2 columns, and both columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. I dont know the way in pgAdminII, but from psql simply give: CREATE TABLE foo( name varchar(20) NOT NULL, id int4 NOT NULL, PRIMARY KEY (name,id)); > > Thanks > > Huub > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] primary keys
[EMAIL PROTECTED] wrote: A table can only have ONE primary key. It can have additional indexes with a UNIQUE restriction, thereby forcing the second field to be as good as a primary key. That is probably what you are looking for, however, you can also in PG Admin II select multiple fields as being the member of the primary key. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] >-Original Message- >From: [EMAIL PROTECTED] >[mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Huub >Sent: Wednesday, November 06, 2002 9:19 AM >To: [EMAIL PROTECTED] >Subject: [SQL] primary keys > > >Hi, > >I want to create a table which has 2 columns, and both >columns have to >be primary key (or: together they are the primary key). How can I do >this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. > >Thanks > >Huub > > >---(end of >broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > ---(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 Thanks..problem solved.. Huub ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to get the source table & field name of a view field
On Thu, 7 Nov 2002, Prime Ho wrote: > Hi, > > Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? SELECT definition from pg_views where viewname=''; > > Regards, > Ho > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] cast lo to oid
"Nekta Katz" <[EMAIL PROTECTED]> writes: > I have the following table > create table scan_docs ( > docid serial, > shipno numeric(10), > scanlo, > type text > ); > when I try to create the following rule > create rule "delete_scan_docs_lo" as > on delete to "scan_docs" > do select lo_unlink (old.scan); Why aren't you using the trigger that type LO provides for this purpose? Seems rather pointless to use a nonstandard type and then ignore the primary (sole) feature it provides... > create rule "delete_scan_docs_lo" as > on delete to "scan_docs" > do select lo_unlink (old.scan::oid); > "psql:scan_docs_rule.sql:3: ERROR: Cannot cast type 'lo' to 'oid' " Curious, as contrib/lo provides a function that's supposed to work for that: -- same function, named to allow it to be used as a type coercion, eg: --CREATE TABLE a (image lo); --SELECT image::oid FROM a; -- CREATE FUNCTION oid(lo) RETURNS oid AS 'MODULE_PATHNAME', 'lo_oid' LANGUAGE 'C'; What PG version are you using? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] PLpgSQL FOR IN EXECUTE question
Consider the following PLpgSQL code fragment FOR this_record IN EXECUTE ''SELECT '' || quote_ident($1) || ''FROM '' || quote_ident($2) LOOP list := list || '', '' || this_record.$1 ; END LOOP; As expected, accessing a field via this_record.$1 does not work. Can it be done otherwise? Regards, Christoph ---(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] primary keys
Uz.ytkownik Huub napisa?: > Hi, > > I want to create a table which has 2 columns, and both columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. Dependent on what you need: 1) you can create primary key based on 2 fields 2) you can't create 2 primary keys - but you can create primary key on first field and create unique index on second one. Tomasz Myrta ---(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] how to get the source table & field name of a view field
On Thu, Nov 07, 2002 at 05:12:20PM +0800, Prime Ho wrote: > Hi, > > Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? > \d view_name should give you the view definition in pqsl. -- Ken Kennedy | http://www.kenzoid.com| [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PLpgSQL FOR IN EXECUTE question
On Thursday 07 Nov 2002 2:35 pm, Christoph Haller wrote: > Consider the following PLpgSQL code fragment > > FOR this_record IN > EXECUTE ''SELECT '' > > || quote_ident($1) > || ''FROM '' > || quote_ident($2) > > LOOP > list := list || '', '' || this_record.$1 ; > END LOOP; > > As expected, accessing a field via this_record.$1 > does not work. > Can it be done otherwise? Perhaps "SELECT ... AS known_name FROM ..." and then this_record.known_name? -- Richard Huxton ---(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] Copying a rowtype variable.
I would personnaly like this feature (assigning a composite from another similar composite) to be added to PLPGSQL. Another nice feature would be to able to insert a composite into a table without have to name all atributes. Just my $.02 "Rison, Stuart" wrote: > > >> 2) I am looking for an elegant way of copying a rowtype variable: > >> > >> eg. > >> > >> DECLARE > >> current_row orf%ROWTYPE; > >> previous_row orf%ROWTYPE; > >> BEGIN > >> > >> LOOP > >> -- use cursors or FOR SELECT to get values into current_row > >> -- now try this: > >> > >> previous_row = current_row; > >> END LOOP; > >> END; > >> > >> Now, as I anticipated, this fails because a rowtype variable is a > >> composite > >> variable. One working alternative is to do: > >> > > > > I haven't tried this. One thing I notice above is that you're using > > the equality operator "=" instead of the assignment operator ":=" . > > Usually Postgres lets you slack on this, but it would be worth trying > > to see whether that has an effect on the problem. > > > > Fair point. But "previous_row := current_row" doesn't work either. > > > Another thing to try is, instead of a simple variable assignment > > > > SELECT current_row INTO previous_row; > > > > ... and see if that works. > > Well, I had high hopes for that one... but it didn't work either! > > > I'll tinker later today; there has to be a way to do it. > > I'd definitely appreciate further suggestions, but thanks all the same for > you help. I have a feeling that you might have to write a PL function to > perform the operation... but I haven't really thought about it! > > Stuart. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Weird NULL behavior
On Thu, 7 Nov 2002, Ludwig Lim wrote: > Hi: > > Has anyone encountered this before? > SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0)); > > > returns the following error message: > Cannot cast type '"char"' to '"numeric"' It seems to me that it's trying to decide on a type for the expression NULL * NULL. It's a NULL, but a NULL of what type? I think the spec gets around this by disallowing such structures AFAIK (NULL can be used in like row value constructors, case and cast). I think the "sql" way of doing the above would be select cast(cast(NULL as NUMERIC(2,0))*cast(NULL as NUMERIC(2,0)) as NUMERIC(2,0)); ---(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] Copying a rowtype variable.
Josh has submitted an appropriate report to pgsql-bugs... so perhaps it will happen! Stuart. > -Original Message- > From: Jean-Luc Lachance [mailto:jllachan@;nsd.ca] > Sent: 07 November 2002 16:29 > To: Rison, Stuart > Cc: ''[EMAIL PROTECTED]' '; 'Josh Berkus ' > Subject: Re: [SQL] Copying a rowtype variable. > > I would personnaly like this feature (assigning a composite from another > similar composite) to be added to PLPGSQL. Another nice feature would be > to able to insert a composite into a table without have to name all > atributes. > > Just my $.02 > > "Rison, Stuart" wrote: > > >> 2) I am looking for an elegant way of copying a rowtype variable: > >> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Generating a cross tab (pivot table)
Richard Huxton wrote: On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote: Regarding to Terry's request on multiple aggregates and Shahbaz's request for generating a cross tab ( pivot table ) in September, I've found an excellent example on a german inet page http://www.itrain.de/ I've translated it and think it's useful for many who subscribed (I hope so, maybe it was told before, but I couldn't find anything about this topic in the techdecs). Very useful. Also note there are some examples of how to produce crosstab results in the table-functions contrib directory in 7.3 Just to amplify a bit, in contrib/tablefunc there is a family of functions called crosstabN(), where N is 2, 3 and 4. These are meant as examples -- you could, for example, create a function crosstab5() if you need it. There is also a function called crosstab(), which returns type RECORD and thus requires the column definition to be specified in the query. See contrib/tablefunc/README.tablefunc for more details and examples. These were done as relatively crude examples and therefore have some limitations which may or may not be a problem for you. If people find the functions useful and provide suggestions for improvement in functionality I'll try to upgrade them for 7.4. Thanks, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PLpgSQL FOR IN EXECUTE question
Christoph Haller <[EMAIL PROTECTED]> writes:
> Consider the following PLpgSQL code fragment
> FOR this_record IN
> EXECUTE ''SELECT ''
> || quote_ident($1)
> || ''FROM ''
> || quote_ident($2)
> LOOP
> list := list || '', '' || this_record.$1 ;
> END LOOP;
> As expected, accessing a field via this_record.$1
> does not work.
> Can it be done otherwise?
FOR this_record IN
EXECUTE ''SELECT ''
|| quote_ident($1)
|| '' AS foo FROM ''
|| quote_ident($2)
LOOP
list := list || '', '' || this_record.foo ;
END LOOP;
There is still another gotcha here though: the datatype of foo had
better remain the same every time, else the cached query plan for
the concatenation will fail. Explicitly casting to text in the
EXECUTE'd SELECT might be a good idea:
EXECUTE ''SELECT CAST(''
|| quote_ident($1)
|| '' AS TEXT) AS foo FROM ''
|| quote_ident($2)
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Quartile (etc) ranking in a SQL statement?
Here's a puzzler: Given a query that returns rows ranked by some criteria, how can I write another query around it that will give me the (say) first quartile (top 25%)? Another way of putting it is: if I have rows that look like this: aaa | 1251 aba | 1197 cax | 1042 ... | ... axq | 23 (142 rows) How can I write a query that will return these as 1 | aaa | 1251 2 | aba | 1197 3 | cax | 1042 ... | ... | ... 142 | axq | 23 -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(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] Weird NULL behavior
On Thu, 7 Nov 2002, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Thu, 7 Nov 2002, Ludwig Lim wrote: > >> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0)); > >> Cannot cast type '"char"' to '"numeric"' > > > It seems to me that it's trying to decide on a type > > for the expression NULL * NULL. It's a NULL, but a > > NULL of what type? > > Yeah, and it's picking "char" (the single-byte datatype), because > (a) the NULLs are initially regarded as type UNKNOWN, and (b) if we > don't have any other way to make a decision we try assuming that > UNKNOWNs are of string category, and (c) the only datatype in string > category that has a "*" operator is "char". > > I am kind of inclined to remove the arithmetic operators on "char" > (+,-,*,/) in 7.4 --- they don't seem to have any real-world uses, > and as this example illustrates, they are perfectly positioned to > capture cases that probably ought to be errors. That seems to make sense. I assume that they were there so that someone could treat it as a 1 byte integer? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Weird NULL behavior
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 7 Nov 2002, Tom Lane wrote: >> I am kind of inclined to remove the arithmetic operators on "char" >> (+,-,*,/) in 7.4 --- they don't seem to have any real-world uses, >> and as this example illustrates, they are perfectly positioned to >> capture cases that probably ought to be errors. > That seems to make sense. I assume that they were there so that someone > could treat it as a 1 byte integer? Presumably ... but defining a numeric type named "int1" would be a lot more sensible than overloading "char" for the purpose. regards, tom lane ---(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] Quartile (etc) ranking in a SQL statement?
In a PLPGPSQL script, once you know count(*) try execute ''select * from table limit '' || int4( theCount / 4); If you want numbering, create a sequence and add nextval() to the query. JLL Jeff Boes wrote: > > Here's a puzzler: > > Given a query that returns rows ranked by some criteria, how can I write > another query around it that will give me the (say) first quartile (top > 25%)? Another way of putting it is: if I have rows that look like this: > > aaa | 1251 > aba | 1197 > cax | 1042 > ... | ... > axq | 23 > (142 rows) > > How can I write a query that will return these as > > 1 | aaa | 1251 > 2 | aba | 1197 > 3 | cax | 1042 > ... | ... | ... > 142 | axq | 23 > > -- > Jeff Boes vox 616.226.9550 ext 24 > Database Engineer fax 616.349.9076 > Nexcerpt, Inc. http://www.nexcerpt.com >...Nexcerpt... Extend your Expertise > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PLpgSQL FOR IN EXECUTE question
Chris, > FOR this_record IN > EXECUTE ''SELECT '' > || quote_ident($1) > || ''FROM '' > || quote_ident($2) > LOOP > list := list || '', '' || this_record.$1 ; > END LOOP; > > As expected, accessing a field via this_record.$1 > does not work. > Can it be done otherwise? Yes. Alias the columns: FOR this_record IN EXECUTE ''SELECT '' || quote_ident($1) || '' AS col1 FROM '' || quote_ident($2) || '' AS col2'' LOOP list := list || '', '' || this_record.col1 ; END LOOP; ---(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] Delete cascade for multi tables?
Hello, I have several tables and one table is a reference table, all other tables are master tablls of this reference table. One record in these master tables have several records in the reference table. I want to delete cascade when delete one record one reocrd in mater tables and delete the releated records in the referecne table. How can I create reference table? Thanks. Zhidian Du _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(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] No promany key in parent table, how to use delete cascade?
Dear All, I want to dreate a delete cascade in children tables. The primary key of parent table is oid, so when I create parent table, there is no apparetly key word "primary key". The problem jumps out. When I create child table using columnn constraint on delete, the SQL says: "PRIMARY KEY for referenced table "parent" not found" I do not need to specify the primary key since I am using oid. How can I get around this problem? Thanks. Zhidian Du _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] No promany key in parent table, how to use delete cascade?
On Thu, 7 Nov 2002, Zhidian Du wrote: > Dear All, > > I want to dreate a delete cascade in children tables. The primary key of > parent table is oid, so when I create parent table, there is no apparetly > key word "primary key". The problem jumps out. > > When I create child table using columnn constraint on delete, the SQL says: > > "PRIMARY KEY for referenced table "parent" not found" > > I do not need to specify the primary key since I am using oid. How can I > get around this problem? I'm not sure if 7.2 lets you make the constraint at all, but even if it does, you need a unique index on oid and you need to specify that the constraint is to oid (col references parent(oid)) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Alter table
Hi, I wonder if it is possible to remove a field of a table ? I haven't found anything about this into the reference manual. Can I do something like that ?: ALTER TABLE table DROP COLUMN column; Thanks Rachel ** [EMAIL PROTECTED] Laboratoire de prehistoire du Lazaret 33 bis bd Franck Pilatte 06300 Nice http://rachel.familinux.org Windows a bug's life ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] changing numeric into int
Hi, I want to change 2 columns in the same table from numeric into int. Can I do this without deleting the old table and creating a new one? Data stays the same.. Thanks Huub ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] [SQL] Problem: Referential Integrity Constraints lost
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > After recreating the missing triggers should i upgrade > to 7.2.3?? Make that "before". I frankly suspect pilot error here. Triggers do not simply disappear. If you did have crash-induced corruption leading to loss of some rows in pg_trigger, it would be exceedingly obvious because *no* operations on the affected tables would work --- relcache would complain about the fact that pg_class.reltriggers didn't match the number of rows in pg_trigger. I think the missing triggers must have been removed or disabled deliberately. (Which is not to say that it couldn't have been a software bug, but you're barking up the wrong tree to blame it on a crash.) Did all the triggers of the affected tables disappear, or only some of them? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [SQL] Problem: Referential Integrity Constraints lost
On Thu, 7 Nov 2002, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > After recreating the missing triggers should i upgrade > > to 7.2.3?? > > Make that "before". > > I frankly suspect pilot error here. Triggers do not simply disappear. > If you did have crash-induced corruption leading to loss of some rows > in pg_trigger, it would be exceedingly obvious because *no* operations > on the affected tables would work --- relcache would complain about the > fact that pg_class.reltriggers didn't match the number of rows in > pg_trigger. I think the missing triggers must have been removed or > disabled deliberately. (Which is not to say that it couldn't have been > a software bug, but you're barking up the wrong tree to blame it on a > crash.) > Did all the triggers of the affected tables disappear, or only some > of them? Just some of them. I really dont know what happened. Looking back at july backups the problem was already there. I never played with system tables in production. I hope to be able somehow to reproduce the problem, or convince myself its my fault for some reason. P.S. I was surprized when i looked up in my english dictionary the word "deliberately" :) > > regards, tom lane > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
