[SQL] finding schema of table that called a trigger
Is there a variable defined that has the schema of the table that called the trigger (like TG_RELNAME = table name)? I didn't see anything in the documentation. Is the only way to get that to look it up with TG_RELID? -- Jeff Hoffmann [EMAIL PROTECTED] ---(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] How to cast VARCHAR to BYTEA and vice-versa?
Hello all, I have a table with a VARCHAR column that I need to convert to a BYTEA. How do I cast VARCHAR to BYTEA? The following doesn't seem to work as it yields the 'cannot cast varchar to bytea' error message: varchar_data::bytea On the same topic, how do I do the reverse, that is, how to I cast from BYTEA back to VARCHAR? Thanks, -Michael Moran ---(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])
[SQL] Newbie wonder...
Please, bear with me, as this is my first post here. (1) I have a 2 table database, result of a conversion from Access. This has been made by an amateur, as one of the tables should be at least 3 related tables, bunch of redundant data,and the other one 2. I know I could create a table as the result of a request, so that I could isolate these redundant data, but what I don't know is how I would in the same time update the original table to put the ID of the matching ROW number in the newly created table, instead of the redundant data ? Should I create a stored procedure for that, and if yes, how ? Or should I do that in 2 passes, sequentially ? (2) How should I go to create a sequence for an existing table? For all futures data entry, after this conversion, I want the unique ID for each row to come from a sequence, but if I know how to create a table using serial, I am not sure how to modify one for this. Thanks, Bernard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] plpgsql & date-time functions
I have a function and I want to update a table's two rows but having problem with plpgsql & date-time functions. First field of the table must be now() this is ok.. but the second field must be now() + '60 days' if the query like this : SELECT INTO to_day now() + interval '60 days' ; it works but if I use variable instead of '60 days' it doesn't work. And I must use variable Thanks ---(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] group by before and after date
I got it. I had to put the whole case statement into the sum so my statement ended up: select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, sum(coalesce(case when b.DatePromisedBy<=a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedBefore, sum(coalesce(case when b.DatePromisedBy >a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedAfter from TableA a left join TableB on a.partid=b.partid group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have 2 tables 1 has a date field and component need by that date and the > other has all the upcoming orders. > I am trying to build a query that will give me the Date and ComponentNeed > and also how many components have been ordered before that date and how many > after. > PostGreSQL is telling me I need to group on DatePromisedBy. I have tried a > number of different possibilities which haven't worked and now I have run > into brain freeze. Any help would be appreciated. > > Tables > > TableA > DueDate > PartID > AmountNeeded > CurrentStock > > Table B > PartID > QuantityOrdered > DeliveredSum > DatePromisedBy > > The select that I want is > > select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, > coalesce(case when b.DatePromisedBy<=a.DueDate > then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) > end,0) as ExpectedBefore, > coalesce(case when b.DatePromisedBy >a.DueDate > then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) > end,0) as ExpectedAfter > from TableA a > left join (Table B) on a.partid=b.partid > group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] group by before and after date
I have 2 tables 1 has a date field and component need by that date and the other has all the upcoming orders. I am trying to build a query that will give me the Date and ComponentNeed and also how many components have been ordered before that date and how many after. PostGreSQL is telling me I need to group on DatePromisedBy. I have tried a number of different possibilities which haven't worked and now I have run into brain freeze. Any help would be appreciated. Tables TableA DueDate PartID AmountNeeded CurrentStock Table B PartID QuantityOrdered DeliveredSum DatePromisedBy The select that I want is select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, coalesce(case when b.DatePromisedBy<=a.DueDate then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) end,0) as ExpectedBefore, coalesce(case when b.DatePromisedBy >a.DueDate then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) end,0) as ExpectedAfter from TableA a left join (Table B) on a.partid=b.partid group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock ---(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] [ADMIN] Postgres schema comparison.
Hi, I have the wonderful job of re-synch'ing all the schemas out there not conforming to the master. I've looked everywhere for something that will help doing this. I'm specifically looking for a way to do a sumcheck or something similar on tables and/or schema as a whole to be able to do a table comparison with the master database. It will be a bonus to pick up exactly what is missing, but for now, just identifying differences is what I want to achieve. I'm using postgres 7.3 mostly, but I may want to use this for 7.4 and 8.0 databases as well. Has anybody got some suggestions of what I can do or use to do this. I've made a simple PHP script which compares the schemas of two databases. It was made in a hurry and is far from being complete, but it works for my purposes :) If you want give it a try, let me know Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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])
Re: [SQL] plpgsql & date-time functions
On Tue, 8 Mar 2005, Fatih Cerit wrote: > I have a function and I want to update a table's two rows but having problem > with plpgsql & date-time functions. First field of the table must be now() > this is ok.. but the second field must be now() + '60 days' if the query > like this : SELECT INTO to_day now() + interval '60 days' ; it works but if > I use variable instead of '60 days' it doesn't work. And I must use > variable If you want a variable number of days, something like now() + variable * interval '1 day' will probably work. ---(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] Newbie wonder...
Bernard, If you are simply doing a one-time convert of an old database schema to a new one, simply load the old tables into postgres and then use SQL commands to insert the data into the new tables. For a sequence on the "existing table," you can do as above and load the old table or just use copy with the column names of all columns except the column with the serial values--these will be auto-incremented. Sean - Original Message - From: "Bernard Grosperrin" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 08, 2005 6:25 PM Subject: [SQL] Newbie wonder... Please, bear with me, as this is my first post here. (1) I have a 2 table database, result of a conversion from Access. This has been made by an amateur, as one of the tables should be at least 3 related tables, bunch of redundant data,and the other one 2. I know I could create a table as the result of a request, so that I could isolate these redundant data, but what I don't know is how I would in the same time update the original table to put the ID of the matching ROW number in the newly created table, instead of the redundant data ? Should I create a stored procedure for that, and if yes, how ? Or should I do that in 2 passes, sequentially ? (2) How should I go to create a sequence for an existing table? For all futures data entry, after this conversion, I want the unique ID for each row to come from a sequence, but if I know how to create a table using serial, I am not sure how to modify one for this. Thanks, Bernard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] outputting dates
hi, i have set my datetype to 'European, SQL'. This correctly accepts dates of the form dd/mm/. However, the output is still in the '-mm-dd' format. How do i get the default output as dd/mm/? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net àà à! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sql: "LIKE" problem
I am using "postgresql-7.3.2-1" now!
I met a problem when using "LIKE" in "WHERE" clause.
For example, a table named "t_test", and its data looks like below:
# SELECT * FROM t_test;
id | c_name
+
1 | abc\
2 | abc\de
(2 rows)
The thing I want to do is searching in above table and restricting
the results to those that begin with string "abc\". The search result is:
# SELECT * FROM t_test WHERE c_name LIKE 'abc\%';
id | c_name
+
1 | abc\
2 | abc\de
(2 rows)
I want to go a step further, and restrict the results to those
that begin with string "abc\d". The search result is:
# SELECT * FROM t_test WHERE c_name LIKE 'abc\d%';
id | c_name
+
(0 rows)
I don't know why the result is "0 rows"(why not is "1 rows"),
And I have trid "LIKE 'abc\\d%'", the result is also "0 rows".
Is this a bug for "postgresql-7.3.2-1" ?
Or how can I search a field when it contains a backslash('\') ?
Thanks!!!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] lower and unicode
Hi , I tested the lower with unicode on 8.0.1 and find it not to work. If I have only latin symbols it is working well, but if I try to use also cyrillic the lower simpli ignore this symbols and all stay as is. I readet that this will work on 8.x . Exists some one using lower/upper + unicode and where can I find info about unicode status with pg? regards, ivan. ---(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] sql: "LIKE" problem
"Her Goo" <[EMAIL PROTECTED]> writes: > I want to go a step further, and restrict the results to those > that begin with string "abc\d". You need four backslashes for that, because \ is special to both the string literal parser and the LIKE operator. So '' reduces to a string constant containing \\, and then the LIKE operator sees that as a quoted backslash. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
