[SQL] Primary key with oid + name : error, which solution ?
I create two table repertory and person. And i have an error : > CREATE TABLE repertory (name_rep name, attribut text[], PRIMARY KEY(name_rep)); > CREATE TABLE person (nam_rep repertory, name_pers text, url text, eadr text, tel text, attribut text[], PRIMARY KEY(name_pers,nam_rep)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'person_pkey' for table 'person' ERROR: DefineIndex: type repertory has no default operator class which solution for this problem ? Stéphane Chomat 22 rue Le Brix 38100 Grenoble 603-999-478 [EMAIL PROTECTED]
[SQL] transposing data for a view
I have a generalized table: scanid | region | volume - 1 A 34.4 1 B 32.1 1 C 29.1 2 A 32.4 2 B 33.2 2 C 35.6 . . . I want to create a flattened out view that looks like the following: scanid | A_volume | B_volume | C_volume 134.4 32.1 29.1 232.4 33.2 35.6 . . . How do I correctly/efficiently construct a psql query to pivot/transpose the data? I am using postgreSQL version 7.0.x thanks, Jeremy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] postgresql error
Sir, I m getting an error that say pg_ReadData and also after which then database connection is lost/disconnected . Can anybody tell me more about this error ? My problem is that i have a databasee with huge no. of records but if i go to take query/backup of the database the pg_Readdata error occurs and backup is stopped always at the same point. I tried to shift the /data/base dir to other distribution to restore my data but same error occured.(I have postgres 7.0.2 & suse distribution) I m in such a position that if i make a fresh install of postgres in the mc then my database will go resulting in hammering me. pls tell me a way to go about it I have posted the error with the earlier mail TIA Sharmad >[EMAIL PROTECTED] writes: >> Is there anyway to increase the query time in postgresql >> because if the query is long and so is the database then the pgRead >> error occurs resulting in the break of connection with the database >> and recently also giving me problems in taking backups. > >There is no query timeout in Postgres. How about telling us exactly >what you're doing and what symptoms you see, instead of jumping to a >conclusion about the cause? > > regards, tom lane > >---(end of broadcast)--- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] transposing data for a view
I think this might do the trick...
CREATE FUNCTION hori_view() RETURNS BOOLEAN AS '
DECLARE
view_select TEXT;
view_from TEXT;
view_where TEXT;
column_name TEXT;
last_column_name TEXT;
g_row generalized_table%ROWTYPE;
BEGIN
SELECT region INTO column_name
FROM generalized_table ORDER BY region LIMIT 1;
view_select := ''SELECT '' || column_name ||
''.scanid, '' || column_name || ''.volume AS "'' ||
column_name || ''_volume"'';
view_from := '' FROM generalized_table '' || column_name;
view_where := '' WHERE '' ||column_name ||
''.region = '' || column_name || ;
last_column_name := column_name;
FOR g_row IN SELECT DISTINCT ON (region) *
FROM generalized_table ORDER BY region OFFSET 1 LOOP
view_select := view_select || '', '' || g_row.region ||
''.volume AS "'' || g_row.region || ''_volume"'';
view_from := view_from || '' JOIN generalized_table '' ||
g_row.region || '' ON ('' || last_column_name ||
''.scanid = '' || g_row.region || ''.scanid)'';
view_where := view_where || '' AND '' || g_row.region ||
''.region = '' || g_row.region || ;
last_column_name := g_row.region;
END LOOP;
EXECUTE ''CREATE VIEW generalized_view AS '' || view_select ||
view_from || view_where;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
SELECT hori_view();
SELECT * FROM generalized_view;
Ok, it may not be pretty but it works,
Regards,
Aasmund.
On Wed, 31 Oct 2001 12:42:10 -0800, "Josh Berkus" <[EMAIL PROTECTED]> wrote:
> Jeremy,
>
> First, to do a pivot table, you have to be using Postgres 7.1.x. 7.0.x
> will not do it. So upgrade now.
>
>
> There are two approaches, the simple approach and the complex. The
> simple approach requires you to know in advance of building the view all
> of the possible values for your category column. The complex approach,
> which is dynamic, requires a rather sophisticated function (which I will
> write eventually, really!) so we won't go into it here.
>
> The simple approach is to create each column as a sub-select in the FROM
> clause of your statement. So, per the example above:
>
> SELECT scanid, A_volume, B_volume, C_volume
> FROM (SELECT scanid FROM volumes GROUP BY scanid) scan
> LEFT OUTER JOIN
> (SELECT scanid, volume as A_volume FROM volumes WHERE region = 'A') av
> ON scan.scanid = av.scanid LEFT OUTER JOIN
> (SELECT scanid, volume as B_volume FROM volumes WHERE region = 'B') bv
> ON scan.scanid = bv.scanid LEFT OUTER JOIN
> (SELECT scanid, volume as C_volume FROM volumes WHERE region = 'C') cv
> ON scan.scanid = cv.scanid
> ORDER BY scanid;
>
> This approach can be adapted to include aggregates and the like.
>
> -Josh Berkus
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
Aasmund Midttun Godal
[EMAIL PROTECTED] - http://www.godal.com/
+47 40 45 20 46
---(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] transposing data for a view
- Original Message - From: "H Jeremy Bockholt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 30, 2001 7:36 PM Subject: [SQL] transposing data for a view > I have a generalized table: > > scanid | region | volume > - > 1 A 34.4 > 1 B 32.1 > 1 C 29.1 > 2 A 32.4 > 2 B 33.2 > 2 C 35.6 > . > . > . > > I want to create a flattened out view that looks like the following: > > scanid | A_volume | B_volume | C_volume > > 134.4 32.1 29.1 > 232.4 33.2 35.6 > . > . > . > > How do I correctly/efficiently construct a psql query to > pivot/transpose the data? I am using postgreSQL version 7.0.x SELECT A.scanid, A.volume AS A_volume, B.volume AS B_volume, C.volume AS C_volume FROM table A JOIN table B ON (A.scanid = B.scanid) JOIN table C ON (B.scanid = C.scanid) WHERE A.region = 'A' AND B.region = 'B' AND C.region = 'C' - James ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Primary key with oid + name : error, which solution ?
On Wed, Oct 31, 2001 at 10:06:22AM +0100, St?phane Chomat wrote: > I create two table repertory and person. And i have an error : > > > CREATE TABLE repertory (name_rep name, attribut text[], PRIMARY > KEY(name_rep)); > > CREATE TABLE person (nam_rep repertory, name_pers text, url text, > eadr text, tel text, attribut text[], PRIMARY KEY(name_pers,nam_rep)); > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index > 'person_pkey' for table 'person' > ERROR: DefineIndex: type repertory has no default operator class Unless it's some OO extension in PostgreSQL that I don't know about, I have never heard of the type "repertory" and "name". I don't think you can use your table name as a data type. BTW, you should also stay away from arrays (IMHO), since they are non-standard. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer NY cops go bar-hopping; LA cops go night-clubbing. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] transposing data for a view
Jeremy, First, to do a pivot table, you have to be using Postgres 7.1.x. 7.0.x will not do it. So upgrade now. > I want to create a flattened out view that looks like the following: > > scanid | A_volume | B_volume | C_volume > > 134.4 32.1 29.1 > 232.4 33.2 35.6 There are two approaches, the simple approach and the complex. The simple approach requires you to know in advance of building the view all of the possible values for your category column. The complex approach, which is dynamic, requires a rather sophisticated function (which I will write eventually, really!) so we won't go into it here. The simple approach is to create each column as a sub-select in the FROM clause of your statement. So, per the example above: SELECT scanid, A_volume, B_volume, C_volume FROM (SELECT scanid FROM volumes GROUP BY scanid) scan LEFT OUTER JOIN (SELECT scanid, volume as A_volume FROM volumes WHERE region = 'A') av ON scan.scanid = av.scanid LEFT OUTER JOIN (SELECT scanid, volume as B_volume FROM volumes WHERE region = 'B') bv ON scan.scanid = bv.scanid LEFT OUTER JOIN (SELECT scanid, volume as C_volume FROM volumes WHERE region = 'C') cv ON scan.scanid = cv.scanid ORDER BY scanid; This approach can be adapted to include aggregates and the like. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Primary key with oid + name : error, which solution ?
On Wed, 31 Oct 2001, [ISO-8859-1] Stéphane Chomat wrote: > I create two table repertory and person. And i have an error : > > > CREATE TABLE repertory (name_rep name, attribut text[], PRIMARY > KEY(name_rep)); > > CREATE TABLE person (nam_rep repertory, name_pers text, url text, eadr > text, tel text, attribut text[], PRIMARY KEY(name_pers,nam_rep)); > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'person_pkey' > for table 'person' > ERROR: DefineIndex: type repertory has no default operator class > > which solution for this problem ? You'd probably be best off using the name of the repertory in the person table along with a foreign key. Also, IIRC name is mostly meant as an internal type. You probably really want varchar(). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Primary key with oid + name : error, which solution ?
Yes, name is an internal type. The problem is in the type of the table repertory. If it is not possible to use this syntaxe, i will take the type char. I just try the new possibility of postgressql. thanks. steph. Le mercredi 31 octobre 2001, à 11:08 , Stephan Szabo a écrit : On Wed, 31 Oct 2001, [ISO-8859-1] Stéphane Chomat wrote: I create two table repertory and person. And i have an error : CREATE TABLE repertory (name_rep name, attribut text[], PRIMARY KEY(name_rep)); CREATE TABLE person (nam_rep repertory, name_pers text, url text, eadr text, tel text, attribut text[], PRIMARY KEY(name_pers,nam_rep)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'person_pkey' for table 'person' ERROR: DefineIndex: type repertory has no default operator class which solution for this problem ? You'd probably be best off using the name of the repertory in the person table along with a foreign key. Also, IIRC name is mostly meant as an internal type. You probably really want varchar(). Stéphane Chomat 22 rue Le Brix 38100 Grenoble 603-999-478 [EMAIL PROTECTED]
Re: [SQL] Primary key with oid + name : error, which solution ?
On Wed, 31 Oct 2001, [ISO-8859-1] Stéphane Chomat wrote: > Yes, name is an internal type. The problem is in the type of the table > repertory. If it is not > possible to use this syntaxe, i will take the type char. I just try the > new possibility of postgressql. Yeah. I think the problem is that you'd need an operator class to take two objects of type repertory and sort them because the uniqueness is handled via with the index system. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
