[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] TEST - name of linked server in MSSQL , test - Postgres db name, public - schema name, users - table Returns: Server: Msg 7312, Level 16, State 1, Line 7 Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema. Works only via OPENQUERY: SELECT * FROM OPENQUERY (TEST, 'select * from users'); Does anybody know how to query Postgres WITHOUT OPENQUERY. I found in groups that: According to the documentation, SQL-Server supports any Win32 ODBC driver ... which would include PostgresODBC. However, in practice, only Microsoft-supplied drivers actually work; ODBC drivers for Postgres and MySQL mysteriously do not function with SQL-Server (even though they do work with MS Access). I think this is incompetence on Microsoft's part rather than malice; all of this works through DTS, a utility which is responsible for over 300 Knowledge Base issues. Note: I have not tried any of this with SQL Server 2000, just 7.0 sp3. Thank you, Igor Igor Kryltsov [EMAIL PROTECTED] 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
[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
