[SQL] How to union table without union statement?
Hi, I need to combine 10 tables which contain same table structure and join an "other table" to show the latest 200 record, I am join the "other table" first and using union statement to select all record now but the collection time is super slow, how can I improve the collection speed? Thanks. -- Jr. P calendarw
[SQL] Conditional NOT NULL constraint
Hi all! Is there a simple way to add a "NOT NULL constraint" to a column without using a trigger if another column is not null? Something like this: CREATE TABLE activity( id SERIAL primary key, name varchar not null, created timestamp not null default now(), modified timestamp, created_by integer not null, modified_by integer ); alter table activity alter column modified_by set not null where modified is not null; I want a constraint which says: "modified_by not null if modified is not null". I know I could use a trigger for this, but I'm curious if there is a way to do this with an index. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to union table without union statement?
calendarw wrote: Hi, I need to combine 10 tables which contain same table structure and join an "other table" to show the latest 200 record, I am join the "other table" first and using union statement to select all record now but the collection time is super slow, how can I improve the collection speed? Start by providing the information needed to diagnose the problem. Post the output of EXPLAIN ANALYSE along with the query SQL and any table definitions/sizes you think are useful. -- Richard Huxton Archonet Ltd ---(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
Re: [SQL] Conditional NOT NULL constraint
Andreas Joseph Krogh wrote: Hi all! Is there a simple way to add a "NOT NULL constraint" to a column without using a trigger if another column is not null? Something like this: CREATE TABLE activity( id SERIAL primary key, name varchar not null, created timestamp not null default now(), modified timestamp, created_by integer not null, modified_by integer ); alter table activity alter column modified_by set not null where modified is not null; I want a constraint which says: "modified_by not null if modified is not null". Would a CHECK do? Something like: ALTER TABLE activity ADD CONSTRAINT both_modified_set CHECK ((modified_by IS NULL AND modified IS NULL) OR (modified_by IS NOT NULL AND modified IS NOT NULL)) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Conditional NOT NULL constraint
On Wednesday 28 February 2007, Richard Huxton wrote: > Andreas Joseph Krogh wrote: > > Hi all! > > Is there a simple way to add a "NOT NULL constraint" to a column without > > using a trigger if another column is not null? > > Something like this: > > > > CREATE TABLE activity( > > id SERIAL primary key, > > name varchar not null, > > created timestamp not null default now(), > > modified timestamp, > > created_by integer not null, > > modified_by integer > > ); > > > > alter table activity alter column modified_by set not null where modified > > is not null; > > > > I want a constraint which says: "modified_by not null if modified is not > > null". > > Would a CHECK do? Something like: > > ALTER TABLE activity ADD CONSTRAINT both_modified_set CHECK > ((modified_by IS NULL AND modified IS NULL) OR (modified_by IS NOT NULL > AND modified IS NOT NULL)) Certainly, thanks. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Mobile: +47 909 56 963 | | +-+ ---(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] Case with Char(1)
Hi list, it is possible to use case with character (1) ? I am having problems to formate the SQL statement. I have: SELECT * FROM test; a --- A B C SELECT a, CASE WHEN a='A' THEN 'one' WHEN a='B' THEN 'two' ELSE 'other' END FROM test; a | case ---+--- A | one B | two C | other I know from all program languages that case do not apply to noun sequencialiable (if this word exists) variable (like integers etc). Any help would be greatfull. Thanks in advance Ezequias ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Case with Char(1)
On Wed, Feb 28, 2007 at 10:02:38AM -0300, Ezequias Rodrigues da Rocha wrote: > Hi list, > > it is possible to use case with character (1) ? > > I am having problems to formate the SQL statement. Your example looked like it worked. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(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
Re: [SQL] Case with Char(1)
Am Mittwoch, 28. Februar 2007 14:02 schrieb Ezequias Rodrigues da Rocha: > it is possible to use case with character (1) ? Have you tried it? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Case with Char(1)
Now it is working. Thank you for your interest. Ezequias 2007/2/28, Andrew Sullivan <[EMAIL PROTECTED]>: On Wed, Feb 28, 2007 at 10:02:38AM -0300, Ezequias Rodrigues da Rocha wrote: > Hi list, > > it is possible to use case with character (1) ? > > I am having problems to formate the SQL statement. Your example looked like it worked. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(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 -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] system tables inquiry & db Link inquiry
Hi, We are using Postgres 8.1.0 Question No 1: = There are lots of system tables that are available in postgres. For example pg_tables will have all the information about the tables that are present in a given schema. pg_views will have all the information about the views for the given schema. I want to find all the sequences. What is the system tables that have the information about all the sequences? Question No 2: = I have 2 postgres instance located in two different servers. I want to create a DBlink (like in Oracle) between these 2. What are the steps involved to create this. Any examples? Please advise. Regards skarthi _ Win a Zunemake MSN® your homepage for your chance to win! http://homepage.msn.com/zune?icid=hmetagline ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] system tables inquiry & db Link inquir
I want to find all the sequences. What is the system tables that have the information about all the sequences? psql -E -U \ds Capture the query that psql sends to the server. Can't help with the dblink -- sorry. -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(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
Re: [SQL] system tables inquiry & db Link inquiry
On Wed, 2007-02-28 at 12:19, Karthikeyan Sundaram wrote:
> Hi,
>
> We are using Postgres 8.1.0
Stop. Do not pass go, do not collect $200. Update your postgresql
installation now to 8.1.8. There were a lot of bugs fixed between 8.1.0
and 8.1.8.
After that...
> Question No 1:
> =
>There are lots of system tables that are available in postgres. For
> example pg_tables will have all the information about the tables that are
> present in a given schema. pg_views will have all the information about the
> views for the given schema.
>
> I want to find all the sequences. What is the system tables that have
> the information about all the sequences?
In the future, you can use this trick to find those things out:
psql -E template1
\? (command to list all the backslash commands from psql)
\ds (<- command for listing sequences from psql)
Tada, you now get the sql that psql used to make that display.
For 8.2.3 that's:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
>Question No 2:
>=
>
> I have 2 postgres instance located in two different servers. I want
> to create a DBlink (like in Oracle) between these 2. What are the steps
> involved to create this.
>
>Any examples? Please advise.
I'm pretty sure there's some examples in the contrib/dblink/doc
directory in the source file to do that. It's pretty simple, I had it
working about 5 minutes after installing dblink.
---(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] pg_dump error
Hi, I am using 8.2.1 on my dev server. When I do a pg_dump, I am getting an error message. pg_dump -U postgres podcast -t channel pg_dump: symbol lookup error: pg_dump: undefined symbol: PQescapeStringConn How can I resolved this? What may be the problem? Because of this, I am not able to dump anything. Regards skarthi _ Play Flexicon: the crossword game that feeds your brain. PLAY now for FREE. http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [ADMIN] pg_dump error
Hi Joshua, Thanks for your reply. No, I recently installed (fresh installation) from scratch. Regards skarthi From: "Joshua D. Drake" <[EMAIL PROTECTED]> To: Karthikeyan Sundaram <[EMAIL PROTECTED]> CC: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [ADMIN] pg_dump error Date: Wed, 28 Feb 2007 11:31:01 -0800 Karthikeyan Sundaram wrote: > Hi, > > I am using 8.2.1 on my dev server. > > When I do a pg_dump, I am getting an error message. > > pg_dump -U postgres podcast -t channel > > pg_dump: symbol lookup error: pg_dump: undefined symbol: PQescapeStringConn > > How can I resolved this? What may be the problem? > >Because of this, I am not able to dump anything. Sounds like you have two different versions of pg_dump on your box. Did you recently try to upgrade? Sincerely, Joshua D. Drake > > > Regards > skarthi > > _ > Play Flexicon: the crossword game that feeds your brain. PLAY now for > FREE. http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate _ Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month. Intro*Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] unsubscribe
---(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] pg_dump inquiry
Hi, I have to dump only 10 tables out of 100 tables. In the pg_dump utility given by postgres there is an option called -t followed by table name. In that option, if I give more than 1 table, it's not accepting. How can I get the dump in one stroke for all the 10 tables? Please advise. Regards skarthi _ Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month. Intro*Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117 ---(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] what is the best way to get key information in postgres?
Hi, I want to know what is the best way to get all the keys(unique, primary, foreign, multiple) for a table in postgres, I actually found this query and i am not sure if it is the best way to get the keys info: select r.relname as "Table", c.conname as "Constraint Name", contype as "Constraint Type", conkey as "Key Columns", confkey as "Foreign Columns", consrc as "Source" from pg_class r, pg_constraint c where r.oid=c.conrelid and relname='tablename'; And if the above query is good, how can I display in the output the column names that relates to the key. Can I use that query to get The multiple key info? or what is the best way to do so? I will really appreciate your help. Thanks, Noura Elhawary ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Esay question, about the numeric format
On Thu, Feb 22, 2007 at 12:20:12 +0100, Rafa Comino <[EMAIL PROTECTED]> wrote: > Hi every body > I have this query > SELECT 20.00::numeric(38,2) > and postgre gives me 20, i need that postgre gives me 20.00 > What can i do? i suppose this must be easy, but i dont find how to do ir > thanks every body If the exact output format matters, you should probably use to_char to convert the number to a string. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to union table without union statement?
Hi, I am using the following query now, but the time is too slow. could anyone can help me? CREATE OR REPLACE VIEW alllogview AS ((( SELECT alarmdtl.tagname, a_alarmtbl.occurtime, a_alarmtbl.restoretime, a_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM a_alarmtbl, alarmdtl WHERE a_alarmtbl.tagname::text = alarmdtl.tagname::text UNION ALL SELECT alarmdtl.tagname, b_alarmtbl.occurtime, b_alarmtbl.restoretime, b_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM b_alarmtbl, alarmdtl WHERE b_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, c_alarmtbl.occurtime, c_alarmtbl.restoretime, c_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM c_alarmtbl, alarmdtl WHERE c_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, d_alarmtbl.occurtime, d_alarmtbl.restoretime, d_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM d_alarmtbl, alarmdtl WHERE d_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, e_alarmtbl.occurtime, e_alarmtbl.restoretime, e_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM e_alarmtbl, alarmdtl WHERE e_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, f_alarmtbl.occurtime, f_alarmtbl.restoretime, f_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM f_alarmtbl, alarmdtl WHERE f_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, g_alarmtbl.occurtime, g_alarmtbl.restoretime, g_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM g_alarmtbl, alarmdtl WHERE g_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, h_alarmtbl.occurtime, h_alarmtbl.restoretime, h_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM h_alarmtbl, alarmdtl WHERE h_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, i_alarmtbl.occurtime, i_alarmtbl.restoretime, i_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM i_alarmtbl, alarmdtl WHERE i_alarmtbl.tagname::text = alarmdtl.tagname::text ORDER BY 1; On 2/28/07, Hiltibidal, Robert <[EMAIL PROTECTED]> wrote: Can you provide a schema? -- *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *On Behalf Of *calendarw *Sent:* Wednesday, February 28, 2007 4:33 AM *To:* pgsql-sql@postgresql.org *Subject:* [SQL] How to union table without union statement? Hi, I need to combine 10 tables which contain same table structure and join an "other table" to show the latest 200 record, I am join the "other table" first and using union statement to select all record now but the collection time is super slow, how can I improve the collection speed? Thanks. -- Jr. P calendarw PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:[EMAIL PROTECTED] Thank you. -- Jr. P calendarw
[SQL] How to store a password encripted in a user defined table
Hello, I wonder if somebody knows how to store passwords in a column that is part of a user defined table. I've been searching and reading the documentation, but I can't find what I'm looking for. I just get password subjects related to client's connections to the database. Thanks in advanced for any help you can provide. ___ Do You Yahoo!? La mejor conexión a Internet y 2GB extra a tu correo por $100 al mes. http://net.yahoo.com.mx
[SQL] column definition list of a dynamic record argument
Hi, I'd like to build a function which have a RECORD type input argument, and to find in the function body its associated column definition list. Is it posible ? TIA, Sabin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
