Re: [SQL] CREATE VIEW ERROR
I found answer. Thank you for everyone who did not reply. CREATE VIEW depend_view AS SELECT depend.subfunction_id, a.subfunction_file AS x, depend.subfunction_dep_id, b.subfunction_file AS y FROM depend INNER JOIN subfunction a ON depend.subfunction_id = a.subfunction_id INNER JOIN subfunction b ON depend.subfunction_dep_id = b.subfunction_id; "Igor" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... > Hi, > > I have following SQL statement which does not report any errors: > > test=# SELECT a.subfunction_file, b.subfunction_file > test-# FROM depend INNER JOIN subfunction a ON depend.subfunction_id = > a.subfunction_id > test-# INNER JOIN subfunction b ON depend.subfunction_dep_id = > b.subfunction_id; > subfunction_file | subfunction_file > + > show_batch_2.php | search_receipt.php > ... > > But when I try to create VIEW a have following error: > > test=# create view depend_view as SELECT a.subfunction_file, > b.subfunction_file > test-# FROM depend INNER JOIN subfunction a ON depend.subfunction_id = > a.subfunction_id > test-# INNER JOIN subfunction b ON depend.subfunction_dep_id = > b.subfunction_id; > > ERROR: CREATE TABLE: attribute "subfunction_file" duplicated > <<< > > > Please advise where is my mistake. I use: > > test=# select version(); >version > - > PostgreSQL 7.2.1 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3 > (1 row) > > > Thank you, > > Igor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL QUERY
I have the following function: CREATE FUNCTION public.auto_incremento() RETURNS trigger AS 'beginnew.id = (select (max(id) + 1) from teste);return new;end;' LANGUAGE 'plpgsql' I created a trigger that uses this function, but i want to the function be usefull for all tables and not only to tbale teste. Someone know how ??? Pedro Igor
[SQL] Case-insensitive
Someone knows how config the postgresql for case-insensitive mode ? Pedro Igor
[SQL] Accent-insensitive
Does have PostgreSQL some option to allow me execute selects accent-insensitive ? I can´t find any reference, including the manual Pedro Igor
[SQL] PostgreSQL X Resin EE
Have someone used Resin EE with PostgreSQL or actually use both for building applications ? About PostgreSQL i know that is one of the best options for back-end, but what about Resin EE ? Thanks ... Pedro Igor
[SQL] Query
Regards . I hope someone can help me in this query. Have a field in a table that needs to check if another table has the value that is being inserted. Ex: table A - id int constraint pkey_id primary key, table B - id int constraint fkey_A_B references A, Here comes my doubt: table C - id int constraint fkey_A_C references A check (if exists B.id = C.id) How can i build this expression so, when I insert a tupple in table C the field will check in the table A(ok, because is a foreign key) and also in table B I have tried : check (select count(b.id) from B b where b.id = id) <> 0) but doesn´t work .. I can use trigger here, but i don´t know if is the best solution . Thanks, Pedro Igor
[SQL] Data between different databases
I would like to know in how can i reference a table in a database A from a database B. In resume, i want to separate the data in my database in two others databases and make references for them. Thanks, Pedro Igor ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003
[SQL] Function unkown
How is this function ? plpgsql_call_handler() RETURNS language_handler AS 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE; ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003
Re: [SQL] bytea or blobs?
On Wednesday 18 February 2004 09:18, you wrote: > Maybe if bandwidth is a restriction the base64 solution > saves some bandwith, since base64 file is ~ 1.3 times larger > than the original, whereas the escaped octal representation > will be ~ 4 times larger. If you use libpq's v3 protocol with binary format (PostgreSQL 7.4+), the overhead is minimal both ways. -- Best regards, Igor Shevchenko ---(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] Copy command freezes but INSERT works fine with trigger on insert.
I tried to add up records 2 different ways: 1. using command [copy messageinfo (user_id, receivedtime, filename, sendersstring, subject, hasattachments, groupmsg, msgsize, version ) FROM '/meridtemp/messageinfo.dat';] from psql 2. using simple INSERT INTO messageinfo ( user_id ) VALUES( 1728::int8 ); In 2nd case it worked but not in 1st one. Why? Later I did an experiment & repeated it few times. After copy command is running for a while from within psql and trying to insert 60,000 records I interrupted it by pressing CTRL+C few times. Each time I had different line within addmsgcountSync where it stopped. It tells me that copy command does not freeze on one particular statement but it did not insert a single record. For this table messageinfo I have trigger: CREATE TRIGGER msgInjector AFTER INSERT ON messageinfo FOR EACH ROW EXECUTE PROCEDURE addmsgcountSync(); CREATE OR REPLACE FUNCTION addmsgcountSync() RETURNS TRIGGER AS ' DECLARE currentTime injector.lastreceivedtime%TYPE; vlastreceivedtime injector.lastreceivedtime%TYPE; userIdRec RECORD; vID messageinfo.user_id%TYPE; injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injector WHERE injector.id = tt::int8 ; BEGIN vID = NEW.user_id; IF ( vID IS NOT NULL ) THEN -- Find out lastrecievedtime we need cursor OPEN injectorCursor( vID ); FETCH injectorCursor INTO userIdRec; vlastreceivedtime = userIdRec.lastreceivedtime; CLOSE injectorCursor; currentTime = CURRENT_TIMESTAMP; IF vlastreceivedtime < currentTime THEN vlastreceivedtime = currentTime; END IF; -- To make sure time of last message is not newer than lastreceivedtime time IF vlastreceivedtime < NEW.receivedtime THEN vlastreceivedtime = NEW.receivedtime; END IF; -- Stopes copy command but not insert one ? UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime = vlastreceivedtime WHERE injector.id = vID::int8; END IF; RETURN NULL; END; ' LANGUAGE 'plpgsql'; = Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(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
Re: [SQL] Copy command freezes but INSERT works fine with trigger on insert.
Copy from .. works fine for 1000 records to load. For data set of 6 records I could never get it finish. And I'm planing to reload 1000,000 records. So there is a limit somewhere between 1,000 & 60,000 since it starts working slower. The only question for me left is. What config parameter(s) I should increase to speed up copy command and to which values rougthly? Also do you know config settings to see if copy operation progresses or waits for something? __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] Why those queries do not utilize indexes?
Thanks a lot but none of those queries was effecient. 1. This query is longest. explain analyze SELECT * FROM messageinfo WHERE user_id::bigint = 2 and msgstatus::smallint = 0; 2. This one is the same as my original in performance and bad index usage. explain analyze SELECT * FROM messageinfo WHERE user_id = 2::bigint and msgstatus = 0::smallint; --- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > Artimenko Igor wrote: > > I could force Postgres to use the best index by removing condition "msgstatus = > > CAST( 0 AS > > smallint );" from WHERE clause & set enable_seqscan to off; > > Total runtime in this case dropped from 1883 ms ( sequential reads ) to 1.598 ms ( > > best index > ). > > > WHERE user_id::bigint = 2 and msgstatus:smallint = 0; > > Sincerely, > > Joshau D. Drake > > > > > > But unfortunatelly It does not resolve my problem. I can not remove above > > condition. I need to > > find a way to use whole condition "WHERE user_id = CAST( 2 AS BIGINT ) and > > msgstatus = > CAST( 0 > > AS smallint );" and still utilyze index. > > > > Yes you are right. Using "messagesStatus" index is even worse for my data set then > > sequential > > scan. > > > > Igor Artimenko > > > > --- Dennis Bjorklund <[EMAIL PROTECTED]> wrote: > > > > > >>On Fri, 27 Aug 2004, Artimenko Igor wrote: > >> > >> > >>>1. Sequential search and very high cost if set enable_seqscan to on; > >>>Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 ) > >>> > >>>2. Index scan but even bigger cost if set enable_seqscan to off; > >>>Index messagesStatus on messageinfo ( Cost=0.00..27220.72, rows=36802 ) > >> > >>So pg thinks that a sequential scan will be a little bit faster (The cost > >>is a little bit smaller). If you compare the actual runtimes maybe you > >>will see that pg was right. In this case the cost is almost the same so > >>the runtime is probably almost the same. > >> > >>When you have more data pg will start to use the index since then it will > >>be faster to use an index compared to a seq. scan. > >> > >>-- > >>/Dennis Björklund > >> > >> > > > > > > > > > > > > ___ > > Do you Yahoo!? > > Win 1 of 4,000 free domain names from Yahoo! Enter now. > > http://promotions.yahoo.com/goldrush > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL > > begin:vcard > fn:Joshua D. Drake > n:Drake;Joshua D. > org:Command Prompt, Inc. > adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA > email;internet:[EMAIL PROTECTED] > title:Consultant > tel;work:503-667-4564 > tel;fax:503-210-0334 > note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL > support > provider. We provide the only commercially viable integrated PostgreSQL replication > solution, > but also custom programming, and support. We authored the book Practical > PostgreSQL, the > procedural language plPHP, and adding trigger capability to plPerl. > x-mozilla-html:FALSE > url:http://www.commandprompt.com/ > version:2.1 > end:vcard > > = Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL Question
Hi, If anybody can offer better SQL query please. We have table create table group_facility ( group_id integer not null, facility_id integer not null ) It stores facilities membership in group. For example: "North Region" - facilityA, facilityB I need to extract groups from this table which contain facilityN AND facilityZ and may be others but these two(both) has to be a group member. Query: SELECT DISTINCT group_id FROM facility_group s1 WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 390) AND EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 999) works but what if I need to find groups where membership is (facilityN1, facilityN100)?? Thank you, Igor K ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Postgresql FK to MS SQL triggers
Hi, If you are moving from Postgres to MS SQL you will most likely will find that you can not recreate your PostgreSQL FK to MSSQL FK because this enterprise class database will NOT allow you to create all 3 FK which are exist in your PGSQL: table users(user_id PK) table journal(created_by, modified_by, deleted_by) ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES users(user_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; ADD CONSTRAINT fk_modified_by FOREIGN KEY (modified_by) REFERENCES users(user_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; ADD CONSTRAINT fk_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(user_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; For interested people I wrote a PHP script which: 1) Extracts all underlying triggers from pg_trigger table in Postgres used to support FK (3 triggers for each FK) 2) Generates a MSSQL script file which recreates all triggers in MSSQL Hope it will save some time for somebody. Igor ---(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] Update PostgreSQL from MS SQL trigger
Hi, I have table 'test_m'(id integer) in MSSQL and I want to write on_test_m_insert trigger in MS SQL which will insert value into PostgreSQL table 'test_p' from database 'test_db' running on host '10.3.2.5'. Can this be achieved with PostgreSQL ODBC driver? If yes, please post template of such trigger. Thank you, Igor ---(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] Update PostgreSQL from MS SQL trigger
I managed to link PostgreSQL to MSSQL as a linked server but: select * from [TEST].[test].[public].[users] << wrote in message news:[EMAIL PROTECTED] > Hi, > > I have table 'test_m'(id integer) in MSSQL and I want to write > on_test_m_insert trigger in MS SQL which will insert value into PostgreSQL > table 'test_p' from database 'test_db' running on host '10.3.2.5'. > > Can this be achieved with PostgreSQL ODBC driver? If yes, please post > template of such trigger. > > > Thank you, > > > Igor > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Help to drop table
Please help to drop table with soace inside name. List of relations Schema | Name | Type | Owner +--+---+-- public | Facility Info| table | postgres > DROP TABLE ?? Thank you, Igor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help to drop table
DROP TABLE "Facility Info" Thank you, Igor "Michael Fuhr" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Fri, Apr 22, 2005 at 03:24:10PM +1000, Igor Kryltsov wrote: > > > > Please help to drop table with soace inside name. > > > > > > List of relations > > Schema | Name | Type | Owner > > +--+---+-- > > public | Facility Info| table | postgres > > > > > DROP TABLE ?? > > See "Identifiers and Key Words" in the "SQL Syntax" chapter of the > documentation, in particular the discussion of quoted identifiers. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [GENERAL] Set Membership operator -- test group membership
Sophie,
The sql like this:
select * from tbl1 where (a, b) in ((1, 20), (2, 30), (3, 50));
works very well in PostgreSQL 8,
Sincerely,
Igor Katrayev, Data Systems Manager
North Pacific Research Board
1007 West Third Avenue, Suite 100
Anchorage, AK 99501
Phone: 907-644-6700
Fax: 907-644-6780
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Sophie Yang
Sent: Tuesday, June 14, 2005 11:09 AM
To: pgsql-general@postgresql.org
Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Subject: [GENERAL] Set Membership operator -- test group membership
Say I have a table tbl1 with two columns:
tbl1(a integer, b integer, c integer)
I want to select the rows in which a and b are members of a list of
integer pairs. The SQL in my mind is something like:
select * from tbl1 where (a, b) in ((1, 20), (2, 30), (3, 50));
I know the SQL above does not work in PostgreSQL. I wonder what is the
proper way to use in PostgreSQL. I tried "select * from tbl1 where (a,
b) in ('{{1, 20}, {2, 30}, {3, 50}}')", and it doesn't work either.
Thanks!
Sophie
__
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(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] why vacuum
On Wednesday 26 October 2005 17:44, Scott Marlowe wrote: > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: > > hi, > > i was in a minor flame war with a mysql guy - his major grouse was that > > 'I wouldnt commit mission critical data to a database that needs to be > > vacuumed once a week'. So why does pg need vacuum? > > The absolutely funniest thing about what this guy is saying is that he > seems rather ignorant of the behaviour of innodb tables. They have > another name for the vacuum command there. It's: > > ALTER TABLE tbl_name ENGINE=INNODB > > Which rebuilds the whole fraggin's table, with an exclusive lock. > > and guess what innodb does if you don't run this command every so often? > > Can you guess yet? Yep, that's right, it just keeps growing and growing > and growing. Not quite so. I'm running quite a few (>50) mysql/innodb servers with database sizes raging from 500mb to 50gb, and I never had to rebuild any innodb tables this way. InnoDB uses index-based data storage and rollback segments, which makes it harder to add bloat to their databases, as compared to PG (but autovacuum is my saviour). Innodb will actually free space when you do DELETE or TRUNCATE, but still, it's tables, indexes and tablespaces will get fragmented. This gets worse over time, but it had never been a big problem for me. My databases do 50 queries/second on average, 24/7. Note - all of this can be due to my access and data change patterns; YMMV. The "only" cleanup operation I do is CHECK/OPTIMIZE, on monthly basis; it's not much better than old PG's VACUUM, as it brings mysql/innodb's performance down by 5x-10x times; same goes for almost any long-running query. I'm moving those servers to PG, due to this (concurrency) and other reasons. My top 3 reasons are: a much better concurrency (even with bg vacuums running :-), a much better planner, and PG's rich feature set. -- Best Regards, Igor Shevchenko ---(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] Use "CREATE USER" in plpgsql function
> -Original Message- > From: Tatarnikov Alexander [mailto:cank...@gmail.com] > Sent: Wednesday, September 15, 2010 12:05 AM > To: pgsql-sql@postgresql.org > Subject: Use "CREATE USER" in plpgsql function > > Hello! > > I have function wich check user credentials and if test > passed function must create new user with generated username > and password. > > Language is plpgsql. > > For example: > > > DECLARE creds RECORD; > ... > SELECT * INTO creds FROM ... > > creds is Record with fields userName(VARCHAR) and > userPassword(VARCHAR) > > so when i use CREATE USER creds."userName" WITH PASSWORD > creds."userPassword" > > i get an error, because creds."userName" is VARCHAR and thus > when function runs it will be look like this: > CREATE USER 'user_1' > but right command is > "CREATE USER user_1" OR " CREATE USER "user_1" " > > so question is how to "unembrace" this parameter (i mean > creds."userName")? > > Thanks > -- > -- > Alexander > It is called "dynamic" sql: EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' || userPassword; Read about "dynamic" sql in PG docs: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html Specifically: "38.5.4. Executing Dynamic Commands" Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
> -Original Message- > From: Asko Oja [mailto:asc...@gmail.com] > Sent: Wednesday, September 15, 2010 2:29 PM > To: Igor Neyman > Cc: Tatarnikov Alexander; pgsql-sql@postgresql.org > Subject: Re: [SQL] Use "CREATE USER" in plpgsql function - > Found word(s) list error in the Text body > > And dynamic SQL leads easily to SQL injection so quoting is > required there. > > execute 'create user ' || quote_ident(i_username) || > ' password ' || quote_literal(i_password); > > > On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman > wrote: > That's too "generic". I was answering specific question. Now, yes, dynamic sql could be used for SQL injection, if not used carefully. But, it exists for a reason. And in this particular case userName and userPassword retrieved from a table. So, care should be taken (appropriate checks to be done) when these values inserted into the table. Btw., do you have another answer to OP question? Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
From: Asko Oja [mailto:asc...@gmail.com] Sent: Wednesday, September 15, 2010 2:29 PM To: Igor Neyman Cc: Tatarnikov Alexander; pgsql-sql@postgresql.org Subject: Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body And dynamic SQL leads easily to SQL injection so quoting is required there. execute 'create user ' || quote_ident(i_username) || ' password ' || quote_literal(i_password); On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman wrote: > -Original Message- > From: Tatarnikov Alexander [mailto:cank...@gmail.com] > Sent: Wednesday, September 15, 2010 12:05 AM > To: pgsql-sql@postgresql.org > Subject: Use "CREATE USER" in plpgsql function > > Hello! > > I have function wich check user credentials and if test > passed function must create new user with generated username > and password. > > Language is plpgsql. > > For example: > > > DECLARE creds RECORD; > ... > SELECT * INTO creds FROM ... > > creds is Record with fields userName(VARCHAR) and > userPassword(VARCHAR) > > so when i use CREATE USER creds."userName" WITH PASSWORD > creds."userPassword" > > i get an error, because creds."userName" is VARCHAR and thus > when function runs it will be look like this: > CREATE USER 'user_1' > but right command is > "CREATE USER user_1" OR " CREATE USER "user_1" " > > so question is how to "unembrace" this parameter (i mean > creds."userName")? > > Thanks > -- > -- > Alexander > It is called "dynamic" sql: EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' || userPassword; Read about "dynamic" sql in PG docs: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html Specifically: "38.5.4. Executing Dynamic Commands" Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql [I.N.] Opps. Missed quote_ident() in your message, sorry.
Re: [SQL] sqlplus reporting equivalent in postgres?
-Original Message- From: Tony Capobianco [mailto:tcapobia...@prospectiv.com] Sent: Thu 12/9/2010 4:47 PM To: pgsql-sql@postgresql.org Subject: sqlplus reporting equivalent in postgres? We're in the process of porting our Oracle 10.2 datawarehouse over to PostGres 8.4. One thing we rely upon are daily/hourly reports that are run and formatted in sqlplus (break on report, compute sum, etc.). Is there an equivalent available in postgres? Thanks. Tony psql - not as advanced, doesn't have all the features SQL*Plus has. Igor Neyman
Re: [SQL] Function compile error
> -Original Message- > From: Sivannarayanreddy [mailto:sivannarayanre...@subexworld.com] > Sent: Wednesday, February 16, 2011 7:36 AM > To: pgsql-sql@postgresql.org > Subject: Function compile error > > Hello, > I am trying to create the function as below but it is > throwing error 'ERROR: syntax error at or near "DECLARE"', > Could some one help me please > > CREATE FUNCTION check_password(databasename text, tablename > text, indexname text)RETURNS VOID AS > DECLARE v_count INTEGER; > BEGIN > select count(1) into v_count from pg_index inx where > inx.indexrelid in > (select oid from pg_class where relname=$3 and relowner in > (select oid from pg_authid where rolname=$1)) > and inx.indrelid in > (select oid from pg_class where relname=$2 and relowner in > (select oid from pg_authid where rolname=$1)); > if v_count = 0 then > execute immediate 'create unique index $3 on $2 (acn_id)'; > end if; > END; > > > > Sivannarayanareddy Nusum | System Analyst(Moneta GDO) > > > > Subex Limited, Adarsh Tech Park, Outer Ring Road, > Devarabisannalli, Bangalore - 560037, India. > Phone: +91 80 6696 3371; Mobile: +91 9902065831 Fax: +91 80 > 6696 ; > > Email: sivannarayanre...@subexworld.com > <mailto:email...@subexworld.com> ; URL: www.subexworld.com > <http://www.subexworld.com/> > > > > Disclaimer: This e-mail is bound by the terms and conditions > described at http://www.subexworld.com/mail-disclaimer.html > <http://www.subexworld.com/mail-disclaimer.html> > CREATE FUNCTION check_password(databasename text, tablename text, indexname text) RETURNS VOID AS $body$ DECLARE v_count INTEGER; BEGIN select count(1) into v_count from pg_index inx where inx.indexrelid in (select oid from pg_class where relname=$3 and relowner in (select oid from pg_authid where rolname=$1)) and inx.indrelid in (select oid from pg_class where relname=$2 and relowner in (select oid from pg_authid where rolname=$1)); if v_count = 0 then execute immediate 'create unique index $3 on $2 (acn_id)'; end if; END; $body$LANGUAGE PLPGSQL; Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] After insert trigger and select
> -Original Message- > From: Rok Jaklič [mailto:r...@rasca.net] > Sent: Wednesday, February 16, 2011 5:35 PM > To: pasman pasmański; pgsql-sql > Subject: Re: After insert trigger and select > > On 02/16/2011 08:46 PM, pasman pasmański wrote: > >> If I have after insert trigger on some table which updates > some data > >> in that same table, will be the select statement from some other > >> client executed after all statements in that trigger? > >> > > select statement is fired before commit ? > > > > pasman > For example let us say that trigger takes a long time to end. > Are all statements in trigger executed before select from > "outside" if select is called somewhere between executing of > the trigger? > With MVCC "writers" don't block "readers", and "readers" don't block "writers". Read PG docs on MVCC. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] to_char() accepting invalid dates?
-Original Message-
From: Bruce Momjian [mailto:br...@momjian.us]
Sent: Monday, July 18, 2011 6:02 PM
To: Thomas Kellerer
Cc: pgsql-sql@postgresql.org
Subject: Re: to_char() accepting invalid dates?
Thomas Kellerer wrote:
> Jasen Betts wrote on 18.07.2011 11:23:
> >> postgres=> select to_date('20110231', 'mmdd');
> >>
> >> to_date
> >>
> >>2011-03-03
> >> (1 row)
> >>
> >> is there a way to have to_date() raise an exception in such a case?
> >
> > it's possible the odd behaviour you get is required by some
standard.
>
> That would be *very* odd indeed.
>
>
> > jasen=# select '20110303'::date;
> Thanks for the tip, this was more a question regarding _why_ to_char()
behaves this way.
> Well, to_char() is based on Oracle's to_char(). How does Oracle
handle
> such a date?
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
> + It's impossible for everything to be true. +
This (from Oracle) makes me think, that it's implemented differently:
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select to_date('20110231', 'mmdd') from dual;
select to_date('20110231', 'mmdd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL>
Regards,
Igor Neyman
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] which is better: using OR clauses or UNION?
=62) (actual > time=2.660..2.834 rows=318 loops=1) > Sort Key: bioassay_id, identifier, ratio, log_ratio, > p_value > -> Append (cost=32.88..15185.06 rows=3856 width=62) > (actual time=0.320..2.131 rows=318 loops=1) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.041..0.041 > rows=0 loops=1) > Filter: (lower(identifier) ~~ > 'bugs001884677'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.036..0.036 rows=0 loops=1) > Index Cond: (lower(identifier) ~=~ > 'bugs001884677'::character varying) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.010..0.010 > rows=0 loops=1) > Filter: (lower(identifier) ~~ > 'bugs001884678'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.008..0.008 rows=0 loops=1) > Index Cond: (lower(identifier) ~=~ > 'bugs001884678'::character varying) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.010..0.010 > rows=0 loops=1) > Filter: (lower(identifier) ~~ > 'bugs001884679'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.008..0.008 rows=0 loops=1) > Index Cond: (lower(identifier) ~=~ > 'bugs001884679'::character varying) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.255..1.676 > rows=318 loops=1) > Filter: (lower(identifier) ~~ 'sptigr4-2210 > (6f24)'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.178..0.178 rows=318 loops=1) > Index Cond: (lower(identifier) ~=~ > 'sptigr4-2210 (6f24)'::character varying) > Total runtime: 4.174 ms > > Also which should scale better if I add more strings to match? would > there be any better design patterns for this problem? > > Thanks for any help > > Adam > > select version(); >version > > PostgreSQL 8.2.12 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > Adam, There is something strange in your 2 execution plans. Exactly the same operation: -- first plan Bitmap Index Scan on in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347 rows=318 loops=1) Index Cond: (lower(identifier) ~=~ 'sptigr4-2210 (6f24)'::character varying) -- second plan Bitmap Index Scan on in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual time=0.178..0.178 rows=318 loops=1) Index Cond: (lower(identifier) ~=~ 'sptigr4-2210 (6f24)'::character varying) takes quite different time: 71.347 versus 0.178 and basically makes all the difference between duration of your first and second statement. I think, what you are seeing here is data being cached in memory (when you executed "union" statement after "or" statement). Other than that, looking at 2 execution plans, I'd say that in general "or" should run faster than "union", at least because it does "Bitmap Heap Scan on dba_data_base" only once, while "union" statement does this heap scan 4 times (once per "unionized" select). HTH, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] which is better: using OR clauses or UNION?
> -Original Message-
> From: adam_pgsql [mailto:adam_pg...@witneyweb.org]
> Sent: Tuesday, August 16, 2011 11:56 AM
> To: Tom Lane
> Cc: pgsql-sql
> Subject: Re: which is better: using OR clauses or UNION?
>
>
> On 16 Aug 2011, at 15:09, Tom Lane wrote:
>
> > adam_pgsql writes:
> >> I have a query hitting a table of 25 million rows. The table has a
> >> text field ('identifier') which i need to query for matching rows.
> The
> >> question is if i have multiple strings to match against this field
I
> >> can use multiple OR sub-statements or multiple statements in a
> >> UNION. The UNION seems to run quicker is this to be expected?
> >
> > Your test cases don't seem exactly comparable; in particular I think
> the
> > second one is benefiting from the first one having already read and
> > cached the relevant disk blocks. Notice how you've got, eg,
> >
> >> -> Bitmap Index Scan on in_dba_data_base_identifier
> (cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347
> rows=318 loops=1)
> >>Index Cond: (lower(identifier) ~=~ 'sptigr4-2210
> (6f24)'::character varying)
> >
> > versus
> >
> >> -> Bitmap Index Scan on
> in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0)
> (actual time=0.178..0.178 rows=318 loops=1)
> >>Index Cond: (lower(identifier) ~=~
> 'sptigr4-2210 (6f24)'::character varying)
> >
> > Those are the exact same subplan, so any honest comparison should be
> > finding them to take the same amount of time. When the actual
> readings
> > are different by a factor of several hundred, there's something
wrong
> > with your measurement process.
> >
> > In the end this comes down to whether duplicates will be eliminated
> more
> > efficiently by a BitmapOr step or by sort/uniq on the resulting
rows.
> > I'd have to bet on the BitmapOr myself, but it's likely that this is
> > down in the noise compared to the actual disk accesses in any
> > not-fully-cached scenario. Also, if you don't expect the sub-
> statements
> > to yield any duplicates, or don't care about seeing the same row
> twice
> > in the output, you should consider UNION ALL instead of UNION.
>
>
> Thanks guys, I'll give some of those options a try and see which ones
> improve performance
>
> (Tom, yes i ran those queries after each other so there was caching
> going on. However, I had noticed a difference in performance when
> spacing the queries before and after a few other big queries to help
> clear the cache).
>
> adam
Adam,
Did you verify that your cache is "cleared"? Like using pg_buffercache
contrib. module?
Besides, there is also OS cache...
Regards,
Igor Neyman
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concurrent Reindex on Primary Key for large table
> -Original Message- > From: rverghese [mailto:ri...@hotmail.com] > Sent: Thursday, February 09, 2012 12:49 PM > To: pgsql-sql@postgresql.org > Subject: Re: Concurrent Reindex on Primary Key for large table > > Thanks! That worked. > > Any thoughts about containing index bloat. I thought the autovac would > clean it up a bit more. Would any tweaks to my settings improve autovac > performance? I am still doing a couple of concurrent reindexes per week > otherwise performance degrades over a couple of days. > > Thanks > RV > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Concurrent-Reindex-on-Primary- > Key-for-large-table-tp5467243p5470216.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. Did you try to make autovacuum "more aggressive" like lowering autovacuum_vacuum_threshold from 2? Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL View to PostgreSQL View
From: Rehan Saleem [mailto:pk_re...@yahoo.com]
Sent: Sunday, February 26, 2012 1:50 PM
To: pgsql-sql@postgresql.org
Subject: SQL View to PostgreSQL View
Hi ,
I am trying to convert sql view to postgresql view but i am getting the
following error i dont know how to handle dbo. in postgresql and when i remove
dbo. from table name then view got created but it does not show any data, while
this is working perfectly fine in sql, here is my code and error details
CREATE OR REPLACE VIEW vwkbcomparesites as
select a.kbid kb_a, b.kbid kb_b, a.chr chr_a, a.start start_a, a."end" end_a,
(a."end" - a.start)+1 tagsize_a,
b.chr chr_b, b.start start_b, b."end" end_b, (b."end" - b.start)+1
tagsize_b,
abs((a."end" + a.start)/2 - (b."end" + b.start)/2) centredistance,
case
when a."end" <= b."end" and a.start >= b.start
then (a."end" - a.start)
when b."end" <= a."end" and b.start >= a.start
then (b."end" - b.start)
when a."end" <= b."end" and a.start <= b.start
then (a."end" - b.start)
when a."end" >= b."end" and a.start >= b.start
then (b."end" - a.start)
end bpoverlap
from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr
inner join dbo.kbdetails kbd on a.kbid=kbd.kbid
where kbd.active='1' ;
i am getting this error , how can i fix this.
ERROR: schema "dbo" does not exist
LINE 15: from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr
^
** Error **
ERROR: schema "dbo" does not exist
SQL state: 3F000
Character: 761
>>
First, I assume you are converting your view from SQL Server, not from SQL.
SQL Server is RDBMS, while SQL is a language being used by multiple RDBMSs
including PostgreSQL.
Second, there is no "standard" dbo ("database owner") role in Postgres.
Before converting from one RDBMS to another you need to do some basic (at
least) documentation reading on "target" RDBMS (in this case - PostgreSQL).
Otherwise, you will stumble on every step.
Regards,
Igor Neyman
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] map row in one table with random row in another table
> -Original Message- > From: rverghese [mailto:ri...@hotmail.com] > Sent: Tuesday, March 06, 2012 4:01 PM > To: pgsql-sql@postgresql.org > Subject: map row in one table with random row in another table > > Hi, I am trying to map every row in one table with a random row in > another. > So for e.g. , for each network in 1 table I am trying to map random > segments from the other table. I have this sql below, but it always > applies the same random segment that it picks to all the rows for the > network. I want each row to have a random segment value. I'm just using > the generate_series function to generate id's as an e.g. > Any suggestions? > > My Query > select id, seg_list from (select generate_series(1,10) as id) as X, > (select segment from segments order by random() limit 1 ) as Y > > I get > > 1;'cob0002' > 2;'cob0002' > 3;'cob0002' > 4;'cob0002' > 5;'cob0002' > 6;'cob0002' > 7;'cob0002' > 8;'cob0002' > 9;'cob0002' > 10;'cob0002' > > What I want is > > 1;'cob0002' > 2;'cob0008' > 3;'cob0006' > 4;'cob0004' > 5;'cob0002' > 6;'cob0007' > 7;'cob0003' > 8;'cob0004' > 9;'cob0009' > 10;'cob0001' > Try this: Select distinct on (id) id, segment From (select generate_series(1,10) as id) as X, (select segment from segments) as Y Order by id, random(); Depending on the size of your tables, performance could become an issue. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using ordinal_position
> -Original Message- > From: John Fabiani [mailto:jo...@jfcomputer.com] > Sent: Thursday, June 07, 2012 7:18 PM > To: pgsql-sql@postgresql.org > Subject: using ordinal_position > > I'm attempting to retrieve data using a select statement without > knowing the column names. I know the ordinal position but not the name > of the column (happens to be a date::text and I have 13 fields). > > Below provides the name of the column in position 3: > > select column_name from (select column_name::text, ordinal_position > from information_schema.columns where > table_name='wk_test') as foo where ordinal_position = 3; > > But how can I use the above as a column name in a normal select > statement. > > Unlike other databases I just can't use ordinal position in the select > statement - RIGHT??? > > Johnf David gave you already pretty complete answer. I just wonder what are these "other" RDBMSs that allow to use ordinal column positions in a query? I am familiar with a few (in addition to Postgress), and none of them does that, not in "select" list., though everybody allow ordinal position from "select" list in "order by" and "group by". Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using ordinal_position
> -Original Message- > From: John Fabiani [mailto:jo...@jfcomputer.com] > Sent: Monday, June 11, 2012 11:11 AM > To: Igor Neyman > Cc: pgsql-sql@postgresql.org > Subject: Re: using ordinal_position > > On 06/11/2012 06:53 AM, Igor Neyman wrote: > > > >> -Original Message- > >> From: John Fabiani [mailto:jo...@jfcomputer.com] > >> Sent: Thursday, June 07, 2012 7:18 PM > >> To: pgsql-sql@postgresql.org > >> Subject: using ordinal_position > >> > >> I'm attempting to retrieve data using a select statement without > >> knowing the column names. I know the ordinal position but not the > >> name of the column (happens to be a date::text and I have 13 > fields). > >> > >> Below provides the name of the column in position 3: > >> > >> select column_name from (select column_name::text, ordinal_position > >> from information_schema.columns where > >> table_name='wk_test') as foo where ordinal_position = 3; > >> > >> But how can I use the above as a column name in a normal select > >> statement. > >> > >> Unlike other databases I just can't use ordinal position in the > >> select statement - RIGHT??? > >> > >> Johnf > > David gave you already pretty complete answer. > > I just wonder what are these "other" RDBMSs that allow to use ordinal > column positions in a query? > > I am familiar with a few (in addition to Postgress), and none of them > does that, not in "select" list., though everybody allow ordinal > position from "select" list in "order by" and "group by". > > > > Regards, > > Igor Neyman > > > > > > > VFP uses position (you might not consider DBF a database). MsSQl > (ordinal_position). Those are the only two I'm aware of. > > Johnf Did you mean MySQL or MS SQL? Because MS SQL Server does not allow ordinal position AFAIK. Igor -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to compare two tables in PostgreSQL
From: saikiran mothe [mailto:saikiran.mo...@gmail.com] Sent: Saturday, November 10, 2012 10:14 PM To: pgsql-sql@postgresql.org Subject: How to compare two tables in PostgreSQL Hi, How can i compare two tables in PostgreSQL. Thanks, Sai Here is simple sql to show data in table1, but not in table2: SELECT from table1 EXCEPT SELECT from table2; And this sql shows data in table2 but not in table1: SELECT from table2 EXCEPT SELECT from table1; Or, you could combine them in one statement, adding "indicator" column: SELECT , 'not in table2' as indicator from table1 EXCEPT SELECT , 'not in table2' as indicator from table2 UNION SELECT , 'not in table1' as indicator from table2 EXCEPT SELECT , 'not in table1' as indicator from table1; Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using Ltree For Hierarchical Structures
From: Don Parris [mailto:parri...@gmail.com] Sent: Sunday, February 24, 2013 5:21 PM To: pgsql-sql@postgresql.org Subject: Using Ltree For Hierarchical Structures Hi all, With many thanks to Misa and others who helped out with my question about working with hierarchical data, I have now written a blog post on how I implemented the ltree module to solve my problem. http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/ Frankly, if you work with hierarchical data, I'm not sure I could recommend it strongly enough. I should think that even experienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly table designs and recursive queries in order to work with hierarchical structures. I really hope this blog post will help others in the same boat. Regards, Don It's all "in the eyes of beholder". IMHO, recursive CTEs are perfect for hierarchical structures, and much cleaner than 2-table design using ltree, that you show in the blog. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using Ltree For Hierarchical Structures
From: Don Parris [mailto:parri...@gmail.com] Sent: Tuesday, February 26, 2013 1:23 PM To: Misa Simic Cc: Igor Neyman; pgsql-sql@postgresql.org Subject: Re: [SQL] Using Ltree For Hierarchical Structures Hi Igor, As Misa points out, my original design used 2 tables - category & line-items. Either way it's two tables. The biggest difference lies in the approach to summing line-items by various levels of category - with each branch of the tree having different levels. I cannot speak to performance, but understanding CTEs has been difficult for me. Ltree is so much simpler. I am certain there is a place for CTEs, but why torture myself trying to hash out a CTE when ltree makes the queries so much easier to write? Don, To answer this question... Before I worked (mostly) with Oracle which has "connect by" construct to work with hierarchies. So, when I switched to Postgres I was happy to find "connect_by" contrib. modul. And with more recent PG versions, it was just natural transition from contrib. module to recursive CTEs. Igor -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using Ltree For Hierarchical Structures
From: Don Parris [mailto:parri...@gmail.com] Sent: Tuesday, February 26, 2013 4:55 PM To: pgsql-sql@postgresql.org Subject: Re: Using Ltree For Hierarchical Structures ... I am unaware of the connect_by module, so will have to look into it. ... Cheers! Don For that look into tablefunc Extension. It also has other useful functions, such as crosstab. Regards, Igor Neyman
Re: [SQL] Correct implementation of 1:n relationship with n>0?
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Wolfgang Keller > Sent: Tuesday, April 30, 2013 2:19 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Correct implementation of 1:n relationship with n>0? > > > It hit me today that a 1:n relationship can't be implemented just by > a > > single foreign key constraint if n>0. I must have been sleeping very > > deeply not to notice this. > > > > E.g. if there is a table "list" and another table "list_item" and the > > relationship can be described as "every list has at least one > > list_item" (and every list_item can only be part of one list, but > this > > is trivial). > > > > BTW: If every list_item could be part of any number (>0) of lists, you > get a n:m relationship with a join table and then the issue that each > list_item has to belong to at least one list arises as well. > > Maybe there should also be a standard solution documented somewhere for > this case, too. > > > > Sincerely, > > Wolfgang > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Wolfgang, If it is n:m relationship, than appearance of the LIST in list_item table : (list_id int not null, item_id int not null, constraint PK_list_item Primary Key (list_id, item_id), constraint FK_ItemList_List Foreign Key (list_id) references List (list_id) on delete cascaded on update restrict, constraint FK_ItemList_Item Foreign Key (item_id) references Item (item_id) on delete cascaded on update restrict) means that this LIST has at least one ITEM assigned to it. Same goes for the ITEM: if it is assigned to at least one List it should appear in this "cross table". It is application responsibility to populate this table, when Items assigned to Lists. It is database responsibility (through declarative foreign keys) to make sure that Lists and Items used in "cross table" have corresponding records in "parent" tables. Using triggers (which is SQL extension implemented differently in every DBMS) database also can support such feature, as: "when last Item removed from the List - drop now "empty" List. Which I don't consider a good idea - what if you'll need this list in the future? Why re-create it? As for your original problem with 1:n relationship, n should be starting from 0 for the case when new List is created and there is no Items to assign to this new List, yet. In this case, FK on Items table referencing List table makes sure that every Item references existing (valid) List. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] array_agg() with join question
On Tue, May 14, 2013 at 10:08 AM, George Woodring <
george.woodr...@iglass.net> wrote:
> To summarize my question at the top, why is it that when I did the JOIN,
> the array_agg results reversed order?
>
> I had a function that ran the following query:
>
> SELECT timeslot, pollgrpid, array_agg(outval)
>FROM
> (SELECT * FROM rrd.a_current WHERE pollgrpid=8 ORDER BY timeslot,
> dsnum) AS foo
>WHERE timeslot >= now() - '1 hour'::interval AND timeslot <= now() + '1
> hour'::interval
>GROUP BY timeslot, pollgrpid
>ORDER BY timeslot;
>
> timeslot| pollgrpid | array_agg
> +---+
> 2013-05-14 08:58:00-04 | 8 | {1,1,0.00125217437744141}
> 2013-05-14 09:00:00-04 | 8 | {1,1,0.00027520751953}
> 2013-05-14 09:02:00-04 | 8 | {1,1,0.00318312644958496}
> 2013-05-14 09:04:00-04 | 8 | {1,1,0.000761985778808594}
> 2013-05-14 09:06:00-04 | 8 | {1,1,0.000777959823608398}
> 2013-05-14 09:08:00-04 | 8 | {1,1,0.101096868515015}
> 2013-05-14 09:10:00-04 | 8 | {1,1,0.86168384552002}
> 2013-05-14 09:12:00-04 | 8 | {1,1,0.00656795501708984}
> 2013-05-14 09:14:00-04 | 8 | {1,1,0.102259159088135}
> 2013-05-14 09:16:00-04 | 8 | {1,1,0.000636100769042969}
>
> I wanted to include missing timestamps in my results, so I joined it with
> generate_series.
>
> SELECT timeslot, pollgrpid, array_agg(outval)
>FROM
> ( SELECT generate_series(rrd_timeslot('avail', now() - '58
> minutes'::interval), now() + '1 hour'::interval, '2 minutes') AS timeslot )
> AS bar
>LEFT JOIN
> (SELECT * FROM rrd.a_current WHERE pollgrpid=8 AND timeslot >= now()
> - '1 hour'::interval AND timeslot <= now() + '1 hour'::interval ORDER BY
> timeslot, dsnum) AS foo
>USING(timeslot)
>GROUP BY timeslot, pollgrpid
>ORDER BY timeslot;
>
> timeslot| pollgrpid | array_agg
> +---+
> 2013-05-14 09:02:00-04 | 8 | {0.00318312644958496,1,1}
> 2013-05-14 09:04:00-04 | 8 | {0.000761985778808594,1,1}
> 2013-05-14 09:06:00-04 | 8 | {0.000777959823608398,1,1}
> 2013-05-14 09:08:00-04 | 8 | {0.101096868515015,1,1}
> 2013-05-14 09:10:00-04 | 8 | {0.86168384552002,1,1}
> 2013-05-14 09:12:00-04 | 8 | {0.00656795501708984,1,1}
> 2013-05-14 09:14:00-04 | 8 | {0.102259159088135,1,1}
> 2013-05-14 09:16:00-04 | 8 | {0.000636100769042969,1,1}
> 2013-05-14 09:18:00-04 | 8 | {0.000638008117675781,1,1}
> 2013-05-14 09:20:00-04 | 8 | {0.174574136734009,1,1}
> 2013-05-14 09:22:00-04 | 8 | {0.1006920337677,1,1}
> 2013-05-14 09:24:00-04 | 8 | {0.00069117546081543,1,1}
> 2013-05-14 09:26:00-04 | 8 | {0.114289045333862,1,1}
> 2013-05-14 09:28:00-04 | 8 | {0.116230010986328,1,1}
> 2013-05-14 09:30:00-04 | 8 | {0.0349528789520264,1,1}
>
> The array_agg results are reversed. I had to ODER BY timeslot, dsnum desc
> on the right of the join to make it match. I am curious as to why this
> happened. I am running 9.2.4.
>
> Thanks,
> Woody
>
> iGLASS Networks
> www.iglass.net
>
As always (with databases) order is not guaranteed unless you specify
"ORDER BY ...".
So, specify whatever order you want inside aggregate function:
array_agg(outval order by column1)
Check the docs:
http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-AGGREGATES
Igor Neyman
Re: [SQL] delete where not in another table
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Campbell, Lance Sent: Tuesday, July 09, 2013 3:25 PM To: pgsql-sql@postgresql.org Subject: [SQL] delete where not in another table DELETE FROM T1 WHERE T1.user_id NOT IN (SELECT user_id FROM T2 WHERE T2.user_id=T1.user_id); Thanks, Lance Campbell Software Architect Web Services at Public Affairs 217-333-0382 -- Try: DELETE FROM t1 USING t2 WHERE t1.user_id != t2.user_id; Test it before running on production db. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update column based on postgis query on anther table
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of ssylla > Sent: Tuesday, July 16, 2013 3:58 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] update column based on postgis query on anther table > > Hi Tom, > > I tried changing the trigger to be BEFORE instead of AFTER: > > create trigger test1_point_get_id_test1_poly > before insert or update on test1_point for each row execute procedure > test1_point_get_id_test1_poly(); > > But the problem persits, the column id_test1_poly remains empty. > > Stefan > > Stefan, Probably, you need to change something in your trigger logic, because like Tom stated it's too late to change NEW values in AFTER record, and in BEFORE trigger the record with NEW value doesn't exist yet, so: select test1_poly.id from test1_poly,test1_point where ST_Within( test1_point.the_geom, test1_poly.the_geom) and test1_point.id=$1; with $1 being NEW.id returns NULL (record with test1_point.id = NEW.id isn't written yet into test1_point table). May be this trigger function is what you need: create or replace function test1_point_get_id_test1_poly() returns trigger as $$ begin select test1_poly.id INTO new.id_test1_poly from test1_poly where ST_Within( NEW.the_geom, test1_poly.the_geom); return new; end; $$ language plpgsql volatile; Still there is an issue. What if your point falls within multiple polygons (multiple records in test1_poly satisfy your criteria)? In this case, select from test1_poly should return multiple records. This will break trigger function code. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] A table with two names or table alias
There are no synonyms in Postgres (like in Oracle). To implement what you want, you need to use views and rules. From Postgres docs: "Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables." Read docs on views and rules. HTH, Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tk421 Sent: Friday, July 25, 2008 8:13 AM To: pgsql-sql@postgresql.org Subject: [SQL] A table with two names or table alias Can I create a table name alias? What i want is to access the same table with two different names. An example: I've the table CITY with fields code and name. I want to know if i can create an alternative name to table CITY, like LOCATION, to make possible these two sentences: insert into CITY values (10, 'New York'); insert into LOCATION values (11, 'Paris') Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column names with - and (
Maria,
Try "" (double quotes:
select x1 as "IL-a", x2 as "IL-a(p30)" from abc
should help.
Igor
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of maria s
Sent: Tuesday, July 29, 2008 12:07 PM
To: Osvaldo Rosario Kussama; pgsql-sql@postgresql.org; Pavel Stehule;
Ivan Sergio Borgonovo
Subject: [SQL] column names with - and (
Hi All,
When I am fetching the data from a table,
I am giving the column names with - and ( and ) like IL-a, IL-a(p30)
etc..
select x1 as IL-a, x2 as IL-a(p30) from abc
But I am getting
ERROR: syntax error at or near "-" and also t "(" , ")"
Can anyone help me to fix this?
Thank you,
Maria
Re: [SQL] Aggregates in WHERE clause?
select employee,count(distinct tasks), greatest(max(last_job_date),max(last_position_date)) from job group by employee having greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1'; From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ruben Gouveia Sent: Wednesday, September 10, 2008 7:11 PM To: pgsql-sql Subject: [SQL] Aggregates in WHERE clause? I tried to do the following and got the following error message: select employee,count(distinct tasks) from job where greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1' group by employee; ERROR: aggregates not allowed in WHERE clause ** Error ** ERROR: aggregates not allowed in WHERE clause SQL state: 42803 Is there away around this? Should i create a function to populate a variable that can be used in it's place...will that even work?
Re: [SQL] prepared query plan did not update
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Wednesday, September 17, 2008 2:55 PM To: pgsql-sql@postgresql.org Subject: [SQL] prepared query plan did not update Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? Thanks alot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql WHERE col1 AND col2 = '$1' ; Are you looking for both: col1 and col2 - to be equal to '$1'? Then it should be: WHERE col1 = '$1' AND col2 = '$1'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] row not deleted but updated (trigger?)
"delete" trigger should return "old".
In your code you return "new" for both: "update" and "delete"
Igor
-Original Message-
From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Ivan Sergio
Borgonovo
Sent: Friday, February 27, 2009 6:56 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] row not deleted but updated (trigger?)
I've:
create or replace function FT1IDX_catalog_brands_update() returns
trigger as $$ begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
Authors, '')
where BrandID=old.BrandID;
else
if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
Name, Authors, new.Name)
where BrandID=new.BrandID;
end if;
end if;
return new;
end $$ language plpgsql volatile;
create trigger FT1IDX_catalog_brands_update_trigger before update or
delete on catalog_brands for each row execute procedure
FT1IDX_catalog_brands_update();
I do something
update catalog_brands set name='zzz' where brandid=1234;
1 row get updated.
When I do
delete from catalog_brands where brandid=1234; no row get deleted and no
error get reported.
what did I miss?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
[EMAIL PROTECTED] wrote: > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > on a RH6.2 install. > > I have a few questions on it and the use of the -E flag. > > 1 - can 7.0.2 be optimized for i686 architecture or is > it only possible to compile for i386 architecture? Yes. Changed C compilation flags in template file for your operating system. > > Max Pyziur BRAMA - Gateway Ukraine > [EMAIL PROTECTED] http://www.brama.com/
Re: [SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
Max Pyziur wrote: > On Thu, 31 Aug 2000, Igor N. Avtaev wrote: > > > [EMAIL PROTECTED] wrote: > > > > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > > > on a RH6.2 install. > > > > > > I have a few questions on it and the use of the -E flag. > > > > > > 1 - can 7.0.2 be optimized for i686 architecture or is > > > it only possible to compile for i386 architecture? > > > > Yes. Changed C compilation flags in template file for your operating > > system. > > Could you please specify which flags are necessary? For my OS - SCO UnixWare 2.1.3 (with SCO SDK): -K pentium or -K p6 > Thanks! > > > > > > > > Max Pyziur BRAMA - Gateway Ukraine > > > [EMAIL PROTECTED] http://www.brama.com/ > [recycle] > Best regards, Igor
[SQL] Help in stored procedure
Hi Guys, I need some help on building the following stored procedure, in PL/PgSQL. If this is not the right place to ask for help in this language, please let me know. Here is what I want to do, my comments in red: CREATE OR REPLACE FUNCTION discover_nsu(integer) RETURNS integer as 'DECLARE nsureturn integer; nsumax integer; caixaunitid alias for $1; branchid integer;BEGIN branchid := select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = caixaunitid); -- the select above will return to me a result of one row and one column, with a integer variable inside, and will assign its result to branchid. nsumax := select max(nsu) from caixa.view_transacao_agencia_nsu where branch = branchid; -- here i'll use the var I discovered in the last select (branchid) and will do another select in a view (this view was previously created and works fine), and store the result of the query inside nsumax var. IF (nsumax <= 0) OR (nsumax ISNULL) THEN nsureturn:=0; ELSE nsureturn:=nsumax + 1; END IF; RETURN nsureturn; -- in the if-then-else above, i was just doing a simple test. If nsumax is equal or lower than 0, or nsumax is NULL, it'll assign 0 to the return var. Else, it'll get the max, add one, and assign the value to the return var, and finally, return it =) END' LANGUAGE 'plpgsql'; Okey, the function gets created fine b/c there are no sintax erros, the problem is when i try to execute: database=> select discover_nsu(1);ERROR: syntax error at or near "select" at character 9QUERY: SELECT select t1.branch as result from caixa.caixaunit as t1, caixa.caixa as t2 where t2.caixaunit = (select caixaunit from caixa.caixaunit where t2.id = $1 )CONTEXT: PL/pgSQL function "descobrir_nsu" line 7 at assignmentLINE 1: SELECT select t1.branch as result from caixa.caixaunit as t... ^ Well, the thing is: when I execute all the selects inside the stored procedure manually, they'll work, proving that there are no errors on the selects statements itself. I believe that the database cannot understand the type of the result, assuming that it's a row instead of a single record(??). I was looking at the PL/PgSQL reference manual and wasn't able to figure out a solution, so here I am .. can aonyone help me? Which type should I use to receive the return from the query? Are cast operations (for type conversions) supported in PL/PgSQL? Thanks for all, please help! Regards, Igor--[EMAIL PROTECTED]
[SQL] System´s database table
I´m looking for the name of the table that contains all databases in my system. I already see this in the postgre manual, but i´m forgot where Tanks ... Pedro Igor
[SQL] PostgreSQL + SSL
I´m trying to config PG with SSL, but i got a error. I create the key and the certificate and put both in $PGDATA directory. I also enabled the ssl option in postgresql.conf. But when i run postmaster i got a error saying that server.key has wrong permissions. Thanks, Pedro Igor
[SQL] Cross-database references
Title: AIP - Assessoria Informática e Proteção LTDA Someone knows how is going the implementation of cross database references in pgsql ? Would have some future release with this great functionality ? Thanks, Pedro Igor
