Re: [SQL] pg, mysql comparison with "group by" clause
"Anthony Molinaro" <[EMAIL PROTECTED]> writes: > More awkward? What *you're* suggesting is more awkward. You realize that > right? How can syntax that is understood and accepted for years be more > awkward? Well gosh, I would say that that's something only a newbie could say about SQL of all things... I had a whole thing written after that but I just deleted it. I grow tired of this thread. I am pretty happy to hear that the SQL standard endorsed the idea having the right thing happen if the primary key is present in the grouping list. That would be a wonderful feature for Postgres. -- greg ---(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] copy tables
am 14.10.2005, um 8:54:39 +0200 mailte Shavonne Marietta Wijesinghe folgendes: > Hello > > I have 2 postgreSQL databases called "DATA1" and "DATA2" with several tables > inside them (table A, B, C). > > I modify the details in table "A" in database "DATA1" > > How can I copy table "A" from database "DATA1" and paste it in database > "DATA2" using the same table name ?? Backup the table A from DATA1 and restore it into DATA2. (pg_dump with -t dumps only the specified table) Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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
[SQL] copy table
Hello How can i copy a table from one schema to another.. Example.. * I have a database "mydatabase" * then i have 2 schemas "myschema1" and "myschema2" * in "myschema1" i have inserted a new line and i want to copy that to "myschema2" so they both will be same.. How can i do it with SQL ?? I use pgadmin 3 to make my database in postgresql thank u
[SQL] Design problemi : using the same primary keys for inherited objects.
Hi everybody, i've got a strange design question to ask you. It's something I couldn't answer to while feeling confusely it was an absolutely BAD thing to do. For our application, we have developed our own framework which sits on top of PostgreSQL. It uses object programming and implements heritage. Inherited objects use heritage links in the framework and relation links in PostgreSQL (Search me why it doesn't use heritage in PostgreSQL !?). I've got this thing : An object A inherits from an object B, which inherits from a object C, which inherits from an object D. One of my colleagues proposed that we don't use serial (integer + sequence) primary keys for these objects, but that we use the very same integer primary keys. That is : the instance A would use the id 12343, and the instance B the same id 12343 and the instance C the same id 12343 and the D instance the same id 12343. It's possible as two instances of an object never inherit from a same instance of another object. The id seems to me absolutely bad, but I wouldn't know how to phrase why. Any suggestion ? Thanks in advance, David. -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] copy table
am 14.10.2005, um 10:44:13 +0200 mailte Shavonne Marietta Wijesinghe folgendes: > Hello > > How can i copy a table from one schema to another.. create table as select * from table > > Example.. Yeah! test=# create schema foo; CREATE SCHEMA test=# create schema bar; CREATE SCHEMA test=# create table foo.test (id int); CREATE TABLE test=# insert into foo.test values (1); INSERT 3381544 1 test=# create table bar.test as select * from foo.test; SELECT test=# select * from foo.test; id 1 (1 row) test=# select * from bar.test; id 1 (1 row) > I use pgadmin 3 to make my database in postgresql You shold better use psql to learn sql. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg, mysql comparison with "group by" clause
Sheesh... I work with 2 MIT guys, and man, you guys will never admit you're wrong. Must be something in the water down there ;) Hey man, you know what, to each his own (but Apostol is one of my favorites, so maybe have that in common? :) You apparently like this shortcut, so be it. I'll say this tho, Oracle and db2 don't do it even tho it's in the standard and their optimizer is already doing the right thing. That's gotta tell ya something, no? don't look for this feature to be something you can do everywhere. If for some reason postgres implements it, it will be the only vendor to do so (though, this seems like a very MySQL-ish thing to do so maybe not just Postgres) take care, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 14, 2005 3:21 AM To: Anthony Molinaro Cc: Greg Stark; Scott Marlowe; Stephan Szabo; Rick Schumeyer; pgsql-sql@postgresql.org Subject: Re: [SQL] pg, mysql comparison with "group by" clause "Anthony Molinaro" <[EMAIL PROTECTED]> writes: > More awkward? What *you're* suggesting is more awkward. You realize that > right? How can syntax that is understood and accepted for years be more > awkward? Well gosh, I would say that that's something only a newbie could say about SQL of all things... I had a whole thing written after that but I just deleted it. I grow tired of this thread. I am pretty happy to hear that the SQL standard endorsed the idea having the right thing happen if the primary key is present in the grouping list. That would be a wonderful feature for Postgres. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Design problemi : using the same primary keys for inherited objects.
- Original Message - i've got a strange design question to ask you.It's something I couldn't answer to while feeling confusely it was anabsolutely BAD thing to do.For our application, we have developed our own framework which sits ontop of PostgreSQL. It uses object programming and implements heritage.Inherited objects use heritage links in the framework and relation linksin PostgreSQL (Search me why it doesn't use heritage in PostgreSQL !?).I've got this thing :An object A inherits from an object B, which inherits from a object C,which inherits from an object D.One of my colleagues proposed that we don't use serial (integer +sequence) primary keys for these objects, but that we use the very sameinteger primary keys.That is : the instance A would use the id 12343, and the instance B thesame id 12343 and the instance C the same id 12343 and the D instance thesame id 12343.It's possible as two instances of an object never inherit from a sameinstance of another object.The id seems to me absolutely bad, but I wouldn't know how to phrasewhy.Any suggestion ?Thanks in advance,David. Most of the inheritance i've seen done in databases retain the parent primary as a foreign key and a primary key. That being said, only you and your team can decide if more than one object will extend a base class. If you were doing something more like this person -> sweepstakes entry to model a sweepsakes entry is a person, and you allow a person to enter a sweepstakes more than once, but to enter a contest the user must provide a unique email address, then you could not just use a foreign key as the primary key in sweepstakes, since the primary key would disallow multiple entries in sweepstakes entry, you would then use a serial data type in both person and sweepstakes along with the foriegn key in sweepstakes from person. The answer depends on the need. Hope that helps. Russ
[SQL] pgOleDb
Hi all, I'm working with postgres 8.0.1 in a VB 6.0 application using pgOleDb 1.0.0.19. I'm facing problems when using some features of Recordset object like: AbsolutePage, RecordCount, PageSize, PageCount. Are these properties implemented??? Do I need make anything to enable then??? Is there a new version of pgOleDb that solve these problems?? thanks in advance Alessandro - Liga One Sistemas LtdaRua Marins Alvarino, 150, Itararé, Vitória - ES CEP: 29.047-660 Tel (27)3324-4097 R 210[EMAIL PROTECTED]www.ligaone.com.br
Re: [SQL] Design problem : using the same primary keys for inherited objects.
> Most of the inheritance i've seen done in databases retain the parent primary > as a foreign key and a primary key. That being said, only you and your team > can decide if more than one object will extend a base class. If you were > doing something more like this > person -> sweepstakes entry > to model a sweepsakes entry is a person, and you allow a person to enter a > sweepstakes more than once, but to enter a contest the user must provide a > unique email address, then you could not just use a foreign key as the > primary key in sweepstakes, since the primary key would disallow multiple > entries in sweepstakes entry, you would then use a serial data type in both > person and sweepstakes along with the foriegn key in sweepstakes from person. > The answer depends on the need. Hope that helps. Thanks Russ, but well... It doesn't help me a lot. Our needs seem to allow that we use an id as primary key and foreign key at the same time. What i fear more is that it be against a good database design practice, because leading to potential problems. I give a clearer example : CREATE TABLE actor ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 ) CREATE TABLE person ( id_person INTEGER PRIMARY KEY REFERENCES actor, arg3 type3, arg4 type4 ) Don't you think it is a BAD design ? If it isn't, well, it will expand my database practices. David -- David Pradier -- Directeur Technique de Clarisys Informatique -- Chef de projet logiciels libres / open-source ---(end of broadcast)--- TIP 1: 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] sql function
I have 2 schemas "operativo" and "autore" and they both have a table "PECDT00" i want to create a function with the following sql command (using the programme pgadmin 3) in pgadmin 3 there is a small wizard to fill when u create a function and u have to add the return type.. this is the sql statement i wanna do DELETE FROM operativo.PECDT00; insert into operativo.PECDT00 (select * from autore.PECDT00); what can i use ad the return type?? thank u
Re: [SQL] sql function
Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> schrieb: > I have 2 schemas "operativo" and "autore" and they both have a table "PECDT00" > > i want to create a function with the following sql command (using the > programme > pgadmin 3) in pgadmin 3 there is a small wizard to fill when u create a > function and u have to add the return type.. > > this is the sql statement i wanna do > > DELETE FROM operativo.PECDT00; > insert into operativo.PECDT00 (select * from autore.PECDT00); > > what can i use ad the return type?? Simple. Open a new text file: ,[ store this as table_copy.sql ] | create or replace function table_copy() returns void as $$ | delete from operative.PECDT00; | insert into operativo.PECDT00 select * from autore.PECDT00; | $$ language sql; ` Now call psql with your Database. ,[ log from psql ] | test=>\i table_copy.sql | CREATE FUNCTION | | test=> select table_copy(); | table_copy | | | (1 Zeile) ` I say it again: use the fine command line tool psql to learn SQL. Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] copy table
INSERT INTO MYSCHEMA2.TABLENAME SELECT * FROM MYSCHEMA1.TABLENAME WHERE < CONDITION >Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote: Hello How can i copy a table from one schema to another.. Example.. * I have a database "mydatabase" * then i have 2 schemas "myschema1" and "myschema2" * in "myschema1" i have inserted a new line and i want to copy that to "myschema2" so they both will be same.. How can i do it with SQL ?? I use pgadmin 3 to make my database in postgresql thank u Yahoo! India Matrimony: Find your partner now.
Re: [SQL] Design problem : using the same primary keys for inherited
David Pradier wrote: Most of the inheritance i've seen done in databases retain the parent primary as a foreign key and a primary key. That being said, only you and your team can decide if more than one object will extend a base class. If you were doing something more like this person -> sweepstakes entry to model a sweepsakes entry is a person, and you allow a person to enter a sweepstakes more than once, but to enter a contest the user must provide a unique email address, then you could not just use a foreign key as the primary key in sweepstakes, since the primary key would disallow multiple entries in sweepstakes entry, you would then use a serial data type in both person and sweepstakes along with the foriegn key in sweepstakes from person. The answer depends on the need. Hope that helps. Thanks Russ, but well... It doesn't help me a lot. Our needs seem to allow that we use an id as primary key and foreign key at the same time. What i fear more is that it be against a good database design practice, because leading to potential problems. I give a clearer example : CREATE TABLE actor ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 ) CREATE TABLE person ( id_person INTEGER PRIMARY KEY REFERENCES actor, arg3 type3, arg4 type4 ) Don't you think it is a BAD design ? If it isn't, well, it will expand my database practices. It *is* a bad design. You should not do this. After all, how is that any different than this? CREATE TABLE actor_person ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 arg3 type3, arg4 type4 ) Furthermore, inheritance is almost certainly the wrong relationship type here. Normally, Actor would be a Role that a Person would be playing: create table role( id serial primary key,-- 1 name char(20) not null-- "Actor" ); create table person( id serial primary key, -- 1 namechar(20) not null,-- "David" role_id int not null references role -- 1 ); David -- Daryl Richter Director of Technology (( Brandywine Asset Management ) ( "Expanding the Science of Global Investing" ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Design problemi : using the same primary keys for inherited objects.
I dont consider this to be a design problem... In fact... I do the inherited
table thing pretty routinely... It (table inheritance) works very well if
you need to implement a Sarbanes-Oxley audit trail or a "virtual" rollback
system without the overhead of managing transactions.
Consider the relationship between a company (sys_client) and the company's
employees (sys_user). An inheritance model in postgreSQL is a very efficient
mechism to ensire that user entries "roll-up-to" or are "owned" by a client
entry in the parent table. Here's a short example:
If you wish -- you can execute the following DDL.
CREATE TABLE sys_client (
id SERIAL NOT NULL PRIMARY KEY,
c_name VARCHAR(72),
lu_client_type INTEGER NOT NULL DEFAULT 4 REFERENCES lu_client_type,
lu_support_program INTEGER NOT NULL REFERENCES lu_support_program(id),
create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
change_id INTEGER DEFAULT 0,
active_flag BOOLEAN NOT NULL DEFAULT TRUE
) WITH OIDS;
CREATE TABLE lu_user_type (
id serial NOT NULL PRIMARY KEY,
type_desc varchar(72) NOT NULL,
can_delete bool DEFAULT false,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
change_id INTEGER NOT NULL DEFAULT 0,
active_flag bool NOT NULL DEFAULT true
) WITH OIDS;
INSERT INTO lu_user_type(type_desc) VALUES ('Administrator');
INSERT INTO lu_user_type(type_desc) VALUES ('User');
INSERT INTO lu_user_type(type_desc) VALUES ('Restricted user');
INSERT INTO lu_user_type(type_desc) VALUES ('Demo');
INSERT INTO lu_user_type(type_desc) VALUES ('Demo - admin');
CREATE TABLE sys_user (
sys_client_id INTEGER NOT NULL REFERENCES sys_client(id),
lu_client_group INTEGER references sys_client_group(id),
lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
f_name VARCHAR(50) NOT NULL,
m_name VARCHAR(50),
l_name VARCHAR(50) NOT NULL,
email_addr VARCHAR(120) NOT NULL,
uname VARCHAR(20) NOT NULL,
upwd VARCHAR(20) NOT NULL,
login_allowed BOOLEAN DEFAULT true,
reset_pwd BOOLEAN DEFAULT false,
pwd_change_reqd bool DEFAULT false,
lost_passwd bool DEFAULT false
) INHERITS (sys_client);
CREATE TABLE sys_user_history (
hist_id SERIAL NOT NULL PRIMARY KEY,
hist_dt TIMESTAMP NOT NULL DEFAULT NOW()
) INHERITS (sys_user);
CREATE OR REPLACE RULE sys_user_history AS ON UPDATE TO sys_user DO INSERT
INTO sys_user_history (SELECT * FROM ONLY sys_user WHERE id = OLD.id);
CREATE TABLE sys_user_login (
id serial NOT NULL PRIMARY KEY,
sys_client INTEGER NOT NULL REFERENCES sys_client(id),
login_date timestamp NOT NULL DEFAULT now(),
valid_until timestamp NOT NULL DEFAULT (now() + '00:20:00'::interval),
session_id varchar(32) NOT NULL UNIQUE,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
change_id int4 NOT NULL DEFAULT 0,
active_flag bool NOT NULL DEFAULT true
) WITH OIDS;
insert into sys_client(c_name) VALUES ('Mattel');
insert into sys_client(c_name) VALUES ('Hasbro');
insert into sys_client(c_name) VALUES ('Lego');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (1, 1, 'Arnold', 'Antione', 'Aardvaark',
'[EMAIL PROTECTED]', 'arnie', 'arnie');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (1, 2, 'Roberto', 'Guiterrez', 'Amendola',
'[EMAIL PROTECTED]', 'arnie', 'arnie');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (2, 1, 'Albert', '', 'Einstien',
'[EMAIL PROTECTED]', 'albert', 'albert');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (2, 2, 'David', 'C', 'Davidson',
'[EMAIL PROTECTED]', 'david', 'david');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (3, 1, 'Marilyn', '', 'Monroe',
'[EMAIL PROTECTED]', 'mmonroe', 'mmonroe');
INSERT INTO sys_user(sys_client_id, lu_user_type, f_name, m_name, l_name,
email_addr, uname, upwd) VALUES (3, 2, 'Fred', 'E', 'Flintstone',
'[EMAIL PROTECTED]', 'fred', 'fred');
SET SQL_INHERITANCE = ON;
-- RUN EACH OF THESE QUERIES IN SUCESSION TO SEE HOW IT REALLY WORKS...
SELECT * FROM sys_client;
SELECT * FROM ONLY sys_client;
SELECT * FROM sys_user;
SELECT * FROM ONLY sys_user;
SELECT SC.id AS client_id, SC.c_name, SU.id AS employee_id, SU.f_name,
SU.l_name FROM sys_client SC JOIN sys_user SU ON SU.sys_client_id = SC.id;
"David Pradier" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi everybody,
>
> i've got a strange design question to ask you.
> It's something I couldn't answer to while feeling confusely it was an
> absolutely BAD thing to do.
>
> For our application, we have developed our own framework which sits on
> top of PostgreSQL. It uses object programming and implements heritage.
> Inherited objects use heritage links in
Re: [SQL] Design problem : using the same primary keys for inherited objects.
Thanks Russ, but well... It doesn't help me a lot. Our needs seem to allow that we use an id as primary key and foreign key at the same time. What i fear more is that it be against a good database design practice, because leading to potential problems. I give a clearer example : CREATE TABLE actor ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 ) CREATE TABLE person ( id_person INTEGER PRIMARY KEY REFERENCES actor, arg3 type3, arg4 type4 ) Don't you think it is a BAD design ? If it isn't, well, it will expand my database practices. That is perfectly valid. Only, I would argue that an actor is a person. What I was offering was dealing with issues where more then one actor could be the same person. Given your design, a person could only be one actor. If that is true, no more discussion is needed. If that is not true, then one way to deal with that is to make compound primary keys in your actor table. table person ( person_id serial primary key, name varchar(20)); table actor( person_id foreign key references person, role varchar(20), primary key ( person_id, role ) ); would then allow a person to be more then on actor based on role. ---(end of broadcast)--- TIP 1: 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] owner of data type "areas" appears to be invalid ?
[PostgreSQL 7.4RC2, x86 linux]
Several tables are giving the errors like:
pg_dump: WARNING: owner of data type "areas" appears to be invalid
from pg_dump.
This is my production database, (50 users, 18 hours/day, 21MB compressed dump).
The output of "pg_dump -t areas" starts with:
REVOKE ALL ON TABLE areas FROM PUBLIC;
REVOKE ALL ON TABLE areas FROM geoyou;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO "101" WITH GRANT OPTION;
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO PUBLIC;
RESET SESSION AUTHORIZATION;
GRANT ALL ON TABLE areas TO PUBLIC;
But "101" is the numerical group id of the "operator" group! (see dump from
pg_group below). The table owner is "geoyou" as seen from the pg_class select
below. User "geoyou" is in pg_user with usesysid=501. There is no row in
pg_user with usesysid=101, and there is none with usename "101".
How can I fix this? I must be able to get clean dumps that can be reloaded in
case of a crash.
-- George Young
pig5=> select * from pg_class where relname='areas';
relname | relnamespace | reltype | relowner | relam | relfilenode | relpages
| reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs |
relhasoids | relhaspkey | relhasrules | relhassubclass |relacl
-+--+--+--+---+-+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++---
areas | 2200 | 14745246 | 501 | 0 |14745245 |1
|41 | 14745247 | 0 | f | f | r
|2 | 0 | 0 |0 |0 | 0 | t
| f | f | f |
{101=a*r*w*d*R*x*t*/101,=arwdRxt/101}
pig5=> select * from pg_user where usesysid=501;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil
| useconfig
-+--+-+--+---+--+--+---
geoyou | 501 | f | f| f | |
|
select * from pg_group where grosysid=101;
groname | grosysid |
grolist
--+--+---
operator | 101 |
{602,616,509,525,614,514,617,539,517,558,581,567,506,609,94,511,573,17115,327,17422,512,537,626,503,519,583,547,570,584,78,10980,518,557,564,528,546,592,599,613,510,513,536,554,500,530,594,608,524,17114,533,17116,17289,17290,17292,17294,17345,17347,17421,17423,17425,214,17430,17427,17428,574,11,391,17431,17667,17703,8309,17769,17842,17773,17874,17877,13283,12758,17966,1,17902,18099,18117,18129,18170,18173,18163,32766,18195,18202,18208,17786,17704,18375,18322,18399,18410,17904,18438,18424,28424,18437,102137,9877,502,32768,18553,13065,10681,8245,17049,15885,15886,8977,18706,18717}
select * from pg_tables where tablename='areas';
schemaname | tablename | tableowner | hasindexes | hasrules | hastriggers
+---+++--+-
public | areas | geoyou | f | f| f
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] owner of data type "areas" appears to be invalid ?
george young writes: > How can I fix this? Re-create the owning user (which you evidently dropped), assigning it sysid 101. PG 8.1 will make it impossible to drop users who still own objects or have permissions ... although that will bring its own set of gotchas ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] owner of data type "areas" appears to be invalid ?
Yes, that worked. Thank you very much! -- George On Fri, 14 Oct 2005 12:04:13 -0400 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > > How can I fix this? > > Re-create the owning user (which you evidently dropped), assigning it > sysid 101. > > PG 8.1 will make it impossible to drop users who still own objects or > have permissions ... although that will bring its own set of gotchas ... > > regards, tom lane > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] FULL OUTER JOIN Question
I have a question about a full outer join returning duplicate rows. I have one table that stores a record for each transaction with totals: CREATE TABLE trans ( id serial PRIMARY KEY, stamp timestamp DEFAULT now(), trans_type_id int NOT NULL REFERENCES trans_type(id), subtotal numeric(6,2), tax numeric(6,2), total_cash numeric(6,2), total_credit numeric(6,2), total_check numeric(6,2), total_gift numeric(6,2) ); I also have a table that stores each item sold: CREATE TABLE trans_item ( id serial PRIMARY KEY, trans_id int NOT NULL REFERENCES trans(id), parent int REFERENCES trans_item(id), qty int NOT NULL DEFAULT 1, item_sku text NOT NULL CHECK(item_sku <> '') REFERENCES item(sku), item_price numeric(5,2), item_tax numeric(4,4) ); Each item can be 'modified' by another item (only one tier of this). This is handled with the 'parent' column. If I sell Item A with an extra, say Item B it would look something like this (assume the trans_item_id sequence is starting at 1) INSERT INTO trans_item (trans_id,qty,item_sku,item_price,item_tax) VALUES (1,1,'itema',5.00,0.07); INSERT INTO trans_item (trans_id,parent,item_sku,item_price,item_tax) VALUES (1,1,'itemb',1.00,0.07); My 'trans' table records the totals for this transaction but I need to be able to recreate the math using just the trans_item rows as well. Sometimes an modifier is flagged to be sold as a free extra to the parent. When I have items that are free my query returns two rows, one with the total and modifier totals and one without. If I add a price to the modifier that was supposed to be free it functions correctly. I've removed a lot of the tax and discounting math to make the query more simple to look at, it fails the same way with or without it. here is my query: CREATE VIEW item_test AS SELECT p.id, p.trans_id, (p.item_price*p.qty)+COALESCE(sum(m.item_price)*p.qty,0) as parent_subtotal, COALESCE(sum(m.item_price)*p.qty,0) as mod_subtotal FROM trans_item p FULL OUTER JOIN trans_item m ON p.id=m.parent WHERE p.parent is null GROUP BY p.id,p.trans_id,p.item_price,p.qty,m.item_price; BAD RESULT: mg=# select * from trans_item where id=20116; id | trans_id | parent | qty | item_sku | item_price | item_tax ---+--++-+--++-- 20116 | 11216 | 20115 | 1 | 91400 | 0.50 | 0.0700 (1 row) mg=# select * from trans where id=20116; id | stamp | trans_type_id | subtotal | tax | total_cash | total_credit | total_check | total_gift ---+-+---+--+--++--+-+ 20116 | 2005-10-14 12:58:13.671 | 1 | 2.25 | 0.16 | 5.00 | 0.00 | 0.00 | 0.00 (1 row) mg=# select * from trans_item where trans_id=20116; id | trans_id | parent | qty | item_sku | item_price | item_tax ---+--++-+--++-- 36437 | 20116 | | 1 | 1 | 1.75 | 0.0700 36438 | 20116 | 36437 | 1 | 91200 | 0.50 | 0.0700 36439 | 20116 | 36437 | 1 | 90100 | 0.00 | 0.0700 (3 rows) mg=# select * from item_test where trans_id=20116; id | trans_id | parent_subtotal | mod_subtotal ---+--+-+-- 36437 | 20116 | 1.75 | 0.00 36437 | 20116 | 2.25 | 0.50 (2 rows) ^ This should only return the second row. What gives? If anyone has the time to look at this I would greatly appreciate it! Best, Tyler Kellen
[SQL] FULL OUTER JOIN Question (mistake)
The first query under 'BAD RESULT:' doesn't have anything to do with my question Please disregard it! mg=# select * from trans_item where id=20116; id | trans_id | parent | qty | item_sku | item_price | item_tax ---+--++-+--++-- 20116 | 11216 | 20115 | 1 | 91400 | 0.50 | 0.0700 (1 row) ^ that Best, Tyler Kellen
[SQL] cast
Hello I have a query that ran in 7.0.2, but in 8.0.1 does not, the query is the next: select n.factura, n.venta_neta, c.nombre_cli || ' ' || c.apellido_pat_cli || ' ' || coalesce (c.apellido_mat_cli,''), date(n.fecha_hora_factura), o.nombre_oft from nota_venta n, clientes c, oft_central o wheredate(n.fecha_hora_factura) >= '2005-10-01' and date(n.fecha_hora_factura) <= '2005-10-14' and n.id_cliente = c.id_cliente and cast(n.udf4 as integer) = o.id_oft_central and n.factura is not null and n.s_factura != 'T' order by o.nombre_oft"; it aparently returns an error because the cast, but I need to do the cast, does somebody knows how can I change the cast but with the same result, thnks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] cast
On Fri, Oct 14, 2005 at 01:08:43PM -0500, Judith Altamirano Figueroa wrote: > Hello I have a query that ran in 7.0.2, but in 8.0.1 does not, the query > is the next: It'd help if we could see the table schema and the error message. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(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] FULL OUTER JOIN Question
Tyler Kellen <[EMAIL PROTECTED]> writes: > I have a question about a full outer join returning duplicate rows. Why do you think they are duplicate? The GROUP BY includes many columns that you can't see directly in the output ... Also, you did not show us the actual input data (where's the rows with trans_item.parent=20116?) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] copy tables
In DATA1 you run SQL Query: copy A to 'PATH\tempA.txt' Then in DATA2 you run SQL Query: copy A from 'PATH\tempA.txt' good luck ! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Shavonne Marietta WijesingheSent: vendredi 14 octobre 2005 13:55To: pgsql-sql@postgresql.orgSubject: [SQL] copy tables HelloI have 2 postgreSQL databases called "DATA1" and "DATA2" with several tables inside them (table A, B, C). I modify the details in table "A" in database "DATA1" How can I copy table "A" from database "DATA1" and paste it in database "DATA2" using the same table name ??Thank you.
