Re: [SQL] unique key problem on update
Gary Stainburn wrote on 20.09.2013 18:30: You need to define the primary key as deferrable: create table skills_pages ( sp_idserial not null, sp_sequence integer not null, sp_title character varying(80), sp_narative text, primary key (sp_id) deferrable ); Cheers. I'll look at that. It's actually the second unique index that's the problem but I'm guessing I can set that index up as deferrable too. Ah, sorry didn't see that ;) but, yes it works the same way: create table skills_pages ( sp_idserial not null, sp_sequence integer not null, sp_title character varying(80), sp_narative text, primary key (sp_id), unique (sp_sequence) deferrable ); Hopefully it'll work for mysql too. No, it won't. MySQL neither has deferrable constraints nor does it evaluate them on statement level (they are *always* evaluated row-by-row). -- 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] unique key problem on update
Gary Stainburn wrote on 20.09.2013 18:07: I want to add a new page after page 2 so I try to increase the sequence number of each row from page 3 onwards to make space in the sequence for the new record. However, I get duplicate key errors when I try. Can anyone suggest how I get round this. Also, the final version will be put onto a WordPress web site which means I will have to port it to MYSQL which I don't know, so any solution that will work with both systems would be a great help. You need to define the primary key as deferrable: create table skills_pages ( sp_idserial not null, sp_sequence integer not null, sp_title character varying(80), sp_narative text, primary key (sp_id) deferrable ); -- 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] Unquoted column names fold to lower case
Theodore Petrosky, 03.07.2013 15:41: sorry, but you misunderstand. this is the correct behavior of SQL. It is part of the specification to do this. Not quite. The SQL standard requires folding to uppercase. -- 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] Unquoted column names fold to lower case
Dev Kumkar wrote on 03.07.2013 17:50: Note that adding quotes for aliases will be blessed by PostgreSQL and then those will be folded to upper case BUT this adds up to lot of changes in the SQL layer. I wonder why you need that. I never had the requirement for that. Which driver/interface do you use that requires an alias to be all uppercase? -- 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] Advice with an insert query
JORGE MALDONADO, 07.06.2013 15:58: I need to insert records into a table where one value is fixed and 2 values come from a SELECT query, something like the following example: INSERT INTO table1 fld1, fld2, fl3 VALUES value1, (SELECT fldx, fldy FROM table2) Is this valid? Respectfully, Jorge Maldonado INSERT INTO table1 (fld1, fld2, fl3) SELECT value1, fldx, fldy FROM table2 -- 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] check for overlapping time intervals
Wolfgang Meiners, 22.04.2013 12:19: Is there a simpler way to check for overlapping timeintervals? I ask this question, because i have more similar tables with similar layout and would have to write similar functions again and again. Do you have the possibility to upgrade to 9.2? The range types introduced with 9.2 seem to be *exactly* what you need. Regards Thomas -- 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
Don Parris wrote on 24.02.2013 23:20: 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. How do you ensure referential integrity with this approach? (i.e. make sure that all elements from the path column actually point to an existing category) Thomas -- 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] Split a string to rows?
Emi Lu wrote on 07.01.2013 21:16: Thanks a lot! I just noticed that my postgresql is 8.3(unnest function is not there by default). Is there a way that I could download and load only this function from somewhere? Are you aware that 8.3 will be de-suppported as of next month? You should really think about an upgrade *now* Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Using regexp_matches in the WHERE clause
Hi, I stumbled over this question on Stackoverflow http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match And my initial reaction was, that this should be possible using regexp_matches. So I tried: SELECT * FROM some_table WHERE regexp_matches(somecol, 'foobar') is not null; However that resulted in: ERROR: argument of WHERE must not return a set Hmm, even though an array is not a set I can partly see what the problem is (although given the really cool array implementation in PostgreSQL I was a bit surprised). So I though, if I convert this to an integer, it should work: SELECT * FROM some_table WHERE array_length(regexp_matches(somecol, 'foobar'), 1) 0 but that still results in the same error. But array_length() clearly returns an integer, so why does it still throw this error? I'm using 9.2.1 Regards Thomas -- 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 regexp_matches in the WHERE clause
So I tried: SELECT * FROM some_table WHERE regexp_matches(somecol, 'foobar') is not null; However that resulted in: ERROR: argument of WHERE must not return a set Hmm, even though an array is not a set I can partly see what the problem is (although given the really cool array implementation in PostgreSQL I was a bit surprised). So I though, if I convert this to an integer, it should work: SELECT * FROM some_table WHERE array_length(regexp_matches(somecol, 'foobar'), 1) 0 but that still results in the same error. But array_length() clearly returns an integer, so why does it still throw this error? I'm using 9.2.1 Sounds to me like this: http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html Thanks, but my question is not related to the underlying problem. My question is: why I cannot use regexp_matches() in the WHERE clause, even when the result is clearly an integer value? Regards Thomas -- 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 regexp_matches in the WHERE clause
Pavel Stehule, 27.11.2012 13:26: My question is: why I cannot use regexp_matches() in the WHERE clause, even when the result is clearly an integer value? use a ~ operator instead So that means, regexp_matches cannot be used as an expression in the WHERE clause? Regards Thomas -- 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] Fun with Dates
Mark Fenbers wrote on 29.10.2012 23:38:
Greetings,
I want to be able to select all data going back to the beginning of
the current month. The following portion of an SQL does NOT work,
but more or less describes what I want...
... WHERE obstime = NOW() - INTERVAL (SELECT EXTRACT (DAY FROM NOW()
) ) + ' days'
In other words, if today is the 29th of the month, I want to select
data that is within 29 days old... WHERE obstime = NOW() - INTERVAL
'29 days'
Or the other way round: anything that is equal or greater than the first
of the current month:
select ...
from foobar
where obstime = date_trunc('month', current_date);
Thomas
--
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] [noob] How to optimize this double pivot query?
Robert Buck, 02.10.2012 03:13: So as you can probably glean, the tables store performance metric data. The reason I chose to use k-v is simply to avoid having to create an additional column every time a new metric type come along. So those were the two options I thought of, straight k-v and column for every value type. Are there other better options worth considering that you could point me towards that supports storing metrics viz. with an unbounded number of metric types in my case? Have a look at the hstore module. It's exactly meant for that scenario with the added benefit that you can index on that column and looking up key names and their values is blazingly fast then. That combined with the tablefunc module (which let's you do pivot queries) might make your queries substantially more readable (and maybe faster as well). Regards Thomas -- 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] Reuse temporary calculation results in an SQL update query
Matthias Nagel wrote on 29.09.2012 12:49: Hello, is there any way how one can store the result of a time-consuming calculation if this result is needed more than once in an SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. Here is an example of what I want: UPDATE table1 SET StartTime = 'time consuming calculation 1', StopTime = 'time consuming calculation 2', Duration = 'time consuming calculation 2' - 'time consuming calculation 1' WHERE foo; It would be nice, if I could use the new start and stop time to calculate the duration time. First of all it would make the SQL statement faster and secondly much more cleaner and easily to understand. Something like: with my_calc as ( select pk, time_consuming_calculation_1 as calc1, time_consuming_calculation_2 as calc2 from foo ) update foo set startTime = my_calc.calc1, stopTime = my_calc.calc2, duration = my_calc.calc2 - calc1 where foo.pk = my_calc.pk; http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING -- 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] Reuse temporary calculation results in an SQL update query
Matthias Nagel wrote on 29.09.2012 12:49: Hello, is there any way how one can store the result of a time-consuming calculation if this result is needed more than once in an SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. Here is an example of what I want: UPDATE table1 SET StartTime = 'time consuming calculation 1', StopTime = 'time consuming calculation 2', Duration = 'time consuming calculation 2' - 'time consuming calculation 1' WHERE foo; It would be nice, if I could use the new start and stop time to calculate the duration time. First of all it would make the SQL statement faster and secondly much more cleaner and easily to understand. Something like: with my_calc as ( select pk, time_consuming_calculation_1 as calc1, time_consuming_calculation_2 as calc2 from foo ) update foo set startTime = my_calc.calc1, stopTime = my_calc.calc2, duration = my_calc.calc2 - calc1 where foo.pk = my_calc.pk; http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING -- 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] DELETE using an outer join
Tom Lane, 19.07.2012 16:52: If you're using a reasonably recent version of PG, replacing the NOT IN by a NOT EXISTS test should also help. Thanks. I wasn't aware of that (and the NOT EXISTS does indeed produce the same plan as the OUTER JOIN solution) Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. Thanks as well. It's not a big deal for me. I was just curious if I missed something. Regards Thomas -- 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] DELETE using an outer join
Sergey Konoplev, 20.07.2012 10:21: On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Now I was wondering if a DELETE statement could be rewritten with the same strategy: Not at the moment. There have been discussions of allowing the same table name to be respecified in USING, but there are complications. However it works. DELETE FROM some_table USING some_table AS s WHERE some_table.col1 = s.col1 AND some_table.col2 = s.col2 AND some_table.id s.id; But that's not an outer join -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] DELETE using an outer join
Hi, (this is not a real world problem, just something I'm playing around with). Lately I had some queries of the form: select t.* from some_table t where t.id not in (select some_id from some_other_table); I could improve the performance of them drastically by changing the NOT NULL into an outer join: select t.* from some_table t left join some_other_table ot on ot.id = t.id where ot.id is null; Now I was wondering if a DELETE statement could be rewritten with the same strategy: Something like: delete from some_table where id not in (select min(id) from some_table group by col1, col2 having count(*) 1); (It's the usual - at least for me - get rid of duplicates statement) The DELETE .. USING seems to only allow inner joins because it requires the join to be done in the WHERE clause. So I can't think of a way to turn that NOT IN from the DELETE into an outer join with a derived table. Am I right that this kind of transformation is not possible or am I missing something? Regards Thomas -- 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] left outer join only select newest record
Gary Stainburn, 23.05.2012 11:47: Here is a select to show the problem. There is one stock record and two tax records. What I'm looking for is how I can return only the second tax record, the one with the highest ud_id select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; s_stock_no | s_regno | s_vin | s_created | ud_id | ud_handover_date +-+---++---+-- UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 41892 | 2012-04-06 UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 42363 | 2012-05-16 (2 rows) Something like: select * from ( select s_stock_no, s_regno s_vin, s_created, ud_id, ud_handover_date, row_number() over (partition by s_stock_no order by ud_id desc) as rn from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' ) t where rn = 1 The partition by s_stock_no order isn't really necessary as your where clause already limits that to a single stock_no. But in case you change that statement to return more than one stock_no in the future it will be necessary. -- 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] Finding Max Value in a Row
Carlos Mennens wrote on 11.05.2012 21:03:
I have a problem in SQL I don't know how to solve and while I'm sure
there are 100+ ways to do this in ANSI SQL, I'm trying to find the
most cleanest / efficient way. I have a table called 'users' and the
field 'users_id' is listed as the PRIMARY KEY. I know I can use the
COUNT function, then I know exactly how many records are listed but I
don't know what the maximum or highest numeric value is so that I can
use the next available # for a newly inserted record. Sadly the
architect of this table didn't feel the need to create a sequence and
I don't know how to find the highest value.
You can get the highest value using:
select max(users_id)
from users;
But that method is neither safe in a multi-user environment nor fast.
But you can always assign a sequence to that column even if it wasn't done
right at the start:
Create a new sequence owned by that column:
create sequence seq_users_id
owned by users.users_id;
Now set the value of the sequence to the current max. id:
SELECT setval('seq_users_id', max(users_id)) FROM users;
And finally make the users_id column use the sequence for the default value:
alter table users alter column users_id set default nextval('seq_users_id');
Thomas
--
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] Finding Max Value in a Row
Carlos Mennens wrote on 11.05.2012 21:30: Thanks for all the help thus far everyone! I sadly didn't create/design the table and would love to create a SEQUENCE on that particular field but not sure how unless I DROP the table and create from scratch. Currently the data TYPE on the primary key field (users_id) is CHAR and I have no idea why...it should be NUMERIC or SERIAL but it's not so my question is if I want to ALTER the column and create a sequence, would I simply do: ALTER TABLE users ALTER COLUMN users_id TYPE serial ; Obviously if any of the data stored in users_id is actual CHAR, I'm guessing the database would reject that request to change type as the existing data would match. However the data type is CHAR but the field values are all numeric from 100010 - 100301 so I'm hoping that would work for SERIAL which is just INTEGER, right? Use this: alter table users alter column users_id type integer using to_number(users_id, '9'); (Adjust the '9' to the length of the char column) Then create and assign the new sequence as I have shown in my other post. -- 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] Finding Max Value in a Row
Carlos Mennens wrote on 11.05.2012 21:50: On Fri, May 11, 2012 at 3:44 PM, Thomas Kellererspam_ea...@gmx.net wrote: Use this: alter table users alter column users_id type integer using to_number(users_id, '9'); (Adjust the '9' to the length of the char column) When you wrote Adjust the '9' to the length of the char column, do you mean change '9' to '312' if my last used maximum value was 312? So the next sequence primary key value would be '313', right? No, the number of 9's defined the number of digits in the numbers. There must not be less 9's in the format mask than the number of digits in the highest value. The above example would create wrong values if the highest number was 10 Check the manual about details on the format mask for to_number() -- 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] Finding Max Value in a Row
Carlos Mennens wrote on 11.05.2012 21:53: Very good question and asked by myself to the original SQL author and he explained while he didn't use the most efficient data types, he used ones he felt would be more transparent across a multitude of RDBMS vendors. So the answer is no, it would not be an issue considering I use and will always use PostgreSQL. If someone else uses a different vendor, they can manage that import/export process then. You should tell those people that char is a bad choice in _any_ DBMS due to the padding that is involved. varchar would have been slightly better. But it's never, ever a good idea to store numbers in a character column. *Every* RDBMS has some kind of integer datatype (they might just have different names). -- 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 change col name during query to use it in where clause
Marcel Ruff, 04.05.2012 12:25: Hi, is an alias name not usable in the where clause? select EXTRACT(day from enddate::TIMESTAMP - old_enddate::TIMESTAMP) AS TAGE from account_h where TAGE5; ERROR: column tage does not exist LINE 1: ... TAGE5 ... You need to wrap the query: select * from ( select EXTRACT(day from enddate::TIMESTAMP - old_enddate::TIMESTAMP) AS TAGE from account_h ) t where TAGE 5; -- 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 Multiple Records At Once?
Carlos Mennens wrote on 11.04.2012 19:50: I'm trying to update a customer record in a table however I need to change several values (cust_address, cust_contact, cust_email). My question is how do I properly format this into one single command? forza=# SELECT cust_id, cust_name, cust_address, cust_contact, cust_email forza-# FROM customers forza-# WHERE cust_name = 'iamUNIX' forza-# ; cust_id | cust_name | cust_address | cust_contact | cust_email +---+---++ 16 | MobileNX | 200 South Shore Drive | Carlos Mennens | car...@mobilenx.com (1 row) I did a quick Google search and I can see there is a method or procedure which involves parenthesis () however I'm not sure how I would change all the values listed above under one command. Can anyone please give me a quick example so I can see how this is drawn out via ANSI SQL? UPDATE customers SET cust_address = 'foo', cust_contact = 'Arthur', cust_email = 'art...@foo.bar' WHERE cust_name = 'iamUNIX' -- 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
rverghese wrote on 08.02.2012 19:07: I have a large table with about 60 million rows, everyday I add 3-4 million, remove 3-4 million and update 1-2 million. I have a script that reindexes concurrently a couple of times a week, since I see significant bloat. I have autovac on and the settings are below. I can't concurrently reindex the primary key, since there can be only one on a table. With 9.1 you can create a new index and drop and re-create the primary key using the new index. This still requires an exclusive lock on the table, but only for a very short moment: The following example is more or less taken from the manual: http://www.postgresql.org/docs/9.1/static/sql-altertable.html create unique concurrently new_index on your_table(your_pk); alter table your_table drop primary key; alter table your_table add primary key using index new_index; As this can be done in one transaction it should be safe with regards to the primary key. Thomas -- 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 COMPATIBILITY
Gera Mel Handumon, 17.01.2012 07:31: What version of postgresql that the update compatibility below will be implemented? UPDATE COMPATIBILITY UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id); None as far as I know. You need to rewrite it to: UPDATE accounts SET contact_last_name = s.last_name, contact_first_name = s.first_name FROM salesmen s WHERE s.id = accounts.sales_id -- 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 COMPATIBILITY
Adrian Klaver, 17.01.2012 16:19: You need to rewrite it to: UPDATE accounts SET contact_last_name = s.last_name, contact_first_name = s.first_name FROM salesmen s WHERE s.id = accounts.sales_id For completeness, you could also do: UPDATE accounts SET (contact_last_name,contact_first_name)= (s.last_name,s.first_name) FROM salesmen s WHERE s.id = accounts.sales_id Nice one! -- 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: Current transaction is aborted, commands ignored until end of transaction block
Marcin Mirosław wrote on 30.12.2011 12:07: Would be nice to have an option in PostgreSQL something along the lines of: 'abort-transaction-on-constraint-violation = false' That option is called MySQL with MyISAM tables. Not true. Oracle and others (I believe at least DB2) behave such that you can insert a bunch of rows and if one or more throw a constraint violation, the transaction can still be committed persisting those that do not violate the constraint. Hi, isn't this option: http://www.postgresql.org/docs/current/static/sql-set-constraints.html ? Regards Not that's something different. It would still prevent comitting the transaction if the constraint check fails at the end. This strict transaction concept is somewhat irritating when you come from other DBMS (such as Oracle or DB2). Using savepoints is the only option to simulate that behaviour in PostgreSQL (and then the constraints need to be immediate) Thomas -- 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] avoid the creating the type for setof
John Fabiani wrote on 30.12.2011 15:26: Hi, I recall somewhere I saw a simple plpgsql function that returned a table with more than one record that did not use a defined type or a temp table ( at least I think I did). Is it possible to create such a function that will return more than one record and not require a record type or temp table? something like return setof record as return query select ... return Sure: create or replace function returns table (f1 text, f2 integer) as $$ select col1, col2 from some table; $$ language sql; If you are using PL/pgSQL you need to use RETURN QUERY SELECT ... inside the function. For more details see the examples in the manual: http://www.postgresql.org/docs/current/static/sql-createfunction.html http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING Thomas -- 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] ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART
Manu T, 07.11.2011 08:18:
I am using this query in the procedure and i error is throwing as mentioned
below.and i want to convert the same oracle query into Postgresql.
ERROR--
*ERROR: syntax error at or near OVER
LINE 1: ...heme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (part...
^
QUERY: SELECT d1.scheme_id,d1.value, d1.dr_cr_flg from ( select
d.scheme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (partition by d.rule_id
order by to_number(d.value) desc) AS rk from ( select b.scheme_id, b.rule_id,
to_number( CASE b.value_type WHEN '%' THEN to_number((select
COALESCE((b.scheme_value * a.base_miles)/100,0) from point_mtrx_acrul
a,rule_matrix b where a.ORG = $1 and a.DEST = $2 and $3 between a.EFF_DT and
a.EXP_DT and a.ARLN_NBR_CD = $4 )) ELSE b.SCHEME_VALUE END ) as value1,
b.dr_cr_flg from rule_matrix b ,scheme_mstr c where b.rule_id = $5 and b.scheme_id
= c.scheme_id and $3 between c.EFF_DT and c.EXP_DT and b.value_type not in
('AWARD') ) d ) d1, scheme_mstr c where d1.rk=1 and c.scheme_id=d1.scheme_id
and $3 between c.EFF_DT and c.EXP_DT and rownum=1
CONTEXT: SQL statement in PL/PgSQL function rule_engine near line 563
** Error **
ERROR: syntax error at or near OVER
SQL state: 42601
Context: SQL statement in PL/PgSQL function rule_engine near line 563
Windowing functions where introduced in Version 8.4. Which version are you
using?
--
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 temporally disable foreign key constraint check
Emi Lu wrote on 21.10.2011 15:36: Good morning, Is there a way to temporally disabled foreign key constraints something like SET FOREIGN_KEY_CHECKS=0 When population is done, will set FOREIGN_KEY_CHECKS=1 Thanks a lot! Emi You can define the FKs as DEFERRABLE INITIALLY IMMEDIATE. Then at the start of your transaction you can defer constraint checking until you commit everything using: SET CONSTRAINTS ALL DEFERRED; Thomas -- 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] new user on mac
Scott Swank, 18.10.2011 23:47: I have a postgres 9.1 database up running, no problem. Purely in terms of writing sql (ddl, dml pg/plsql), what tools are recommended? Coming from an Oracle world, I'm thinking of toad, sql developer, etc. 1. psql text editor of choice (if so, which one?) 2. navicat 3. textmate with pgedit 4. eclipse plugin 5. other? check out the list at: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools -- 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 the aggregate function max()
John Fabiani, 23.09.2011 04:49: I need to find the max(date) of a field but I need that value later in my query. If I select max(x.date_field) as special_date from (select date_field) from table where ...)x I get one row and column. But now I want to use that field in the rest of the query select y.*, max(x.date_field) as special_date from (select date_field) from table where ...)x from aTable y where y.somefield = special_date. The above only returns one row and one column the special_date. Not sure I undersand you correctly, but shouldn't the following be doing what you want: SELECT y.* FROM table y WHERE y.somefield = (SELECT max(x.date_field) FROM table x WHERE ...) Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Combining several CTEs with a recursive CTE
Hello all, this is more a just curious question, rather than a real world problem. We can combine several CTEs into a single select using something like this: WITH cte_1 as ( select ), cte_2 as ( select ... where id (select some_col from cte_1) ) select * from cte_2; But this does not seem to work when a recursive CTE is involved WITH cte_1 as ( select ), recursive cte_2 as ( select ... where id (select some_col from cte_1) union all select ... ) select * from cte_2; This throws an error: syntax error at or near cte_2 I'm just wondering if this is intended behavioury, simply not (yet) implemented or even invalid according to the standard? I didn't find any reference that it's not allowed in the manual. Regards Thomas -- 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] Combining several CTEs with a recursive CTE
David Johnston, 20.09.2011 16:15: I'm just wondering if this is intended behavioury, simply not (yet) implemented or even invalid according to the standard? I didn't find any reference that it's not allowed in the manual. Regards Thomas Try sticking the recursive keyword after the with if any of the following CTEs are recursive. WITH RECURSIVE normal 1 AS () ,recursine1 AS () ,normal2 AS () ,recursine2 AS () SELECT ... David J. Ah! That does the trick. Thanks Thomas -- 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] Window function sort order help
Nicoletta Maia, 14.09.2011 10:30: SELECT `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` , MIN( `Y`.`history_timestamp` ) AS `start_time` FROM `Table` AS `X` JOIN `Table` AS `Y` ON `X`.`consumer_id` = `Y`.`consumer_id` AND `X`.`move_date` = `Y`.`move_date` AND `X`.`history_timestamp`= `Y`.`history_timestamp` WHERE NOT EXISTS ( SELECT * FROM `Table` AS `Z` WHERE `X`.`consumer_id` = `Z`.`consumer_id` AND `X`.`move_date` `Z`.`move_date` AND `X`.`history_timestamp`= `Z`.`history_timestamp` AND `Y`.`history_timestamp`= `Z`.`history_timestamp` ) GROUP BY `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` ORDER BY `X`.`consumer_id`, `X`.`history_timestamp` ASC With Y I select history_timestamp preceding the current row with the same move_date. With Z I verify that no changes have occurred to move_date between X.history_timestamp and Y.history_timestamp. That is not a valid PostgreSQL SQL statement. Postgres does not use backticks for quoting, it uses the standard double quotes. Thomas -- 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?
Bruce Momjian, 20.07.2011 03:03:
Well, to_char() is based on Oracle's to_char(). How does Oracle handle
such a date?
Oracle throws an error for the above example:
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
OK, it's a bug then. Let me see if I can find a fix for it.
Thanks for the info. I didn't know it was modelled after the Oracle
implementation.
Regards
Thomas
--
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?
Bruce Momjian, 19.07.2011 00:02:
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?
Oracle throws an error for the above example:
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
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] to_char() accepting invalid dates?
Hi,
I just noticed that to_char() will accept invalid dates such as 2011-02-31 and
adjust them accordingly:
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?
Regards
Thomas
--
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?
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.
Thomas
--
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] newbie question * compare integer in a where IN statement
Jose Ig Mendez, 13.07.2011 09:36:
Hi everybody,
I'm trying to compare in a sentence like this (using PostGres 8.3) :
select * from myTable where id_integer IN ('1,2,3,4')
I want to get the records which key id_integer is 1 or 2 or 3 or 4. the type od my
id, of course, is integer.
I've tried many differents ways but I cannot get the result I want.
I would like to cast the integer parameter I cannot change the part after IN it has to
be a string list.
Do I have to use a function ? I would like not to use it.
How can I compare a key (integer) with a lists of values ?
Just leave out the quotes:
select *
from myTable
where id_integer IN (1,2,3,4)
--
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] Function to total reset a schema
Surfing wrote on 29.05.2011 09:38: Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set to 0 each sequence. Btw, it could be good to execute a vacuum statement on each table, but from within the function this is not allowed. Is there a way to obtain the same result in another way (without using the vacuum)? If you are only using a single schema in your database, you could create an empty database with all your tables that you then use as the template database when creating a new one: Then each time you want to reset your schema (=database) you do a drop database real_database; create database real_database template template_database; Then you don't need to adjust your reset script if your database changes (you only maintain the template database using your SQL scripts) Thomas -- 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] ordering by date for each ID
Nguyen,Diep T wrote on 12.05.2011 03:59: Each ID can have different number of score counts. My goal is to add column order, which shows the order of the values in column date in descendant order for each property. The expected output will look like this: id | date | score_count | order +--+---+--- 13 | 1999-09-16 | 4| 4 13 | 2002-06-27 | 4| 3 13 | 2006-10-25 | 4| 2 13 | 2010-05-12 | 4| 1 65 | 2002-07-18 | 3| 3 65 | 2004-08-05 | 3| 2 65 | 2007-08-15 | 3| 1 86 | 2001-05-29 | 5| 5 86 | 2002-04-04 | 5| 4 86 | 2006-03-02 | 5| 3 86 | 2008-02-13 | 5| 2 86 | 2011-01-19 | 5| 1 Any help would be appreciated. SELECT id, date, score_count, row_number() over (partition by id order by date desc) as order_value FROM your_table -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Specifying column level collations
Hi,
I'm playing around with 9.1beta1 and would like to create a table where one
column has a non-default collation.
But whatever I try, I can't find the correct name that I have to use.
My database is initialized as follows:
postgres=# select version();
version
PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
(1 row)
postgres=# select name, setting
postgres-# from pg_settings
postgres-# where name in ('lc_collate', 'server_encoding', 'client_encoding');
name | setting
-+-
client_encoding | WIN1252
lc_collate | German_Germany.1252
server_encoding | UTF8
(3 rows)
Now I'm trying to create a table where one column's collation is set to french:
create table foo (bar text collate fr_FR) -- collation fr_FR for encoding
UTF8 does not exist
create table foo (bar text collate fr_FR.1252) -- collation fr_FR for encoding
UTF8 does not exist
create table foo (bar text collate fr_FR.UTF8) -- collation fr_FR for encoding
UTF8 does not exist
create table foo (bar text collate French_France.1252) -- collation
French_France.1252 for encoding UTF8 does not exist
So, how do I specify the collation there?
And is there a command to show me all available collations that I can use?
Thanks
Thomas
--
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] Specifying column level collations
Guillaume Lelarge wrote on 07.05.2011 14:02: create table foo (bar text collate fr_FR) -- collation fr_FR for encoding UTF8 does not exist create table foo (bar text collate fr_FR.1252) -- collation fr_FR for encoding UTF8 does not exist create table foo (bar text collate fr_FR.UTF8) -- collation fr_FR for encoding UTF8 does not exist create table foo (bar text collate French_France.1252) -- collation French_France.1252 for encoding UTF8 does not exist So, how do I specify the collation there? You first need to use CREATE COLLATION, such as: b1=# CREATE COLLATION fr (locale='fr_FR'); CREATE COLLATION Thanks for the quick answer. It seems there is something missing with my installation: postgres=# CREATE COLLATION fr (locale='fr_FR'); ERROR: could not create locale fr_FR: No such file or directory I used the ZIP distribution from EnterpriseDB (not the installer) so maybe the support for collations is simply not included with the plain binaries. postgres=# select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype --+---+---+--+-+--- default |11 |10 | -1 | | C|11 |10 | -1 | C | C POSIX|11 |10 | -1 | POSIX | POSIX (3 rows) Regards Thomas -- 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] Specifying column level collations
Tom Lane wrote on 07.05.2011 18:48: Thomas Kellererspam_ea...@gmx.net writes: My database is initialized as follows: postgres=# select version(); version PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit (1 row) I gather this is on Windows. Windows has its own notion of locale names, which look like this: lc_collate | German_Germany.1252 rather than the de_DE type of convention that's used by every other platform on the planet. There is not yet support in initdb for pre-populating pg_collation with Windows-style entries, so you will have to create your own entries. Presumably this would work for you, for instance: CREATE COLLATION german (locale='German_Germany.1252'); Ah! That did it. Thanks a lot. Regards Thomas -- 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] Oracle Equivalent queries in Postgres
Pavel Stehule, 16.02.2011 11:50: Try to use a standardized information_schema instead - these views are same on PostgreSQL and Oracle. Unfortunately they are not the same: Oracle does not support INFORMATION_SCHEMA Regards Thomas -- 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] Oracle Equivalent queries in Postgres
Pavel Stehule, 16.02.2011 12:20: Unfortunately they are not the same: Oracle does not support INFORMATION_SCHEMA sorry, I expected so all mature databases support it. Yes, this is really hard to understand. I would assume creating the INFORMATION_SCHEMA views based on the existing Oracle views is just a matter of maybe 3-4 days of days work. So it is really not understandable that Oracle does not support this. But then they probably don't care - after all it's Oracle. Regards Thomas -- 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 get row number in select query
Piotr Czekalski, 27.01.2011 16:21: Gentelmen, I follow this thread and I don't exactly get an idea of yours, but isn't is as simple as (example: table web.files contains one column named fileurl ): select row_number() over(), X.fileurl from (select fileurl from web.files order by fileurl) X The only disadvantage is that if you do want to order resultset you have to use select from select as numbers are added before order which may cause some performance troubles. You can get the row_number() without using the sub-select and without ordering the whole result as you can specify the order in the over() clause: select fileurl row_number() over (order by fileurl) from web.files Regards Thomas -- 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] sqlplus reporting equivalent in postgres?
Samuel Gendler wrote on 11.12.2010 04:23: psql - not as advanced, doesn't have all the features SQL*Plus has. On the other hand, it is at least capable of command history and readline support. Hmm, for me SQL*Plus does support command history, but this is getting off-topic now... Regards Thomas -- 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] Insert row in 1.10.1 and 1.10.3
ndias, 26.11.2010 17:22: When doing a insert row with less columns mentioned in into table(col1, col2, col3,... than the columns that exist on the table, on 1.10.1 it returns an error saying INSERT has more expressions than target columns (the error is translated so maybe the text is not exactly like this). Although, when this is insert is done on our test environment, where the version is 1.10.3 it works fine. The tables have the same columns and so on. What versions are you talking about? PostgreSQL is currently at 9.0.1, the previous version was 8.4.something I doubt there ever was a Version 1.10.3 of PostgreSQL Regards Thomas -- 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 collect text-fields from multiple rows ?
Andreas wrote on 16.10.2010 05:23: Hi, how can I collect text-fields from multiple rows into one output row? I'd like to do an equivalent to the aggregate function SUM() only for text. The input is a select that shows among other things a numerical column where I would like to group by. The text column of all rows in a group should get concatenated into 1 text devided by a '\n'. Even better would be if I could add a second text colum per line as topic. Input e.g. select group_nr::integer, memo::text, topic::text ... 1, 'bla ', 'weather' 2, 'yada..', 'weather' 2, 'talk talk..', 'cooking' 2, 'words words, ...', 'poetry' 3, Output: 1, 'weather\nbla...' 2, 'weather\nyada..\ncooking\ntalk talk..\npoetry\nwords words, ...' If you are on 9.0: SELECT group_nr, string_agg(memo||'--'||topic, '--') FROM the_table_with_no_name GROUP BY group_nr; On 8.x you need to user array_agg() SELECT group_nr, array_to_string(array_agg(memo||'--'||topic),'--') FROM the_table_with_no_name GROUP BY group_nr; -- 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] Create Datefield from 3 fields
Andreas Forø Tollefsen, 06.10.2010 13:11: Hi. I am trying to create a datefield using YEAR, MONTH and DAY fields of type integer. I tried this query, but it did not give good results: select to_date(gwsyear::text || gwsmonth::text || gwsday::text, '-MM-DD') FROM cshapes You are missing the dashes in the input string that you specify in the format mask to_date(gwsyear::text ||'-'|| gwsmonth::text ||'-'|| gwsday::text, '-MM-DD') Thomas -- 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 disaster - subquery error but delete continues
Greg Caulton, 10.09.2010 11:46: delete from form_record_details where form_record_id in (select form_record_id from forms where form_id= 40003656) Seems fine at 1am. However the subquery has a typo in it - there is no form_record_id in the forms table But rather than psql throwing an error... it deletes every row in the form_record_details table please tell me this is fixed since 8.3 This is not a bug. I assume there is a form_record_id in the table form_record_details. As you have not used table prefixes or aliases, you are simply referencing the form_record_id from the outer table in the subquery. Thomas -- 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 escape _ in select
Wes James, 28.07.2010 19:35: I'm trying to do this: select * from table where field::text ilike '%\_%'; but it doesn't work. How do you escape the _ and $ chars? The docs say to use \, but that isn't working. ( http://www.postgresql.org/docs/8.3/static/functions-matching.html ) The text between '%...%' can be longer, I'm just trying to figure out how to escape some things. I've found that ' works with '' and \ works with \\ To get around the somewhat quirky usage of backslashes, you can simply define a different esacpe character: select * from table where field::text ilike '%...@_%' escape '@'; Thomas -- 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] subtract two dates to get the number of days
Jean-David Beyer, 14.07.2010 19:05: I just looked them up in my data definitions. Dates are _stored_ as type DATE NOT NULL Very good ;) Yes, if the data happen to be stored at all. But when a program generates the dates dynamically and wants to produce queries from them, it is easier to use the C++ class to generate the dates. Yes of course. Years ago, I made a C++ data type that allowed a date datatype where I could add, subtract, and so on. I use it in programs that do not necessarily use a database, To be honest: I expect the programming language to support those things. I would love it. For all I know, the C++ Standard Library supports it now, but I do not believe it did when I wrote that class. Ah, those historic things. I primarily use Java and that had Date support right from the start. Well, €0.02 is still more than my US$0.02, I believe. Nice one :) Regards Thomas -- 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] subtract two dates to get the number of days
Jean-David Beyer wrote on 14.07.2010 14:37: My dates are of the form -mm-dd and such. Storing a date as a string is never a good idea. And I want to do things like adding or subtracting days, months, or years to it or from it. Also the logical comparisons. Which is all a piece of cake when you use the proper datatype Years ago, I made a C++ data type that allowed a date datatype where I could add, subtract, and so on. I use it in programs that do not necessarily use a database, To be honest: I expect the programming language to support those things. but also in programs that do when the computations are the big part of the cpu load, as contrasted to just gentle massaging of existing data. I would expect doing date maths with strings is wasting more CPU than using a native date datatype. Just my €0.02 Thomas -- 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] subtract two dates to get the number of days
Campbell, Lance, 13.07.2010 16:58: I want to subtract to dates to know the number of days different. Example: 01/02/2010 - 01/01/2010 = 1 day 08/01/2010 - 07/31/2010 = 1 day How do I do this? SELECT DATE '2010-02-01' - DATE '2010-01-01'; SELECT DATE '2010-08-01' - DATE '2010-07-31'; -- 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] Cant execute the query
Srikanth Kata wrote on 02.07.2010 14:24: When i am executing this query, i am facing the select s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner join account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner join (select subsno from getexpiringsubs($1,cast($2 as integer),cast($3 as double precision),$4) as (subsno int,expirydt timestamp without time zone,balcpt double precision)) as e on s.subsno=e.subsno where s.status=15 and d.domno=$5 order by d.domname,s.expirydt,a.actname Error is : ERROR: there is no parameter $1 LINE 5: inner join (select subsno from getexpiringsubs($1,cast($2 as... ^ ** Error ** ERROR: there is no parameter $1 SQL state: 42P02 Character: 295 Please suggest on this What about the answers you got here? http://www.dbforums.com/postgresql/1658082-cant-execute-query.html http://old.nabble.com/Unable-to-run-this-query-td29073430.html#a29073430 -- 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] is there a tutorial on window functions?
John, 11.06.2010 16:17: Hi, I'd like to learn the use of window functions and did not find a tutorial using google (postgres window function tutorial). I'm hoping someone has a link. There is one in the manual: http://www.postgresql.org/docs/current/static/tutorial-window.html Thomas -- 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 get localized to_char(DATE) output
Tom Lane, 06.05.2010 00:51: Thomas Kellererspam_ea...@gmx.net writes: I'm trying to get the output of the to_char(date, text) method in German but I can't get it to work: I think you need 'TMMon' to get a localized month name. regards, tom lane Ah! Silly me. Now that you write this I can see it in the manual ;) The manual says the value for lc_time is OS dependent and indeed set lc_time = 'German' does not work on Solaris. Is there a way to get a list of allowed values for lc_time for a specific installation? Thanks for your help Thomas -- 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 get localized to_char(DATE) output
Jasen Betts, 06.05.2010 11:57: The manual says the value for lc_time is OS dependent and indeed set lc_time = 'German' does not work on Solaris. Is there a way to get a list of allowed values for lc_time for a specific installation? man -k locale would be my starting point (for anything POSIXish) Looks like locale -a does it on linux, that may be worth a try. de_DE is the locale for German as used in Germany. Thanks for the answer. Is there a way to get this information from within a SQL statement? Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to get localized to_char(DATE) output
Hi, I'm trying to get the output of the to_char(date, text) method in German but I can't get it to work: My understanding is, that I need to set lc_time for the session in order to change the language used by to_char(), but this does not seem to work for me: postgres= select version(); version - PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit (1 Zeile) postgres= select to_char(current_date, 'Mon'); to_char - May (1 Zeile) postgres= set lc_time = 'German'; SET postgres= select to_char(current_date, 'Mon'); to_char - May (1 Zeile) postgres= Postgres' messages are in German, but not the output of to_char() After changing lc_time to 'German' I would have expected 'Mai' instead of 'May' What am I mising? Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with function returning a result set
Hi,
I'm playing around with functions returning result sets, and I have a problem
with the following function:
-- Create sample data
CREATE TABLE employee (id integer, first_name varchar(50), last_name
varchar(50));
INSERT INTO employee values (1, 'Arthur', 'Dent');
INSERT INTO employee values (2, 'Zaphod', 'Beeblebrox');
INSERT INTO employee values (3, 'Ford', 'Prefect');
COMMIT;
-- Create the function
CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar)
RETURNS TABLE(id integer, full_name text)
AS
$$
BEGIN
RETURN QUERY
SELECT id, first_name||' '||last_name
FROM employee
WHERE last_name LIKE name_pattern ||'%';
END
$$
LANGUAGE plpgsql;
COMMIT;
Now when I run:
SELECT *
FROM get_employees('D');
I get one row returned which is correct, but the ID column is null (but should
be 1). It does not depend which row(s) I select through the procedure. I also
tried to change the datatype of the returned id to int8 and an explicit cast in
the SELECT statement, but to no avail.
When I define the function using SQL as a language (with the approriate
changes), the ID column is returned correctly.
I'm using Postgres 8.4.3 on Windows XP
postgres= select version();
version
-
PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit
(1 row)
What am I missing?
Regards
Thomas
--
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] Problem with function returning a result set
Tom Lane, 08.04.2010 10:59: Thomas Kellererspam_ea...@gmx.net writes: CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) RETURNS TABLE(id integer, full_name text) AS $$ BEGIN RETURN QUERY SELECT id, first_name||' '||last_name FROM employee WHERE last_name LIKE name_pattern ||'%'; END $$ LANGUAGE plpgsql; I get one row returned which is correct, but the ID column is null (but should be 1). Don't name the parameter the same as the table column ... regards, tom lane I knew it was something simple I overlooked ;) Thanks for the quick response. Regards Thomas -- 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] count function alternative in postgres
junaidmalik14 wrote on 03.04.2010 14:58: Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct Thanks Your question has been answered several times already (including this list) http://archives.postgresql.org/pgsql-hackers/2010-04/msg00182.php http://archives.postgresql.org/pgsql-hackers/2010-04/msg00179.php http://forums.devshed.com/postgresql-help-21/count-function-alternative-in-postgres-691450.html http://www.dbforums.com/postgresql/1655165-count-function-alternative-postgres.html -- 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 Developer accessing PostgreSQL
Snyder, James, 29.03.2010 18:33: Hello, Is there a way to configure Oracle’s SQL Developer to access a PostgreSQL database? Thanks,Jim As others have pointed out, it's not possible. The Postgres Wiki contains a list of GUI Tools that work with Postgres: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools Thomas -- 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 syntax rowcount value as an extra column in the result set
Snyder, James, 29.03.2010 18:25: Thanks for all the dialog on this subject. My version was derived from the postgreSQL's .jar file (specifically named postgresql-8.4-701.jdbc4.jar) that I'm using. When I do the following: select version() I get the following: PostgreSQL 8.3.6 Then you cannot use the new windowing functions, you will need to upgrade to 8.4 Thomas -- 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 syntax rowcount value as an extra column in the result set
Jayadevan M, 26.03.2010 07:56: Thank you for setting that right. Apologies for not checking version. The orginal poster stated that he is using 8.4, so that solution will work for him. Thomas -- 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 syntax rowcount value as an extra column in the result set
Snyder, James wrote on 25.03.2010 22:33: I’m using PostgreSQL (8.4.701) There is no such version. The current version is 8.4.3 On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people The same syntax will work on Postgres Thomas -- 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] 8.4.1 distinct query WITHOUT order by
Gary Chambers wrote on 21.12.2009 23:15: The current maintainer is unsure about being able to do the right thing and recompile the code after fixing the query. Why not simply add the necessary GROUP BY? Thomas -- 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] pg_get_functiondef and overloaded functions
Tom Lane wrote on 30.10.2009 05:44:
select pg_get_functiondef('foo(int)'::regproc)
select pg_get_functiondef('foo(int4)'::regproc)
select pg_get_functiondef('foo(integer)'::regproc)
but each time I get the error: function foo(integer) does not exist
What am I missing?
You need to use regprocedure. regproc is mainly for bootstrap purposes
--- it accepts a function name only.
Thanks, works fine.
Where would I find a documentation of all those types?
The section about pg_get_functiondef (and others) doesn't mention this.
Regards
Thomas
--
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 information_schema to find about function parameters?
Mario Splivalo, 29.10.2009 17:51: I looked at the information_schema.routines, to get information about the functions in the database, but there doesn't seem to be a way to extract the parameters information about functions? Where would I seek for such information? They are stored as an array in pg_proc (proargnames, proallargtypes) http://www.postgresql.org/docs/current/static/catalog-pg-proc.html Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_get_functiondef and overloaded functions
Hi,
I'm playing around with the new pg_get_functiondef() function but I can't get it to work when I need to specify the argument list.
select pg_get_functiondef('foo'::regproc)
works without problems.
However if I have e.g. foo(int) and foo(int, int) I can't get this to work.
I tried
select pg_get_functiondef('foo(int)'::regproc)
select pg_get_functiondef('foo(int4)'::regproc)
select pg_get_functiondef('foo(integer)'::regproc)
but each time I get the error: function foo(integer) does not exist
What am I missing?
Regards
Thomas
--
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] Common table expression - parsing questions
the6campbells wrote on 29.09.2009 04:54: 2. Do you intend to remove the requirement to include the recursive keyword - as other vendors allow The standard *requires* the keyword. As far as I can tell there are two DBMS that require it (Postgres, Firebird) and two that don't (SQL Server and Oracle with the newest release) Thomas -- 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] CHECK constraint on multiple tables
Mario Splivalo wrote on 14.09.2009 16:20: Have you considered refactoring so there's only one table? Unfortunately I can't do that, due to the object-relational-mapper-wrapper-mambo-jumbo. You could still refactor that into one single table, then create two updateable views with the names that the dreaded OR mapper expects. That would enable you to have a proper unique check on the base table, and you OR mapper still sees two tables that it can update. Thomas -- 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] mysql code questions
Ray Stell wrote on 12.08.2009 20:19: http://www.brentozar.com/archive/2009/04/getting-the-most-recent-record/ How this works? What is ttNewer? What is a clustered primary key in mysql? That article talks about SQL Server not MySQL. select tt.* FROM TestTable tt LEFT OUTER JOIN TestTable ttNewer ON tt.id = ttNewer.id AND tt.create_date ttNewer.create_date WHERE ttNewer.id IS NULL; I would probably do it this way: SELECT tt. * FROM testtable tt WHERE create_date = (SELECT MAX(create_date) FROM testtable tt2 WHERE tt.id = tt2.id); Don't know which one is more efficient (with just a few rows, it doesn't really pay off to look at the execution plan) Thomas -- 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] Bit by commands ignored until end of transaction block again
Chris, 23.07.2009 09:06: psql -d dbname .. # select now(); now --- 2009-07-23 17:04:21.406424+10 (1 row) Time: 2.434 ms (csm...@[local]:5432) 17:04:21 [test] # savepoint xyz; ERROR: SAVEPOINT can only be used in transaction blocks (csm...@[local]:5432) 17:04:25 [test] You haven't explicitly started a transaction, therefore savepoints won't work. Django (it seems) just issues queries with no knowledge of (and no way to support) them. The above situation only arises if you run in autocommit mode which is the default for psql (which I have *never* understood). If you do a \set AUTOCOMMIT off, then you can set a savepoint without using BEGIN. I have this in my psqlrc.conf and your example looks like this on my computer: c:\Temppsql training thomas psql (8.4.0) Type help for help. training= select now(); now 2009-07-23 09:30:55.791+02 (1 row) training= savepoint abc; SAVEPOINT training= release abc; RELEASE training= I don't believe any serious ORM would run in autocommit mode, so that shouldn't be a problem. Thomas -- 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] WITH RECURSION output ordering with trees
Philippe Lang, 10.07.2009 11:10:
Hi,
I'm playing with the new WITH RECURSIVE feature of 8.4. I'm trying to
figure out how to use it with trees.
Here is the test code I use:
I'd like to perform a real recursion, and show the tree structure in a
more appopriate way, like this:
Any idea how to do that? (without trying to sort on the lookup column,
whose values can be random outside this test)
The manual has a nice hint on this adding up IDs to generate a path like column that can be used for sorting.
Try the following:
WITH RECURSIVE parse_tree (depth, id, lookup, parent_id, sort_path) AS
(
SELECT 0,
parent.id,
cast(parent.lookup as text),
parent.parent_id,
array[0] as sort_path
FROM recursion_sample parent
WHERE parent_id IS NULL
UNION ALL
SELECT
parent.depth + 1,
child.id,
rpad(' ', depth * 2) || child.lookup,
child.parent_id,
parent.sort_path || child.id
FROM parse_tree parent JOIN recursion_sample child on child.parent_id =
parent.id
)
select id, lookup
from parse_tree
order by sort_path
;
This will output:
id | lookup
-+
1 | a1
2 | b11
243 | c113
645 | c111
823 | c112
6 | b12
583 | c122
845 | c121
9 | b13
10 | c131
(10 rows)
Thomas
--
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] it's not NULL, then what is it?
Steve Crawford wrote on 01.07.2009 00:39: canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; I believe count will only count not-null Correct SELECT count(some_col) FROM some_table; is the same as SELECT count(*) FROM some_table WHERE some_col IS NOT NULL; -- 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] Comparing two tables of different database
Nicholas I, 29.04.2009 08:39: Hi, can anybody me suggest me, how to compare two tables of different database. Do you want to compare the data or the structure of the two tables? Thomas -- 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] Permanent alias for postgresql table
Marco Lechner, 12.03.2009 13:59: Hi list, I'm searching for a way to create permanent alias for tablenames in postgresql. We are storing various versions of a routable network in postgresql (postgis, pgrouting) and access a certain version with a bunch of php-skripts. We like to use aliases for the currently used tables oo be able to relink the current tables rapidly by changing the alias target. Any idea - or is this approach nonsense? A view? CREATE VIEW constant_table_name AS SELECT * FROM current_table Thomas -- 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] Permanent alias for postgresql table
Marco Lechner, 12.03.2009 15:26: Hi Mina, thanks for your answer. I thought about that, but don't views decrease performance, because they are calculated on access? I'm not sure what you mean with calculated. A view is just a SQL query. There is no difference in executing the SQL query that's behind a view or the view itself. Except for the minimal time it takes to retrieve the view definition. But I would never sacrifice easy of development or usage for the microseconds of overhead the VIEW generates. And the overhead (if at all) will be neglectable compared to the time it takes to return the result. Thomas -- 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] store pdf files
ivan marchesini wrote on 09.12.2008 11:11: Hi to all... I need to create a db that contain link to some pdf files.. At the moment these are simple links (to the files that are stored into the file system) storing paths into a column of a dbf table... I need to manage this data considering that the db I'm going to create will be moved in some months to another server... so I think that the link to the files positions into the file system isn't a valid solution... can you suggest me a better way to manage this pdf data?? Each pdf is quite small (100k) should I use BLOB? I have made quite good experience storing BLOBs (bytea in Postgres) inside the database. I do like the transactional safety I get from the database and the fact that the database an handle several ten thousands of documents in a single table without a problem. With a file based solution you'll need to find a way to distribute the PDFs over different directories to ensure that the file count in each directory doesn't get too high (at least we had some problems storing about 50.000 documents (Word, pdf, ...) on a HP/UX machine in a single directory. Especially when trying to access that directory via ftp... Thomas -- 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] fast insert-if-key-not-already-there
Patrick Scharrenberg, 04.08.2008 17:51: Hi! I have to do much inserts into a database where the key most often is already there. My current approach is to query for the key (ip-address), and if the result is null I do the insert. For every IP-Address I need the ip_addr_id from the same table. [...] Now I'm wondering if there is a better solution, since I'm doing ~20 inserts at once and every time I'm doing single lookup's for the IDs. If you know that most of the time the record is already there, I simply execute the UPDATE, then check how many rows were updated. If that returns zero, I'll send the INSERT Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] xpath_table
Hi,
I am using xpath_table to convert elements from an XML column to rows.
Now according to
http://www.postgresql.org/docs/8.3/static/xml2.html
this function will be removed in a future version.
That chapter also claims that the new XML syntax covers the functionality of
the xml2 module, but I cannot find a way to return the elements of an XML
document as rows (as xpath_table does)
Suppose I have the following content in my xml column:
team
member id=10 name=Arthur Dent/
member id=11 name=Ford Prefect/
/team
I am using a statement similar to this:
select member_id, member_name
from xpath_table('id', 'xml_text', 'xmltest',
'/team/member/@id|/team/member/@name', 'true')
as t(id integer, member_id varchar, member_name varchar)
to get the following output
member_id member_name
10 Arthur Dent
11 Ford Prefect
How would I achieve the same without using the deprecated xml2 module?
Thanks in advance
Thomas
--
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] Protection from SQL injection
Thomas Mueller wrote on 26.04.2008 18:32: Literals can still be used when using query tools, or in applications considered 'safe'. I fail to see how the backend could distinguish between a query sent by a query tool and a query sent by an application. Thomas -- 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 updating dataset the record goes to the end of the dataset
Nacef LABIDI, 22.04.2008 11:54: Hi all, I am using Postgres in a Delphi application through ODBC. I am having an issue with updating records. When I create a dataset to get the records in a table then after I update one of these records and then refresh the dataset, the record goes to the end of the dataset. This is disappointing when editing records on a DBGrid, where users find their updated records jump to the end of the grid. Even after restarting the application, the updated record keeps showing at the end of the grid. I have tried the same thing with SQL Server and it works normally, so I thought it was a postgres behvior. Has anyone an idea about what could be the cause of such a behavior. Rows in a relational database are not sorted. This is true for any RDBMS. If you want to apply a certain sort order you *have* to use an ORDER BY clause for your SELECT. If the rows show up in the order you expect without an ORDER BY this is pure coincidence (even with SQL Server) The simply solution is to add an ORDER BY that sorts e.g. by a timestamp that is set when the row is created. Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Having a mental block with (self) outer joins
Hi, I'm playing around with putting a hierarchy of items into the database. But for some reason I'm having a mental block understanding the following: I have a table category with id and parent_id implementing the typical adjacency model. To get the first two levels of the hierarchy I use: SELECT t1.name as lev1, t2.name as lev2 FROM category t1 LEFT JOIN category t2 ON t2.parent = t1.id WHERE t1.name = 'ROOT' ; Now what I don't understand is that the root node (which has a NULL parent_id) is not selected. My understanding from the outer join is that it would return all items from the left tables regardless whether they have a corresponding row in the right table. So given the data name, id, parent_id ROOT, 1, NULL CHILD1, 2, 1 CHILD2, 3, 1 I would have expected the following result: ROOT, NULL ROOT, CHILD1 ROOT, CHILD2 but the row with (ROOT,NULL) is not returned. I'm sure I'm missing something very obvious, but what? Thanks in advance Thomas -- 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] Having a mental block with (self) outer joins
hubert depesz lubaczewski, 21.04.2008 16:05: ROOT, 1, NULL CHILD1, 2, 1 CHILD2, 3, 1 I would have expected the following result: ROOT, NULL ROOT, CHILD1 ROOT, CHILD2 but the row with (ROOT,NULL) is not returned. why would you expect it? the columns are: parent and child (on your output). you dont have any row that has *parent_id = 1* and id = NULL. Ah, of course that's where my mental block was ;) Thanks for the quick response you can get this output though: NULL, ROOT ROOT, CHILD1 ROOT, CHILD2 with this query: select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id If the table contains more levels (i.e. child1 being the parent of another item) this bring others back as well. And I was trying to retrieve the full path for each item (I do know the max. number of levels) Cheers, and thanks a lot for the quick response Thomas -- 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] Columns view? (Finding column names for a table)
Steve Midgley wrote on 06.02.2008 21:33: Hi, I see this documentation item but can't figure out how to use it: http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html The view columns contains information about all table columns (or view columns) in the database. select column_name from information_schema.columns where table_name = 'table_name' works for me. Thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Removing whitespace using regexp_replace
Hi, I have a column with the datatype text that may contain leading whitespace (tabs, spaces newlines, ...) and I would like to remove them all (ideally leading and trailing). I tried SELECT regexp_replace(myfield, '\A\s*', '') FROM mytable; (for leading whitespace, to start with) But it does not remove anything. I replace my first attempt '^\s*' with '\A\s*' after reading the chapter about newline-sensitive matching, but that doesn't seem to do the trick either. Just for a test I changed this to SELECT regexp_replace(myfield, '\s*', '') FROM mytable; and expected *all* whitespace to be removed from my string, but only the leading ones were replaced. Which I don't understand at all. Why weren't other whitespace sequences not replaced with that expression? What would be the correct RE to replace leading and trailing whitespace without affecting anything inbetween? I'm pretty sure I'm missing someting very obvious... Thanks in advance Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Removing whitespace using regexp_replace
Andreas Kretschmer wrote on 28.10.2007 12:42: I have a column with the datatype text that may contain leading whitespace (tabs, spaces newlines, ...) and I would like to remove them all (ideally leading and trailing). You can use trim() for that: select 'x' || trim(both '\t' from trim(both ' ' from ' \t\tfoo bar ')) || 'x'; (for testing with 'x' around the result) Yes I was thinking about a solution like that as well, but wouldn't that only work if the order in which spaces and tabs appear is always the same? The above would replace ' \t' but not '\t ', right? For regexp_replace() you need an extra parameter 'g' like below: Cool, works like a charm. Didn't see that parameter when first reading that chapter. But it seems my problem was actually caused by something else: SELECT regexp_replace(myfield, '\s*', '', 'g') FROM mytable; does not replace anything, but SELECT regexp_replace(myfield, '[ \t\n\r]*', '', 'g') FROM mytable; does replace all whitespaces (as I expected). And subsequently SELECT regexp_replace(myfield, '^[ \t\n\r]*', '', 'g') FROM mytable; replaces only the whitespace at the beginning. I thought \s is a shortcut for whitespace, which in my understanding is the same as [ \t\r\n]. Am I wrong here? Cheers Thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Removing whitespace using regexp_replace
Andreas Kretschmer wrote on 28.10.2007 13:32: But it seems my problem was actually caused by something else: SELECT regexp_replace(myfield, '\s*', '', 'g') FROM mytable; you should escape the \, change to ...'\\s*'... Ah! Didn't think this was necessary, as \t or \n did not need to be escaped. But without anchors this replaces all whitespaces, also within the text and not only at the beginning/end (^ and $) Yes of course, this was only for testing ;) Thanks for your help! Thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Make a SQL statement not run trigger
Jon Collette wrote on 21.08.2007 23:26: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? What we have done once, was to include a column in the table for this purpose. If a special value for the column was provided during UPDATE or INSERT, the trigger would immediately terminate, not doing any work. Thus the trigger still fires every time, but simply won't do nothing. Not very elegant, but worked for our environment. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to analyse the indexes in postgres?
Karthikeyan Sundaram wrote on 18.02.2007 09:15: Hi, I am new to postgres. I need some kind of template script or advise on how to analyse the indexes. In our database, we do delete, insert, update tons of rows. http://www.postgresql.org/docs/8.2/interactive/routine-reindex.html ---(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] Removing duplicate rows
Paul Lambert wrote on 06.02.2007 23:44: Sort on Weenblows is a bastard to work with, and I don't believe it has a unique option. I probably should have mentioned this was on Weenblows. You can get all (or most?) of the *nix/GNU commandline tools for Windows as well. As native Win32 programs that do not require a pseudo *nix (aka as Cygwin) to run in: http://gnuwin32.sourceforge.net tsort and uniq are part of the coreutils package Thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SEQUENCES
Rodrigo Sakai wrote on 02.10.2006 18:39: Hi all, I need to get all sequences and their respective current values! Is there any catalog table or any other away to get this??? Quote from the manual at: http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences, views, composite types Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updatable views: any decent front-ends?
Bath, David wrote on 24.11.2005 23:57: While I can happily create rules on views to allow inserts, updates and deletes, I can't find a GUI front-end that understands that the view allows record edits that I can run on linux (whether through X or web-based doesn't matter) and simply open the relation and edit data without designing horrible forms with lots of code. I note that MS-Access allows this, as it asks for the field(s) that are unique and can be used for updating when you attach a table, but I don't have (or want) a Windows box or MS-Office. So 1) Does anybody know of a tool that allows easy editing of data in views? You might want to try my SQL Workbench: http://www.sql-workbench.net It will not auto-detect the key columns for updating the view, but it will allow you to select them manually if needed (for update/delete). Maybe I'll add automatic detection of the keys in the near future (if I find the time) It is written in Java (Swing) and thus should (and does) work on Linux. Whether it qualifies as decent is up to you :) Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
