[GENERAL] restruct cash table
Hello, I have a small problem with my table: I have a table for my cash and i do several actions in it. I receive money in different forms, I expend money in different forms etc. And do each action I have various foreign keys. But they are not all in use in every column. I don't know how I can structure the table better: CREATE TABLE cash ( id serial, type text NOT NULL, account text NOT NULL, value numeric(10,2) NOT NULL, item text, department text, person text, description text ); In this table is data like this: ID | type | account | value | item | department | client | ---+--+-+-+---+--+--+ 1 | sale | acc_1 | 10,00 | paper | department_1 | NULL | 2 | deposit | acc_1 | 15,00 | NULL | NULL | client_1 | 3 | deposit | acc_2 | 25,00 | NULL | NULL | client_2 | 4 | transfer | acc_2 | -15,00 | NULL | NULL | NULL | 5 | transfer | acc_1 | 15,00 | NULL | NULL | NULL | 6 | purchase | acc_2 | -12,00 | NULL | department_3 | NULL | 7 | usage| NULL| -1,00 | page | | client_1 | 8 | usage| NULL|1,00 | page | department_2 | NULL | 9 | usage| NULL| -1,00 | page | department_1 | NULL | 10 | usage| NULL|1,00 | page | department_2 | NULL | Is there a better way to store this actions? (type, account, item, department and client have all its own tables) Thanks, Gerhard signature.asc Description: Digital signature
Re: [GENERAL] Equivalent for AUTOINCREMENT?
Halle Craig, Am 2008-11-05 20:37:31, schrieb Craig Ringer: If you really, truly need gapless sequences, there are some options. I posted about them recently on another thread. The archives will contain that post and many others from many people on the same topic. Be aware, though, that gapless sequences have some NASTY performance consequences. Since this NASTY performance consequences would only hit the INSERT statement and it is very unlikely that I have concurence WRITE/INSERT access, it is a minor problem. Design your application not to expect your primary keys to be gapless. If it requires contiguous sequences for something, generate them at query time instead of storing them as primary keys. If the contiguous sequence numbers must also be stable over the life of the record, try to redesign to avoid that requirement if at all possible. Yes it is a requirement... and this is, why I have tried to get the highest value of the column serno. CREATE TABLE id_counter ( last_used INTEGER NOT NULL ); INSERT INTO id_counter ( last_used ) VALUES ( -1 ); -- UPDATE id_counter SET last_used = last_used + 1; -- INSERT INTO sometable ( id, blah ) VALUES ( (SELECT last_used FROM id_counter), 'blah'); Thank you for the example I will try it out now. Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Redefining an existing Table Schema for Foreign Key Constraint - Question
Hallo Harald, Am 2008-11-03 13:41:52, schrieb Harald Fuchs: In article [EMAIL PROTECTED], Brian714 [EMAIL PROTECTED] writes: Customers Table id:integer -- primary key first_name:varchar(50) last_name:varchar(50) cc_id:integer references Creditcards.id address:varchar(200) email:varchar(50) password:varchar(20) This is the usual 1:n relationship, but I think you got it backwards. There are two questions to ask: 1. Are there customers with more than one credit card? This could be a problem for the above table... 2. Are there credit cards owned by more than one customer? CreditCards are personaly and sometimes (in France) I need an ID card to prove, that I am the holder... So how can one credit card can have more then one owner? Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] UPDATE tuples with a sub-select
On Fri, Nov 07, 2008 at 11:15:07AM +0100, Thomas Kellerer wrote: Hi, are there any plans to support updating a tuple using a sub-select in one of the future versions. e.g, something like: UPDATE report_table SET (order_count,order_value) = (SELECT count(*), sum(amount) FROM order o WHERE o.customer_id = report_table.customer_id); What about: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o WHERE o.customer_id = report_table.customer_id) Its untested, but I think, it must works like this. I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on the roadmap Regards Thomas Regards, Gerhard signature.asc Description: Digital signature
Re: [GENERAL] avoiding seq scan without duplicating
Andrus [EMAIL PROTECTED] writes: Simple query is slow, performs seq scan while index exists: explain select count(*)::integer as cnt from firma2.dok where dokumnr in (17,2) and dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE alusdok='LF' -- and dokumnr in (17,2) ) Index is used if join condition is duplicated in subquery: explain select count(*)::integer as cnt from firma2.dok where dokumnr in (17,2) and dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE alusdok='LF' and dokumnr in (17,2) ) The proposed transformation is not correct because of the odd behavior of NOT IN with respect to nulls. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE tuples with a sub-select
On Fri, Nov 07, 2008 at 02:31:42PM +0100, Thomas Kellerer wrote: Gerhard Heift, 07.11.2008 13:35: are there any plans to support updating a tuple using a sub-select in one of the future versions. e.g, something like: UPDATE report_table SET (order_count,order_value) = (SELECT count(*), sum(amount) FROM order o WHERE o.customer_id = report_table.customer_id); What about: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o WHERE o.customer_id = report_table.customer_id) Its untested, but I think, it must works like this. Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) ) But unfortunately it gives an error: ERROR: subquery in FROM cannot refer to other relations of same query level [SQL State=42P10] Ok, its a little bit more complicated: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT customer_id, count(*) AS s_count, sum(amount) AS s_value FROM order o GROUP BY customer_id) AS summary WHERE summary.customer_id = report_table.customer_id) Regards Thomas Regards, Gerhard signature.asc Description: Digital signature
Re: [GENERAL] avoiding seq scan without duplicating
Tom, The proposed transformation is not correct because of the odd behavior of NOT IN with respect to nulls. Thank you. In this particular case dokumnr is dok table primary key of type int. bilkaib.dokumnr can contain nulls but this does not affect to result probably. So in this case this conversion is probably correct. Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Equivalent for AUTOINCREMENT?
Michelle Konzack wrote: Halle Craig, Am 2008-11-05 20:37:31, schrieb Craig Ringer: If you really, truly need gapless sequences, there are some options. I posted about them recently on another thread. The archives will contain that post and many others from many people on the same topic. Be aware, though, that gapless sequences have some NASTY performance consequences. Since this NASTY performance consequences would only hit the INSERT statement and it is very unlikely that I have concurence WRITE/INSERT access, it is a minor problem. And DELETE. And anything that happens in the same transaction after the INSERT or DELETE that touches the table with the gapless sequence. It'll probably be OK if you keep the transactions that modify the table with the gapless sequences as short as possible, preferably doing nothing except the modification in question. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] sum timestamp result in hours
select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento)) from atendimentopublico.registro_atendimento rgat inner join cadastro.localidade loca on loca.loca_id=rgat.loca_id inner join atendimentopublico.solicitacao_tipo_especificacao step on step.step_id=rgat.step_id where date(rgat_tmregistroatendimento) between '2007-01-01' and '2007-12-31' and rgat.step_id in (706,710,717,718,719,722,725,728,730,732,733,720,721,727,714,729) and rgat.rgat_cdsituacao=2 and date(rgat_tmencerramento) between '2007-01-01' and '2007-12-31' and rgat.rgat_tmencerramento rgat.rgat_tmregistroatendimento and rgat.loca_id=339 and rgat.rgat_id in (20111305, 30102409, 30102096, 30097214, 30102396, 20057815, 20325895) Result: 1 year 4 mons 88 days 51:42:00 I need help to view result query only in hours, the timestamp columns in bold above. thanks Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com. http://br.new.mail.yahoo.com/addresses
Re: [GENERAL] How to use index in WHERE int = float
On Thu, Nov 06, 2008 at 12:08:57AM +0200, Andrus wrote: PG 8.3 would even throw it out, unless dokumnr was explicitly cast to a float8 as well. I tried in 8.3 create temp table dok ( dokumnr serial primary key ); select * from dok where dokumnr='1'::float8 and this run without error. Hum, it doesn't seem to does it. Sorry, I was thinking PG was more strict than it appears to be. So i do'nt understand how 8.3 throws out. It won't, I was getting confused! Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE tuples with a sub-select
Tom Lane, 07.11.2008 14:33: Thomas Kellerer [EMAIL PROTECTED] writes: are there any plans to support updating a tuple using a sub-select in one of the future versions. It's the first item under UPDATE on the TODO list ... That is good news :) Thanks Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to design a customer TABLE which hold credit card infos and other payments?
* Do not Cc: me, because I READ THIS LIST, if I write here * *Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe* Hello, I am coding a new OnlineStore (the existing ones fit not my needs, are to complicate to use or simply closed source and too expensive e.g. InterShop) with an integrated powerful ledger. So now it comes to infos about Credit Cards, PayPal and friends... Does someone have an experience with it 1) how I should store it 2) how the TABLE fields should look like And yes, I would use a reference to external tables instead of puting those data in the main customer TABLE... Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] How to design a customer TABLE which hold credit card infos and other payments?
On Thu, Nov 6, 2008 at 2:43 PM, Michelle Konzack [EMAIL PROTECTED] wrote: * Do not Cc: me, because I READ THIS LIST, if I write here * *Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe* Sorry, it's how this list works. If you don't want that, there are some options for majordomo you can set to alleviate the issue. I'm not changing how I reply to the list just for you. Hello, I am coding a new OnlineStore (the existing ones fit not my needs, are to complicate to use or simply closed source and too expensive e.g. InterShop) with an integrated powerful ledger. So now it comes to infos about Credit Cards, PayPal and friends... If you are storing credit card data then you must follow the PCI standards for doing so. Look them up on the web and get a copy. Failure to follow their security guidelines will result in you not being allowed to process or handle credit cards. That said, the best way to store them is to not store them. If you still have to, then use some kind of encryption using the user's password as part of the key, and don't store the user's password, only an md5 of it. Also, store the password on one machine, encrypted, do the encryption decryption on another machine. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database access over the Internet...
On Thu, Nov 6, 2008 at 2:27 PM, Michelle Konzack [EMAIL PROTECTED] wrote: * Do not Cc: me, because I READ THIS LIST, if I write here * *Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe* Again, you get to look up the options for major domo to alleviate the problem. ...because using 2-3 differnet databases sucks! Agreed. especially if they are supposed to hold the same data set. After IRC'ing with some peoples and discusing about PostgreSQL, MySQL, SQlite, Oracle Informix and others I like to know, if you use a database access over the Internet what your experience is with... Depends. I've used extranet connections that worked quite well. If you can talk your hosting provider(s) into setting them up for you they're still way cheaper than having monstrous database servers all over the place. You can also look into using a VPN connection between hosting centers. This would go across the regular old internet but have the advantage of making it look, from a networking perspective, like your machines were sitting next to each other (except for the increase in latency of course). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to design a customer TABLE which hold credit card infos and other payments?
Hello, Scott Marlowe wrote: On Thu, Nov 6, 2008 at 2:43 PM, Michelle Konzack [EMAIL PROTECTED] wrote: * Do not Cc: me, because I READ THIS LIST, if I write here * *Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe* Sorry, it's how this list works. If you don't want that, there are some options for majordomo you can set to alleviate the issue. I'm not changing how I reply to the list just for you. Hello, I am coding a new OnlineStore (the existing ones fit not my needs, are to complicate to use or simply closed source and too expensive e.g. InterShop) with an integrated powerful ledger. So now it comes to infos about Credit Cards, PayPal and friends... If you are storing credit card data then you must follow the PCI standards for doing so. Look them up on the web and get a copy. Failure to follow their security guidelines will result in you not being allowed to process or handle credit cards. That said, the best way to store them is to not store them. If you still have to, then use some kind of encryption using the user's password as part of the key, and don't store the user's password, only an md5 of it. Also, store the password on one machine, encrypted, do the encryption decryption on another machine Try to avoid storing any card and card holder info, and you definitely shouldn't keep in DB the whole data required to authorize transaction. Just take in mind how dangerous this info could be in case of security leak. -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, ODBCng - http://www.commandprompt.com/
[GENERAL] Importing text file into a TEXT field
Hello, Is there a way to easily import a relatively huge text file into a table column? I'd like to use psql and I`'ve looked at lo_* commands and I can't figure how to import my text file into my TEXT column. My last solution is to write a little script to load my text file in a var and then insert to databse. Thanks Bruno Lavoie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] avoiding seq scan without duplicating
Simple query is slow, performs seq scan while index exists: explain select count(*)::integer as cnt from firma2.dok where dokumnr in (17,2) and dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE alusdok='LF' -- and dokumnr in (17,2) ) Aggregate (cost=152063.71..152063.73 rows=1 width=0) - Bitmap Heap Scan on dok (cost=152055.67..152063.71 rows=1 width=0) Recheck Cond: ((dokumnr = 17) OR (dokumnr = 2)) Filter: (NOT (hashed subplan)) - BitmapOr (cost=4.01..4.01 rows=2 width=0) - Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00 rows=1 width=0) Index Cond: (dokumnr = 17) - Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00 rows=1 width=0) Index Cond: (dokumnr = 2) SubPlan - Seq Scan on bilkaib (cost=0.00..152034.41 rows=6902 width=4) Filter: (alusdok = 'LF'::bpchar) Index is used if join condition is duplicated in subquery: explain select count(*)::integer as cnt from firma2.dok where dokumnr in (17,2) and dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE alusdok='LF' and dokumnr in (17,2) ) Aggregate (cost=870.45..870.46 rows=1 width=0) - Bitmap Heap Scan on dok (cost=862.41..870.44 rows=1 width=0) Recheck Cond: ((dokumnr = 17) OR (dokumnr = 2)) Filter: (NOT (hashed subplan)) - BitmapOr (cost=4.01..4.01 rows=2 width=0) - Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00 rows=1 width=0) Index Cond: (dokumnr = 17) - Bitmap Index Scan on dok_dokumnr_idx (cost=0.00..2.00 rows=1 width=0) Index Cond: (dokumnr = 2) SubPlan - Bitmap Heap Scan on bilkaib (cost=4.77..858.39 rows=3 width=4) Recheck Cond: ((dokumnr = 17) OR (dokumnr = 2)) Filter: (alusdok = 'LF'::bpchar) - BitmapOr (cost=4.77..4.77 rows=219 width=0) - Bitmap Index Scan on bilkaib_dokumnr_idx (cost=0.00..2.38 rows=110 width=0) Index Cond: (dokumnr = 17) - Bitmap Index Scan on bilkaib_dokumnr_idx (cost=0.00..2.38 rows=110 width=0) Index Cond: (dokumnr = 2) how to make query fast without repeating join condition two times in query ? Andurs. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Semi-customized queries? Subset of SQL? Accessing the parser? Injection?
Am 2008-11-04 11:12:05, schrieb Webb Sprague: If they're that smart, they're smart enough to deal with SQL, and likely to be frustrated by a like-sql-but-not command language or a GUI query designer. Instead, create a user that only has enough access to read data (and maybe create temporary tables) and use that user to give them a sql commandline. It'll be drastically less development effort for you, and the end result is less likely to frustrate your users. Can't do that. (Or I wouldn't have asked the question.) Need a WWW interface, period. And where is the problem? Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Importing text file into a TEXT field
On Fri, Nov 07, 2008 at 11:15:43AM -0500, Bruno Lavoie wrote: Is there a way to easily import a relatively huge text file into a table column? How big is relatively huge? I'd like to use psql and I`'ve looked at lo_* commands and I can't figure how to import my text file into my TEXT column. the lo_* commands are for working with large objects; these have somewhat unusual semantics compared to the normal data in columns in PG. If you're routinely expecting files of more than, say, one MB then they're probably a good way to go, but it's a lot more work getting them going in the first place. My last solution is to write a little script to load my text file in a var and then insert to databse. If you want to just get the data into a TEXT column as quickly as possible; I'd probably just write a little bit of code to perform the escaping that PG requires on the file. You can then simply do: COPY tbl (col) FROM '/path/to/escaped/file'; I'm not sure if this is really what you want though! Enormous TEXT columns can be a bit fiddly to work. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redefining an existing Table Schema for Foreign Key Constraint - Question
In article [EMAIL PROTECTED], Michelle Konzack [EMAIL PROTECTED] writes: Hallo Harald, Am 2008-11-03 13:41:52, schrieb Harald Fuchs: In article [EMAIL PROTECTED], Brian714 [EMAIL PROTECTED] writes: Customers Table id:integer -- primary key first_name:varchar(50) last_name:varchar(50) cc_id:integer references Creditcards.id address:varchar(200) email:varchar(50) password:varchar(20) This is the usual 1:n relationship, but I think you got it backwards. There are two questions to ask: 1. Are there customers with more than one credit card? This could be a problem for the above table... 2. Are there credit cards owned by more than one customer? CreditCards are personaly and sometimes (in France) I need an ID card to prove, that I am the holder... So how can one credit card can have more then one owner? That's exactly why I told you I think you got it backwards. You need a cust_id column in your CreditCards table, not a cc_id column in your Customers table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to design a customer TABLE which hold credit card infos and other payments?
Oh, btw, the wikipedia page on the PCI standard I mentioned http://en.wikipedia.org/wiki/PCI_DSS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE tuples with a sub-select
Thomas Kellerer [EMAIL PROTECTED] writes: are there any plans to support updating a tuple using a sub-select in one of the future versions. It's the first item under UPDATE on the TODO list ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE tuples with a sub-select
Gerhard Heift, 07.11.2008 13:35: are there any plans to support updating a tuple using a sub-select in one of the future versions. e.g, something like: UPDATE report_table SET (order_count,order_value) = (SELECT count(*), sum(amount) FROM order o WHERE o.customer_id = report_table.customer_id); What about: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o WHERE o.customer_id = report_table.customer_id) Its untested, but I think, it must works like this. Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) ) But unfortunately it gives an error: ERROR: subquery in FROM cannot refer to other relations of same query level [SQL State=42P10] Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sum timestamp result in hours
On Fri, Nov 07, 2008 at 06:10:15AM -0800, paulo matadr wrote: select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento)) from atendimentopublico.registro_atendimento rgat [... lots of complicated and irrelevant SQL] Result: 1 year 4 mons 88 days 51:42:00 I need help to view result query only in hours, the timestamp columns in bold above. a lot of people don't have clients that are capable of displaying bold text hence the above query is somewhat meaningless. it appears easy to express the above question with resorting to strange formatting which would exclude less people from helping. Stating the question more simply would also help; I'd interpret your request as being something like: I'm trying to get the sum of differences between two timestamp columns, the query should return the number of hours in total. I've tried this but it doesn't work: SELECT sum(age(datecol1,datecol2) FROM tbl; as it gives me 1 year 4 mons 88 days 51:42:00 and I can't figure out how to turn this into a total number of hours. If I've interpreted your request correctly; I think you don't want to use the age() function, just a simple subtraction would do. You can then use the extract() function to pull the resulting interval apart and get your result. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Equivalent for AUTOINCREMENT?
Hi again, Am 2008-11-05 20:13:40, schrieb Craig Ringer: Michelle Konzack wrote: OK, you hit me, I am trying to convert a mysql scheme to postgresql... OK, in the same time I am trying to make programs like os-commerce PostgreSQL usable Realy, I hate programs which relay on ONE database only and can not easyly adapt to use another one... For example, if you're thinking about concurrency issues you'll find differences between databases in transaction isolation, visibility, locking, handling of deadlocks, etc. You'll potentially be able to use the database much more efficiently, reliably, and smoothly if you design to one particular RDBMS's concurrency features and behaviour, rather than trying to make it universal. In fact, making it universal may end up being the same thing as serializing absolutely everything. This isn't generally something you can just hide behind a database abstraction layer unless you're prepared for miserable performance and ugly, ugly ways of doing things. My major problem is, that I am using PostgreSQL since I think 6.4 (it was March 1999) and my databases are between 20 MBytes and several TBytes and some of my hosting providers (I have 8 worldwide) use ONLY MySQL and want install PostgreSQL. And no, I do not realy use very High-Complex Database operation which can not ported to anoter databases. The ONLY real option would be, go to a HostingProvider which VERY good and reliabel Internet connectivity and install there my WHOLE PostgreSQL database and let my other websites access them over the internet... This would solv all of my MySQL/PostgreSQL problems... But I have not found a singel Hosting-Provider which is willing to host a couple of Databases for which I need 6 TByte of diskspace... Even a smaller one (used by my own website and onlinestore) which will have arround 1-2 GByte give me already trouble. Also I have data from over 140.000 worldwide customers in it. A dedicated Root-Server with 300 GByte SAS in Raid-1 without Hotfix cost alread 150 Euro per month. I have at home a SCSI dino of fiveteen 300 GB drives (-Raid-5 with 3600 GByte) and the most inexpensive Option would be a 19/42U closet in Nürnberg at Hetzner) which then cost 99€/month plus traffic and electricity. Can you recommend to host the database on a seperated machine in the Internet at another ISP? If you want to impose strong data intregrity checking as part of your schema, and handle violations of those checks cleanly in your application, then you'll probably be doing database specific things there too. This why I have asked in another thread on lists.php.net how to make an API for my programs which then can be adapted to the specific DB. Since I have no need for complex operation I have created PHP includes like /usr/share/tdphp-vserver/01_database_pgsql.inc /usr/share/tdphp-vserver/01_database_mysql.inc /usr/share/tdphp-vserver/01_database_oracle.inc which have the neccesary functions defined. However, not all functions are universell used in my php5 scripts, which mean, that I have sometimes 4 or 5 different UPDATE functions... ...and of course, have to comment it since otherwise in 4 weeks I would not more know WHAT I have done and WHY... However, this INCLUDES can then adapted to the database specific functions, but right it is the hell... Sometimes there are also database features that're just so compelling that you'll save yourself vast amounts of development time (and thus produce a better app due to spending that time on other things) by using them. I've made use of PostgreSQL's advisory locks to solve problems that'd otherwise require inter-client communication by side channels or the use of an application server, for example. I know, WHY I use PostgreSQL for 9 1/2 years... The main app I'm working on at present (not the awful Access based one I'm having to do) would probably be portable to Oracle with a bit of work. Porting it to MySQL, even new versions, or worse to SQLite would be absurd to even try. My german hosting provider is using MySQL 5 and I have MANY problems to adapt my own OnlineStore to it... With PostgreSQL 8.0 to 8.3 it was not a problem... But since my hosting provider is a small one, maybe I can convice him to do something. For some month he told me, for the 15 Euro/month month I have 2000 MByte of diskspace and if I do not install a website Ican use the entired 2000 MByte for the database... grmpfIt is MySQL/grmpf I am thinking on a small dedicated database server with 4x SAS 147 GByte and an AMD Opteron with 2 GByte of diskspace. The benefit of using powerful database features and designing around MVCC has been significantly quicker development than would've been possible had I been forced to attempt to be database-agnostic, as well as very strong data integrity enforcement, good error handling recovery,
[GENERAL] Database access over the Internet...
* Do not Cc: me, because I READ THIS LIST, if I write here * *Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe* ...because using 2-3 differnet databases sucks! Hello *, After IRC'ing with some peoples and discusing about PostgreSQL, MySQL, SQlite, Oracle Informix and others I like to know, if you use a database access over the Internet what your experience is with... My problem is, that I am using PostgreSQL since version 6.4 (March 1999) and most Hosting-Provides offer only MySQL which let me run into trouble with my nicely designed database. So my idea is now, to install a small 19/1U (maybe 2U) RackServer which hold (at least for the data) four SAS drives of 76/147 GByte in Raid-1 with 2 Hotfix (and maybe 3-4 smaller 36 GByte drives for the OS+Logs. It seems, I can have 1U racks with 4 drives and hardware Raid-1 for less then 1400 Euro and the 2U ones for arround 1800 Euro. Of course, without the SAS drives... Now the problem is availability... Installing 3-4 Racks all over the world and clustering it? The database would have only the data of my OnlineStore (with data of currently arround 140.000 customers and increasing) and my website. So, for this Base-Database I need only arround 1-2 GByte for now but if my website is entirely online, the data volume increase rapidely. How do you manage such case? Are there Hosting-Provider where I can cluster my Database with the Database on another ISP? E.g. One in the USA, one in Germany and one in Swiss? How do you manage Backups for the Database? Note 1: I live in a MobilHome (generaly two and a MobilOffice) and have no ADSL availlable here. Only GSM/UMTS/HSDPA (up to 3.6 MBit). Maybe I will install Astra2connect which give me a bidirectional satelit connection but this is currently future for me even if it cost only 20 Euro per month prepayed for 2 years. Note 2: I am working 95% mobil, which is WHY I use GSM/UMTS/HSDPA. (cost only 25 Euro/month with unlimited traffic at O2 in Germany) Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Equivalent for AUTOINCREMENT?
Michelle Konzack wrote:
The ONLY real option would be, go to a HostingProvider which VERY good
and reliabel Internet connectivity and install there my WHOLE PostgreSQL
database and let my other websites access them over the internet...
... making them subject to problems with transit between the host
handing PostgreSQL and the host handling the rest of the work. You need
every hop of the main routes between the Pg host and all your other
providers to work reliably, with stable and reasonably low latency, all
the time. This isn't very likely.
You'll also have big latency problems, and if your design doesn't use
any more powerful database features you probably do LOTS of round trips,
lots of read-modify-writes, etc, and you'll suffer a horrible impact on
performance.
But I have not found a singel Hosting-Provider which is willing to host
a couple of Databases for which I need 6 TByte of diskspace...
You would probably need dedicated co-located hosting where you provide
the machine and they provide rack space, stable power, an Ethernet port
or two, access to a console server with remote power cycle capability,
and hopefully access to some backup storage.
You'd have a hard time fitting 6TB of fast, reliable storage even in a
5RU enclosure (say 24 300GB SAS disks in a high-density storage server
chassis) so you'd probably need an external FC or SAS storage enclosure,
probably as well as internal storage.
$LOTS.
On the other hand, you expect $LOTS if you need to store 6TB of data
with fast, reliable access to it.
At least with SAS becoming so standard you don't need to worry about
fibre channel anymore.
If you don't need all of your data to be accessible particularly
quickly, you can save megabucks by using a set of 1TB SATA disks for
your bigger, more rarely used stuff. If you don't have too many big
indexes you might be able to keep them on the SAS disks for faster access.
A dedicated Root-Server with 300 GByte SAS in Raid-1 without Hotfix cost
alread 150 Euro per month.
Where is it hosted? At that price, I want one.
Can you recommend to host the database on a seperated machine in the
Internet at another ISP?
Personally, I wouldn't do it, because of the aforementioned problems
with latency and with unstable transit. I guess it depends on your
price/performance/reliability tradeoff requirements.
If you want to impose strong data intregrity checking as part of your
schema, and handle violations of those checks cleanly in your
application, then you'll probably be doing database specific things
there too.
This why I have asked in another thread on lists.php.net how to make
an API for my programs which then can be adapted to the specific DB.
Since I have no need for complex operation
If you host your database somewhere far enough away from the application
that you're seeing several-hundred-millisecond latencies then you WILL
need complex operations. You will need to do as much work as possible on
the database end, avoid read-modify-writes (but you should do that
anyway), use SQL or PL/PgSQL functions to batch operations together, etc.
So ... targeting a specific database isn't all bad, so long as you think
Right, but there are MANY application which are realy simple, where
database access can easyly ported but they HARDCODE inside the
applications the database functions instead of exporting them to an
INCLUDE for example and of course, without anny comments...
Yeah, I agree that's pretty objectionable. Whether it's bad because the
database access is simple (ie they're not using the database's ability
to do lots of the hard work for them) or because they've mixed it in
with the rest of the code I don't know.
I find I'll look at some application and see an abstraction layer that
results in the application doing things like:
x = get_value('field', 'table');
x ++;
set_value('field', 'table', x);
ie
SELECT field FROM table;
-- returns `42'
UPDATE table SET field = 43;
... which just makes me want to cry. Concurrency issues? Latency? Argh.
And that's a simple one; it gets WAY better once they come up with their
own procedural methods of doing what could be done with an UPDATE ...
FROM ... WHERE or the like.
That said, I'll confess to using Hibernate (a Java ORM) in large parts
of one of the apps I'm working on at present. It does a pretty good job,
and it's sane enough that you can bypass and use SQL (via the JDBC APIs)
when you have work to do that it's not well suited for, such as bulk
updates or reporting queries.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Importing text file into a TEXT field
Hello, The intent is to use pdftotext and store the resulting text in datbase for full text search purposes... I'm trying to develop a mini content server where I'll put pdf documents to make it searchable. Generally, PDFs are in size of 500 to 3000 pages resulting in text from 500kb to 2megabytes... I'm also looking at open source projects like Alfresco if it can serve with ease to my purpose... Anyone use this one? Comments are welcome. Thanks Bruno Lavoie Sam Mason a écrit : On Fri, Nov 07, 2008 at 11:15:43AM -0500, Bruno Lavoie wrote: Is there a way to easily import a relatively huge text file into a table column? How big is relatively huge? I'd like to use psql and I`'ve looked at lo_* commands and I can't figure how to import my text file into my TEXT column. the lo_* commands are for working with large objects; these have somewhat unusual semantics compared to the normal data in columns in PG. If you're routinely expecting files of more than, say, one MB then they're probably a good way to go, but it's a lot more work getting them going in the first place. My last solution is to write a little script to load my text file in a var and then insert to databse. If you want to just get the data into a TEXT column as quickly as possible; I'd probably just write a little bit of code to perform the escaping that PG requires on the file. You can then simply do: COPY tbl (col) FROM '/path/to/escaped/file'; I'm not sure if this is really what you want though! Enormous TEXT columns can be a bit fiddly to work. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE tuples with a sub-select
Gerhard Heift, 07.11.2008 14:47: What about: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o WHERE o.customer_id = report_table.customer_id) Its untested, but I think, it must works like this. Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) ) But unfortunately it gives an error: ERROR: subquery in FROM cannot refer to other relations of same query level [SQL State=42P10] Ok, its a little bit more complicated: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT customer_id, count(*) AS s_count, sum(amount) AS s_value FROM order o GROUP BY customer_id) AS summary WHERE summary.customer_id = report_table.customer_id) Cool! Thanks a lot Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UPDATE tuples with a sub-select
Hi, are there any plans to support updating a tuple using a sub-select in one of the future versions. e.g, something like: UPDATE report_table SET (order_count,order_value) = (SELECT count(*), sum(amount) FROM order o WHERE o.customer_id = report_table.customer_id); I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on the roadmap Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] After delete trigger problem
Hello, I have a child table with CONSTRAINT fkey FOREIGN KEY (x) REFERENCES master (x) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE. and CREATE TRIGGER td_y AFTER DELETE ON chlid FOR EACH ROW EXECUTE PROCEDURE fn_td_y(); and this trigger refers to the master table... CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS $BODY$ DECLARE fi integer; BEGIN SELECT i INTO fi FROM master WHERE x = old.x; ... It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve this? Regards, Teemu Juntunen
Re: [GENERAL] After delete trigger problem
Teemu Juntunen [EMAIL PROTECTED] writes: CREATE TRIGGER td_y AFTER DELETE ON chlid FOR EACH ROW EXECUTE PROCEDURE fn_td_y(); It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve this? Your trigger is firing after the RI triggers. If you want it to fire before, give it a name that is before them (in ASCII order). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] After delete trigger problem
On Nov 7, 2008, at 10:57 AM, Teemu Juntunen wrote: Hello, I have a child table with CONSTRAINT fkey FOREIGN KEY (x) REFERENCES master (x) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE. and CREATE TRIGGER td_y AFTER DELETE ON chlid FOR EACH ROW EXECUTE PROCEDURE fn_td_y(); and this trigger refers to the master table... CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS $BODY$ DECLARE fi integer; BEGIN SELECT i INTO fi FROM master WHERE x = old.x; ... It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve this? Yes,, that is intended. An AFTER DELETE statement runs after the triggering statement has completed and your FOREIGN KEY constraint is set to ON DELETE CASCADE so by the time the statement completes and the trigger fires the DELETE has already CASCADEd to the master table. As far as how to get around it we'd need to know a little more about what the trigger is actually supposed to do. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] After delete trigger problem
On Nov 7, 2008, at 11:24 AM, Erik Jones wrote: On Nov 7, 2008, at 10:57 AM, Teemu Juntunen wrote: Hello, I have a child table with CONSTRAINT fkey FOREIGN KEY (x) REFERENCES master (x) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE. and CREATE TRIGGER td_y AFTER DELETE ON chlid FOR EACH ROW EXECUTE PROCEDURE fn_td_y(); and this trigger refers to the master table... CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS $BODY$ DECLARE fi integer; BEGIN SELECT i INTO fi FROM master WHERE x = old.x; ... It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve this? Yes,, that is intended. An AFTER DELETE statement runs after the triggering statement has completed and your FOREIGN KEY constraint is set to ON DELETE CASCADE so by the time the statement completes and the trigger fires the DELETE has already CASCADEd to the master table. As far as how to get around it we'd need to know a little more about what the trigger is actually supposed to do. Ah, nevermind this. Tom has just reponded in another reply and educated me to the fact that fkeys fire in sequence with triggers, I thought they were separate. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE tuples with a sub-select
On Fri, Nov 07, 2008 at 11:15:07AM +0100, Thomas Kellerer wrote: Hi, are there any plans to support updating a tuple using a sub-select in one of the future versions. e.g, something like: UPDATE report_table SET (order_count,order_value) = (SELECT count(*), sum(amount) FROM order o WHERE o.customer_id = report_table.customer_id); What about: UPDATE report_table SET order_count = s_count, order_value = s_value FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o WHERE o.customer_id = report_table.customer_id) Its untested, but I think, it must works like this. I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on the roadmap Regards Thomas Regards, Gerhard signature.asc Description: Digital signature
[GENERAL] Specifying text to substitute for NULLs in selects
Hi, I can specify the text used to represent a null value in output from copy, but I'd like to do something similar is select output, eg: all NULL values are represented by NA or NaN. I can't find anything in the docs about this. This could be managed using case statements around all the columns in the query, but is there a simpler way, like setting a system variable to specify this? Thanks, Brent Wood -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Defining string to represent null values in select
Hi, I can specify the text used to represent a null value in output from copy, but I'd like to do something similar is select output, eg: all NULL values are represented by NA or NaN. I can't find anything in the docs about this. This could be managed using case statements around all the columns in the query, but is there a simpler way, like setting a system variable to specify this? Thanks, Brent Wood -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
[EMAIL PROTECTED] wrote:
Hi,
I can specify the text used to represent a null value in output
from copy, but I'd like to do something similar is select output,
eg: all NULL values are represented by NA or NaN.
I can't find anything in the docs about this.
This could be managed using case statements around all the columns
in the query, but is there a simpler way, like setting a system
variable to specify this?
wtw_drupal=# create schema test;
CREATE SCHEMA
wtw_drupal=# create table test.test(c1 text);
CREATE TABLE
wtw_drupal=# insert into test.test values(null);
INSERT 0 1
wtw_drupal=# insert into test.test values('test');
INSERT 0 1
wtw_drupal=# \copy test.test to stdout null as 'BANANA'
BANANA
test
wtw_drupal=# drop schema test cascade;
NOTICE: drop cascades to table test.test
DROP SCHEMA
everything clearly explained in the COPY manual:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
I think you are more after something like
SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar.
-Said
Ivan Sergio Borgonovo wrote:
On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
[EMAIL PROTECTED] wrote:
Hi,
I can specify the text used to represent a null value in output
from copy, but I'd like to do something similar is select output,
eg: all NULL values are represented by NA or NaN.
I can't find anything in the docs about this.
This could be managed using case statements around all the columns
in the query, but is there a simpler way, like setting a system
variable to specify this?
wtw_drupal=# create schema test;
CREATE SCHEMA
wtw_drupal=# create table test.test(c1 text);
CREATE TABLE
wtw_drupal=# insert into test.test values(null);
INSERT 0 1
wtw_drupal=# insert into test.test values('test');
INSERT 0 1
wtw_drupal=# \copy test.test to stdout null as 'BANANA'
BANANA
test
wtw_drupal=# drop schema test cascade;
NOTICE: drop cascades to table test.test
DROP SCHEMA
everything clearly explained in the COPY manual:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT .... RETURNING
On Thu, Nov 6, 2008 at 8:49 AM, Rafal Pietrak [EMAIL PROTECTED] wrote: One comment I'd like to make as total lamer on the subject, is that the assumption on SELECT (that it's not firing triggers), could potentially be resolved by a *global* or database configuration option - once selected, the SQL programmers' responsibility would be: not to assume that on SELECT at the application layer. I think Tom meant that PostgreSQL's code assumes that select fires no triggers. Hence, triggers (and probably constraints) wouldn't fire if you did a select (insert ... returning). So your setting would basically mean make triggers fire some of the time, and I don't care about data consistency either. I doubt any sane person would activate it ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Fri, 07 Nov 2008 15:20:24 -0500 Said Ramirez [EMAIL PROTECTED] wrote: I think you are more after something like SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar. missing an else at least and... wtw_drupal=# create table test.test(c1 int); CREATE TABLE wtw_drupal=# insert into test.test values(null); INSERT 0 1 wtw_drupal=# insert into test.test values(1); INSERT 0 1 wtw_drupal=# \copy (select case when c1 is null then 'NA' else c1 end from test.test) to stdout ERROR: invalid input syntax for integer: NA \copy: ERROR: invalid input syntax for integer: NA furthermore... even if c1 was text you may end up in output like: 'NA' that will be hard to be discerned from a normal string. BTW I just discovered that COPY doesn't work on view. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
Said Ramirez wrote: I think you are more after something like SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar. -Said An even simpler way to do this is using the COALESCE function: http://www.postgresql.org/docs/current/interactive/functions-conditional.html SELECT COALESCE(foo, 'NA') AS foo FROM bar; will either return the value in the field(s) foo or 'NA' if it is NULL. Keep in mind that you can't mix data types, like 'NaN'::text and 32.3::float in the result. -Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] archive command Permission Denied?
Please bear with me. I am fairly new to Linux. I am working on archiving my WAL files. For now I am just mailing myself a list of the directory contents using the following script. *-rwxr-xr-x 1 postgres postgres 87 Oct 28 20:23 /var/lib/pgsql/mail-WAL-list.sh* the contents are simply *ls -lrt /var/lib/pgsql/data/pg_xlog/ | mail -s WAL files are: [EMAIL PROTECTED] I also have this set up to run in the postgres users cron at 8 a.m. I can run this when logged on as postgres and the cron runs fine as well, but I keep seeing the following in my logs. *LOG: archive command failed with exit code 126 DETAIL: The failed archive command was: /var/lib/pgsql/mail-WAL-list.sh WARNING: transaction log file 000100F0 could not be archived: too many failures sh: /var/lib/pgsql/mail-WAL-list.sh: Permission denied* I would appreciate any advice on what permission I need to set in order for this command to run. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com
Re: [GENERAL] Specifying text to substitute for NULLs in selects
Mike Toews wrote: Keep in mind that you can't mix data types, like 'NaN'::text and 32.3::float in the result. oh yeah, regarding mixing data types (in regards to the first post)... A good exception is that you can use 'NaN' for floating point data types, so: SELECT COALESCE(myval, 'NaN') as myval FROM foo; where myval is a field with a floating-point data type. This maneuver is sometimes preferred in some aggregates like sum() where you don't want to take sums on incomplete sets since NULL is counted as 0 whereas a single NaN value forces the resulting sum to be NaN. There are other special floats like 'Infinity' and '-Infinity', which can also be coalesced in for NULL float values: http://www.postgresql.org/docs/current/interactive/datatype-numeric.html -Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive command Permission Denied?
Jason Long [EMAIL PROTECTED] writes: I am working on archiving my WAL files. For now I am just mailing myself a list of the directory contents using the following script. *-rwxr-xr-x 1 postgres postgres 87 Oct 28 20:23 /var/lib/pgsql/mail-WAL-list.sh* I can run this when logged on as postgres and the cron runs fine as well, but I keep seeing the following in my logs. *LOG: archive command failed with exit code 126 DETAIL: The failed archive command was: /var/lib/pgsql/mail-WAL-list.sh WARNING: transaction log file 000100F0 could not be archived: too many failures sh: /var/lib/pgsql/mail-WAL-list.sh: Permission denied* That's just bizarre. The permissions on the script itself seem to be fine, so the only theory that comes to mind is the server doesn't have search (x) permission on one of the containing directory levels ... but that's hard to believe seeing that your data directory is inside the same tree. [ thinks... ] I believe that some flavors of Unix are picky about shell scripts having a proper introducer line. I'm not sure that would manifest as Permission denied, but does it work better if you put #! /bin/sh as the first line of the script file? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive command Permission Denied?
I wrote: That's just bizarre. The permissions on the script itself seem to be fine, so the only theory that comes to mind is the server doesn't have search (x) permission on one of the containing directory levels ... Oh, wait, I bet I've got it: you're using a SELinux-enabled system and SELinux doesn't believe that it's a good idea to let the Postgres server execute something out of its data directory. That would explain why the other methods of executing the script work --- typical SELinux policy is a lot stricter on network-exposed daemon processes than other stuff. If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. The solution I'd recommend is putting the script someplace that's more usual to store scripts. You might be able to do something with changing the security context on the script file instead, but I'm not sure exactly what to change it to. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Show all commands executed
How can I show on terminal all commands executed by my postgre server? Example: My system execute the comand : 'SELECT * FROM clients' and the postgre show this commando on the terminal ? Thanks
Re: [GENERAL] Show all commands executed
Anderson dos Santos Donda [EMAIL PROTECTED] writes: How can I show on terminal all commands executed by my postgre server? You could turn on log_statements and then tail -f the postmaster log. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: I wrote: That's just bizarre. The permissions on the script itself seem to be fine, so the only theory that comes to mind is the server doesn't have search (x) permission on one of the containing directory levels ... Oh, wait, I bet I've got it: you're using a SELinux-enabled system and SELinux doesn't believe that it's a good idea to let the Postgres server execute something out of its data directory. *I am using Centos 5.2 and is looks like there is SELinux. This is the first time it has caused me a problem so far.* That would explain why the other methods of executing the script work --- typical SELinux policy is a lot stricter on network-exposed daemon processes than other stuff. If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* The solution I'd recommend is putting the script someplace that's more usual to store scripts. *I moved this inside the postgres data directory. I will post back the results. If this does not work I will have my Linux consultant have a look. Thank you very much for the advice.* You might be able to do something with changing the security context on the script file instead, but I'm not sure exactly what to change it to. regards, tom lane
Re: [GENERAL] archive command Permission Denied?
Jason Long [EMAIL PROTECTED] writes: Tom Lane wrote: If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* You'd need to look in the system log (/var/log/messages) not the postmaster's log. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: Jason Long [EMAIL PROTECTED] writes: Tom Lane wrote: If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* You'd need to look in the system log (/var/log/messages) not the postmaster's log. I did not look in the postmasters logs. I looked in less /var/log/message and less /var/log/secure I saw nothing that looked related to this. This does seem a little strange. I will definitely post back my finding when I resolve this. regards, tom lane
Re: [GENERAL] Specifying text to substitute for NULLs in selects
Thanks guys,
I'm aware of those options, what I was wondering was if there is a more generic
way,
for example the Empress RDBMS allows 'set MSNULLVALUE NA', and all NULLs
will from then on be output as NA.
The COPY option is closest to a generic setting, but doesn't work with a select
query,
just a table dump.
I guess something like the following will work from the shell, although it is
hardly elegant :-)...
psql -d DB -Atc select '', attr, attr, attr, '' from ; | sed
's/||/|NA|/' | sed 's/|//' | sed 's/|//' data.txt
Slightly simpler than the case statement approach in Postgres is COALESCE()
eg: select COALESCE(attr,'NA') as attr from table;
but this still needs to be applied to every column in the outout which may have
nulls. rather than a generic one off setting. A view using COALESCE() may be
the easiest way for users to have this capability automatically..
Thanks,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
Said Ramirez [EMAIL PROTECTED] 11/08/08 12:34 PM
I think you are more after something like
SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar.
-Said
Ivan Sergio Borgonovo wrote:
On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
[EMAIL PROTECTED] wrote:
Hi,
I can specify the text used to represent a null value in output
from copy, but I'd like to do something similar is select output,
eg: all NULL values are represented by NA or NaN.
I can't find anything in the docs about this.
This could be managed using case statements around all the columns
in the query, but is there a simpler way, like setting a system
variable to specify this?
wtw_drupal=# create schema test;
CREATE SCHEMA
wtw_drupal=# create table test.test(c1 text);
CREATE TABLE
wtw_drupal=# insert into test.test values(null);
INSERT 0 1
wtw_drupal=# insert into test.test values('test');
INSERT 0 1
wtw_drupal=# \copy test.test to stdout null as 'BANANA'
BANANA
test
wtw_drupal=# drop schema test cascade;
NOTICE: drop cascades to table test.test
DROP SCHEMA
everything clearly explained in the COPY manual:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water Atmospheric
Research Ltd.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Sat, 08 Nov 2008 13:05:08 +1300 Brent Wood [EMAIL PROTECTED] wrote: Thanks guys, I'm aware of those options, what I was wondering was if there is a more generic way, for example the Empress RDBMS allows 'set MSNULLVALUE NA', and all NULLs will from then on be output as NA. The COPY option is closest to a generic setting, but doesn't work with a select query, just a table dump. \copy (select ) to ... works. As written in my 2nd post. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
On Friday 07 November 2008 4:05:08 pm Brent Wood wrote: Thanks guys, I'm aware of those options, what I was wondering was if there is a more generic way, for example the Empress RDBMS allows 'set MSNULLVALUE NA', and all NULLs will from then on be output as NA. The COPY option is closest to a generic setting, but doesn't work with a select query, just a table dump. I guess something like the following will work from the shell, although it is hardly elegant :-)... psql -d DB -Atc select '', attr, attr, attr, '' from ; | sed 's/||/|NA|/' | sed 's/|//' | sed 's/|//' data.txt Slightly simpler than the case statement approach in Postgres is COALESCE() eg: select COALESCE(attr,'NA') as attr from table; but this still needs to be applied to every column in the outout which may have nulls. rather than a generic one off setting. A view using COALESCE() may be the easiest way for users to have this capability automatically.. Thanks, Brent Wood Using psql http://www.postgresql.org/docs/8.2/interactive/app-psql.html lfnw=# \a\t\f ','\pset null 'NA' Output format is unaligned. Showing only tuples. Field separator is ,. Null display is NA. lfnw=# SELECT null,1; NA,1 -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive command Permission Denied?
Tom Lane wrote: Jason Long [EMAIL PROTECTED] writes: Tom Lane wrote: If that is what's happening, you'll find avc denied messages in the system log that correlate to the archive failures. *I did not see anything like this in my logs.* You'd need to look in the system log (/var/log/messages) not the postmaster's log. *I think I found the problem. By putting the archive command directly in postgresql.conf I got this error /usr/sbin/sendmail: Permission denied So I guess I need to allow the use of sendmail. How is postgres running the command different from my doing it as the postgres user or cron running as the postgres user? Oh, well time to call my Linux guru. Thanks for all your help. You definitely got me going tin the right direction.* regards, tom lane
Re: [GENERAL] postgresql and Mac OS X
adam_pgsql wrote: When I do the install script in contrib it says I have no rights to the directory. It was in /usr/local/pgsql/data/log and I changed it to /usr/local/pgsql/log. It was set as root.wheel with 755 permissions so I suspect it's mad at me because the postgres user was left in the cold. Have you switched on logging in postgresql.conf? doh! There's no postgresql.conf file, just a postgresql.conf.sample. Guess I have to start from .sample and work my way up... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] options for launching sql script asynchronously from web app
I'd like to launch some sql script asynchronously from a web app and have some kind of feedback later. Some form of authentication would be a plus. Is there anything ready? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] options for launching sql script asynchronously from web app
Authenticate in web app and drop a script in a directory and run them with cron maybe? Authenticate in web app and drop a row in a table and let a long running process suck the row out and kick something off in a thread pool? I've seen both. You've got to monitor both somehow. The second one is much more work but lets you do more with the output and gives you a nice thread pool. There might be a simple bash-ie way to make a thread pool, but I don't know it. --Nik On Fri, Nov 7, 2008 at 8:06 PM, Ivan Sergio Borgonovo [EMAIL PROTECTED]wrote: I'd like to launch some sql script asynchronously from a web app and have some kind of feedback later. Some form of authentication would be a plus. Is there anything ready? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive command Permission Denied?
Jason Long [EMAIL PROTECTED] writes: I got this error /usr/sbin/sendmail: Permission denied So I guess I need to allow the use of sendmail. How is postgres running the command different from my doing it as the postgres user or cron running as the postgres user? SELinux treats it differently: programs that are run as network-accessible daemons get locked down to do only what the SELinux policy says they should be able to do. This is not unreasonable --- if someone managed to crack into your Apache server, for instance, you'd be really glad that they weren't able to use the breach to spam the world from your machine. However, if you want your Postgres server able to do things not listed in the SELinux policy for it, you'll need to adjust that policy. Or disable SELinux ... but I don't really recommend doing that if your machine is at all exposed to the internet. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Specifying text to substitute for NULLs in selects
Thanks Adrian, That's perfect!! Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Adrian Klaver [EMAIL PROTECTED] 11/08/08 1:49 PM On Friday 07 November 2008 4:05:08 pm Brent Wood wrote: Thanks guys, I'm aware of those options, what I was wondering was if there is a more generic way, for example the Empress RDBMS allows 'set MSNULLVALUE NA', and all NULLs will from then on be output as NA. The COPY option is closest to a generic setting, but doesn't work with a select query, just a table dump. I guess something like the following will work from the shell, although it is hardly elegant :-)... psql -d DB -Atc select '', attr, attr, attr, '' from ; | sed 's/||/|NA|/' | sed 's/|//' | sed 's/|//' data.txt Slightly simpler than the case statement approach in Postgres is COALESCE() eg: select COALESCE(attr,'NA') as attr from table; but this still needs to be applied to every column in the outout which may have nulls. rather than a generic one off setting. A view using COALESCE() may be the easiest way for users to have this capability automatically.. Thanks, Brent Wood Using psql http://www.postgresql.org/docs/8.2/interactive/app-psql.html lfnw=# \a\t\f ','\pset null 'NA' Output format is unaligned. Showing only tuples. Field separator is ,. Null display is NA. lfnw=# SELECT null,1; NA,1 -- Adrian Klaver [EMAIL PROTECTED] NIWA is the trading name of the National Institute of Water Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Short CVS question, history
Hi, I have a short CVS question please: How do I go from a particular file revision like pgsql/cvs/pgsql/src/backend/parser/parse_relation.c.1.3 to the complete commit? I.e. I would like to navigate back from this particular file to the commit and see all the other files that were touched by the commit. Also, is it possible that you moved the CVS repository once and lost some history? Thanks! Dirk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
