Re: [SQL] How to Convert Integer to Serial
On Fri, Oct 29, 2010 at 3:48 PM, venkat ven.tammin...@gmail.com wrote:
Dear All,
I want to convert integer datatype to serial datatype.. is that
possible.Please let me know.
I don't think ALTER TABLE will let you do that... so the best way to achieve
the same is:
- Create a sequence for the column (set the start of sequence according to
the values you got already in the column)
- Now set this sequence as the default value for that specific column which
you need to ALTER like this:
ALTER TABLE tablename ALTER COLUMN columnname
SET DEFAULT nextval('newsequence'::regclass);
--
Shoaib Mir
http://shoaibmir.wordpress.com/
Re: [SQL] How to Convert Integer to Serial
On Fri, Oct 29, 2010 at 3:58 PM, venkat ven.tammin...@gmail.com wrote: Dear All, I got the solution... Here is my query ALTER TABLE DemoTable ALTER gid TYPE INT2; Are you sure that you have converted here to a SERIAL type? -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [SQL] Can i force deletion of dependent rows?
On Sat, Feb 13, 2010 at 10:23 PM, Wayne E. Seguin wayneeseg...@gmail.comwrote: Is there another answer, asside from in the application and/or as a trigger using `DELETE FROM B WHERE z = 'x';` as it's body? Did you look at ON DELETE CASCADE option, check out http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html CREATE TABLE order_items ( product_no integer REFERENCES products *ON DELETE RESTRICT*, order_id integer REFERENCES orders *ON DELETE CASCADE*, quantity integer, PRIMARY KEY (product_no, order_id) ); -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [SQL] mail alert
- Original Message - *From:* Jan Verheyden jan.verhey...@uz.kuleuven.ac.be *To:* 'pgsql-sql@postgresql.org' %27pgsql-...@postgresql.org%27 *Sent:* Tuesday, August 11, 2009 6:31 PM *Subject:* [SQL] mail alert Hi All, I was looking in what way it’s possible to alert via mail when some conditions are true in a database. I guess you might be able to do that by using PLPython, PLPerl or PLTcl and using the standard functions for doing emails in there. -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [SQL] Import (.CVS File) to postgreSql
On Tue, Aug 11, 2009 at 2:36 PM, Premila Devi premiladevi.daya...@agbnielsen.com wrote: Dear Sir, I am really don’t understand Sir. May I have a screenshot on how to make these configuration sir. The File is in CVS, as I attached before sir. As I told you before just use the COPY command. You don't need to do any configurations and all.. just use the CSV option with COPY as explained in the manual. Its just like any other SQL statement once you have the database server installed. Please do not post privately to my email as its always better posting it to the list. -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [SQL] search path within trigger
Did you try 'select current_schema' ? -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 6/9/07, chester c young [EMAIL PROTECTED] wrote: within a table pl/pgsql trigger, what's the easiest way to see if the schema for the triggered table is the same as search_path? Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=listsid=396545469 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Inserting a path into Database
If you are on 8.1 you can use double qoutes ( 'C:\\Program Files\\My
program' ) on in 8.2 you can use the new backslash_quote (string)
setting.
You can find help on backslash_quote (string) at --
http://www.postgresql.org/docs/current/static/runtime-config-compatible.html
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 6/4/07, Ranieri Mazili [EMAIL PROTECTED] wrote:
Hello,
I need to insert a path into a table, but because \ I have a error by
postgres, so how can I insert a path like bellow into a table:
insert into production values ('C:\Program Files\My program');
I appreciate any help
Thanks
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Encode
For that you will need to drop and re-create it with a different encoding -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/19/07, Ezequias R. da Rocha [EMAIL PROTECTED] wrote: Hi list, It is possible to change the Encode of one database while it is on ? Sincerely Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time of executed query
Did you try EXPLAIN ANALYZE SQL Query? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this result in a new column the same time will be replicated many times and consuming more processing. Any other suggestion ? Ezequias 2007/3/6, Bart Degryse [EMAIL PROTECTED]: note the time just before your operation starts note the time just after it ends show timeafter - timebefore Ezequias Rodrigues da Rocha [EMAIL PROTECTED] 2007-03-06 14:20 Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time of executed query
EXPLAIN ANALYZE does give the query execution time at the end of its output. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My resultset is retrieving my rows. Another question that cames with the Analyze. PgAdmin return the time to retrieve the data or all time to fill the grid on the SQL Editor. -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ 2007/3/6, Shoaib Mir [EMAIL PROTECTED]: Did you try EXPLAIN ANALYZE SQL Query? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this result in a new column the same time will be replicated many times and consuming more processing. Any other suggestion ? Ezequias 2007/3/6, Bart Degryse [EMAIL PROTECTED]: note the time just before your operation starts note the time just after it ends show timeafter - timebefore Ezequias Rodrigues da Rocha [EMAIL PROTECTED] 2007-03-06 14:20 Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox: http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Insert into a date field
Just tried the following way, that might help you:
postgres=# create table testing (a date);
CREATE TABLE
postgres=# insert into testing values (to_date('1963-09-01', '-MM-DD'));
INSERT 0 1
postgres=# select * from testing;
a
1963-09-01
(1 row)
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/2/07, Richard Huxton dev@archonet.com wrote:
Ezequias Rodrigues da Rocha wrote:
Hi list,
I am trying to insert a record in a table with a date field but the
postgresql reports me the following error:
ERROR: column data_nascimento is of type date but expression is of
type
integer
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
I tryed like this:
'1963-09-01'
'1963-09-01'::date
1963-09-01
Show us the whole statement - this doesn't look like the error to me.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Duplicate records
That has been asked in the past a number of time too, you can look at the following for details -- http://archives.postgresql.org/pgsql-novice/2006-06/msg00093.php -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/2/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/
Re: [SQL] Duplicate records
Where do you see the second table in it? I guess here: A = mytable B = (select field1, field2, count(*) from mytable group by field1, field2 having count(*) 1) So that is all around one table that is 'mytable', where A and B are just the aliases. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/2/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: Thank you but are you talking of two tables ? I intent to check in only one table. Please explain to me. Ezequias 2007/2/2, Bart Degryse [EMAIL PROTECTED]: select * from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) 1) B where A.field1 = B.field1 and A.field2 = B.field2 Ezequias Rodrigues da Rocha [EMAIL PROTECTED] 2007-02-02 14:48 Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/ -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/
Re: [SQL] Log, Logs and more Logs
Check for log_directory setting in your postgresql.conf file... - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/31/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I noticed today morning that my pg.log doesn't exist in my server. Why it occurs ? My /var/lib/pgsql/data is like that: [EMAIL PROTECTED] data]# ls -la total 92 drwx-- 10 postgres postgres 4096 Jan 31 08:35 . drwx-- 4 postgres postgres 4096 Jul 26 2006 .. drwx-- 7 postgres postgres 4096 Jan 30 12:29 base drwx-- 2 postgres postgres 4096 Jan 31 08:44 global drwx-- 2 postgres postgres 4096 Feb 17 2006 pg_clog -rw--- 1 postgres postgres 3513 Sep 14 15:28 pg_hba.conf -rw--- 1 postgres postgres 1460 Feb 17 2006 pg_ident.conf drwx-- 4 postgres postgres 4096 Feb 17 2006 pg_multixact drwx-- 2 postgres postgres 4096 Jan 28 16:00 pg_subtrans drwx-- 2 postgres postgres 4096 Feb 17 2006 pg_tblspc drwx-- 2 postgres postgres 4096 Feb 17 2006 pg_twophase -rw--- 1 postgres postgres 4 Feb 17 2006 PG_VERSION drwx-- 3 postgres postgres 4096 Jan 30 13:14 pg_xlog -rw--- 1 postgres postgres 14079 Jan 30 14:42 postgresql.conf -rw-r--r-- 1 root root 13652 Mar 17 2006 postgresql.conf.old -rw--- 1 postgres postgres49 Jan 31 08:36 postmaster.opts -rw--- 1 postgres postgres45 Jan 31 08:36 postmaster.pid [EMAIL PROTECTED] data]# Can someone tell me how to solve this kind of problem ? Ezequias 2007/1/30, Andrew Sullivan [EMAIL PROTECTED]: On Tue, Jan 30, 2007 at 04:41:13PM -0200, Ezequias Rodrigues da Rocha wrote: So you are tell me that it is impossible to retrieve it ok ? No. Just by changing this values (what I did now) it is possible to get the error messages and their statements OK?. Yes, but you need to signal the postmaster to reload its config file. Either kill -SIGHUP or restart the postmaster. After that, you should see the statement that caused your error. Note that if the UPDATE itself doesn't cause the error, but say a later COMMIT (you can get this in serializable mode easily), you'll see the COMMIT as the thing that caused the error. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(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/
Re: [SQL] Log, Logs and more Logs
You need to create the folder manually, give the postgres user permission for write on it and then restart the database server. This way you should be able to set the log files in pg_log folder... Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/31/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I restart the server but the pg_log folder doesn't appears. My log session is like this on postgresql.conf: # These are only used if redirect_stderr is on: log_directory = 'pg_log'# Directory where log files are written # Can be absolute or relative to PGDATA log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern. # Can include strftime() escapes #log_truncate_on_rotation = off # If on, any existing log file of the same # name as the new log file will be # truncated rather than appended to. But # such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. #log_rotation_age = 1440# Automatic rotation of logfiles will # happen after so many minutes. 0 to # disable. #log_rotation_size = 10240# Automatic rotation of logfiles will # happen after so many kilobytes of log # output. 0 to disable. Regards... Ezequias 2007/1/31, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] : It was commented. Thank you Shoaib Mir I must restart my server ? Ezequias 2007/1/31, Shoaib Mir [EMAIL PROTECTED]: Check for log_directory setting in your postgresql.conf file... - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/31/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I noticed today morning that my pg.log doesn't exist in my server. Why it occurs ? My /var/lib/pgsql/data is like that: [EMAIL PROTECTED] data]# ls -la total 92 drwx-- 10 postgres postgres 4096 Jan 31 08:35 . drwx-- 4 postgres postgres 4096 Jul 26 2006 .. drwx-- 7 postgres postgres 4096 Jan 30 12:29 base drwx-- 2 postgres postgres 4096 Jan 31 08:44 global drwx-- 2 postgres postgres 4096 Feb 17 2006 pg_clog -rw--- 1 postgres postgres 3513 Sep 14 15:28 pg_hba.conf -rw--- 1 postgres postgres 1460 Feb 17 2006 pg_ident.conf drwx-- 4 postgres postgres 4096 Feb 17 2006 pg_multixact drwx-- 2 postgres postgres 4096 Jan 28 16:00 pg_subtrans drwx-- 2 postgres postgres 4096 Feb 17 2006 pg_tblspc drwx-- 2 postgres postgres 4096 Feb 17 2006 pg_twophase -rw--- 1 postgres postgres 4 Feb 17 2006 PG_VERSION drwx-- 3 postgres postgres 4096 Jan 30 13:14 pg_xlog -rw--- 1 postgres postgres 14079 Jan 30 14:42 postgresql.conf -rw-r--r-- 1 root root 13652 Mar 17 2006 postgresql.conf.old -rw--- 1 postgres postgres49 Jan 31 08:36 postmaster.opts -rw--- 1 postgres postgres45 Jan 31 08:36 postmaster.pid [EMAIL PROTECTED] data]# Can someone tell me how to solve this kind of problem ? Ezequias 2007/1/30, Andrew Sullivan [EMAIL PROTECTED]: On Tue, Jan 30, 2007 at 04:41:13PM -0200, Ezequias Rodrigues da Rocha wrote: So you are tell me that it is impossible to retrieve it ok ? No. Just by changing this values (what I did now) it is possible to get the error messages and their statements OK?. Yes, but you need to signal the postmaster to reload its config file. Either kill -SIGHUP or restart the postmaster. After that, you should see the statement that caused your error. Note that if the UPDATE itself doesn't cause the error, but say a later COMMIT (you can get this in serializable mode easily), you'll see the COMMIT as the thing that caused the error. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(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
Re: [SQL] Log, Logs and more Logs
You need to set it like this: log_destination = 'syslog,stderr' redirect_stderr = on and then restart the server. It should show up the log from then onwards --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/31/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I really don't know how to set it. I created my pg_log on: /var/lib/pgsql/data. the settings on pgsql.conf is like this: #--- # ERROR REPORTING AND LOGGING #--- # - Where to Log - #log_destination = 'stderr'# Valid values are combinations of # stderr, syslog and eventlog, # depending on platform. # This is used when logging to stderr: #redirect_stderr = off# Enable capturing of stderr into log # files Ezequias 2007/1/31, Joe [EMAIL PROTECTED]: On Wed, 2007-01-31 at 10:12 -0200, Ezequias Rodrigues da Rocha wrote: I restart the server but the pg_log folder doesn't appears. My log session is like this on postgresql.conf: # These are only used if redirect_stderr is on: Did you look at the setting of redirect_stderr, and the various logging settings just above it? Joe -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/
Re: [SQL] Log, Logs and more Logs
No problemo :) What was the query and the statement for error? that way we might be able to help... - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/31/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: Great. Now the log is working. Thank you, Thank you, Thank you, But my statement does not report an error anymore. I really confused now. Any suggestion ? Ezequias 2007/1/31, Shoaib Mir [EMAIL PROTECTED]: You need to set it like this: log_destination = 'syslog,stderr' redirect_stderr = on and then restart the server. It should show up the log from then onwards --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/31/07, Ezequias Rodrigues da Rocha [EMAIL PROTECTED] wrote: I really don't know how to set it. I created my pg_log on: /var/lib/pgsql/data. the settings on pgsql.conf is like this: #--- # ERROR REPORTING AND LOGGING #--- # - Where to Log - #log_destination = 'stderr'# Valid values are combinations of # stderr, syslog and eventlog, # depending on platform. # This is used when logging to stderr: #redirect_stderr = off# Enable capturing of stderr into log # files Ezequias 2007/1/31, Joe [EMAIL PROTECTED]: On Wed, 2007-01-31 at 10:12 -0200, Ezequias Rodrigues da Rocha wrote: I restart the server but the pg_log folder doesn't appears. My log session is like this on postgresql.conf: # These are only used if redirect_stderr is on: Did you look at the setting of redirect_stderr, and the various logging settings just above it? Joe -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/ -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/
Re: [SQL] Update query by joining multiple tables.
Might be a view and then a rule attached with that can help you out with doing updates using joins -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/17/07, Andrew Sullivan [EMAIL PROTECTED] wrote: On Wed, Jan 17, 2007 at 04:50:18PM +0530, Moiz Kothari wrote: http://www.postgresql.org/docs/8.1/interactive/sql-update.html, it is not specified if a join can be done between multiple tables to update a table, i Sure it is: A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. 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 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] Unable to drop table, error mentions reltriggers
Run the following: update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='vm_message'; It should solve the problem -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/28/06, Kashmira Patel (kupatel) [EMAIL PROTECTED] wrote: Hello, I have two tables as follows: create table vm_message ( MessageIdvarchar(128) not null, MessageType integer not null default 1, UseCount integer not null default 1, Sender varchar(256) not null, Urgent boolean not null default false, Private boolean not null default false, MessageLengthinteger , MessageSize integer , MessageTime bigint, AttachedMsgIdvarchar(128) , MessageOid OID , UTCTime varchar(32) , EndTime bigintnot null default 9223372036854775807, ReceiveTime bigintnot null default 0, NdrReasoninteger , StartTimebigintdefault 0, MWIOnboolean not null default false, TotalMessageLength integer not null default 0, uid serial not null UNIQUE, CallerPhoneNumbervarchar(15) not null default '', foreign key (AttachedMsgId) references vm_message on delete cascade on update cascade, primary key (MessageId) ); create table vm_future_msg_job ( MessageIdvarchar(128) not null, Recipients varchar(1024) not null, StartTimebigintnot null, JobType int default 0, foreign key (MessageId) references vm_message on delete cascade on update cascade, primary key (MessageId,Recipients) ); When I try to drop either one of these tables, I get the following error: ERROR: relation vm_message has reltriggers = 0 and the table does not get dropped. What does this error mean???
[SQL] subquery abnormal behavior
I just noticed an abnormal behavior for the subquery: create table myt1 (a numeric); create table myt2 (b numeric); select a from myt1 where a in (select a from myt2); This should be giving an error that column 'a' does not exist in myt2 but it runs with any error... I had been trying it on 8.2! Can someone please comment? Thank you, - Shoaib Mir EnterpriseDB (www.enterprisedb.com)
Re: [SQL] subquery abnormal behavior
I just noticed the same behavior in Oracle and SQL Server as well :) Regards, - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/11/06, Shoaib Mir [EMAIL PROTECTED] wrote: I just noticed an abnormal behavior for the subquery: create table myt1 (a numeric); create table myt2 (b numeric); select a from myt1 where a in (select a from myt2); This should be giving an error that column 'a' does not exist in myt2 but it runs with any error... I had been trying it on 8.2! Can someone please comment? Thank you, - Shoaib Mir EnterpriseDB (www.enterprisedb.com)
Re: [SQL] subquery abnormal behavior
Oh that explains a lot... Thank you, - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/11/06, Michael Glaesemann [EMAIL PROTECTED] wrote: On Dec 11, 2006, at 15:48 , Shoaib Mir wrote: create table myt1 (a numeric); create table myt2 (b numeric); select a from myt1 where a in (select a from myt2); This should be giving an error that column 'a' does not exist in myt2 but it runs with any error... The a in the IN clause is the same a in outer expression. This is in effect: select a from myt1 where a = a; Now, if you were to say select a from myt1 where a in (select myt2.a from myt2); ERROR: column myt2.a does not exist LINE 1: select a from myt1 where a in (select myt2.a from myt2); And if you were to instead have create table myt1 (a numeric); CREATE TABLE create table myt2 (b numeric); CREATE TABLE insert into myt1(a) values (1), (2); INSERT 0 2 insert into myt2 (b) values (3), (4), (2); INSERT 0 3 create table myt3 (a numeric); CREATE TABLE insert into myt3 (a) values (2), (3),(4); INSERT 0 3 test=# select a from myt1 where a in (select a from myt3); a --- 2 (1 row) It looks like PostgreSQL treats it as a natural join like select a from myt1 natural join myt3; Hope this helps. Michael Glaesemann grzm seespotcode net
Re: [SQL] Problem with SQL stored procedure
Wrong mailing list to ask this :-) Try out a MS SQL Server one and you will get good help on that... Regards, Shoaib On 12/8/06, sid tow [EMAIL PROTECTED] wrote: Hi all I dont know if this is the right forum to post this but any way if you could help me or guide me to an appropriate forum, I would appreciate it. I am trying to call a SQL (SQL server 2000) stored procedure from my c++ code and in that stored procedure I have a Print statement which will print some string. Now the problem is that the parameters that I supply to the stored procedure are returned back with out being modified, but I expect some modifications to happen to those parameters. And if I comment out the Print statement I get what I desire. Can somebody explain what exactly is happening here? Regards, Syed - Original Message From: ivan marchesini [EMAIL PROTECTED] To: Postgres SQL language list pgsql-sql@postgresql.org Sent: Monday, November 13, 2006 10:02:06 PM Subject: [SQL] drop a check Dear all... I have created a check constraint without giving it a name.. now I have a check named $25 in my table that I need to drop or modify!!! How can I do??? with names is simple alter table tablename drop constraint constraintname; but without name?? :-) many thanks... Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Access over 1 million songs - Yahoo! Music Unlimited.http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/*http://music.yahoo.com/unlimited/
Re: [SQL] Multiple DB join
I think using the contrib module 'dblink' (http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/) can help you here.Thanks, -- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 8/15/06, Sumeet Ambre [EMAIL PROTECTED] wrote:Hi All,I have a database which consists of 20 million records and I've split up the db into 6-7 dbs. I have a base database which consists ofthe ids with link all the databases. I'm performing search on thissingle base table. After searching i get some ids which are ids in the otherdatabases which i split up. Now i need to retrieve those records. Is there a way i can join tables from multiple databases as we can joinmultiple tablesin a single database.Thanks,SA.---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Change of data type
You can do the following:ALTER TABLE table-name ALTER colum-name TYPE text;Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com) On 8/8/06, Kumar Dev [EMAIL PROTECTED] wrote: Use Alter table notebook from control center right click on the table and open alter table notebook you can drop a column or add a column or change the datatype Kumar On 8/7/06, Judith [EMAIL PROTECTED] wrote: Hello everybody, excuse me how can I change de data type of a field,I currently have: material character(30) but I now want the field in text type like this: materialtext somebody knows if ALTER TABLE has some option to do this?, or Howcan I do that?---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly -- KumarDB2 DBA SAP Basis professional
