Re: [GENERAL] ERD Tool
Adarsh Sharma, 31.08.2011 13:54: Dear all, Is there any open source ERD Tool for Postgresql Database. I find some paid tools but looking for free tools. Have a look at Power*Architect: http://www.sqlpower.ca/page/architect It's not perfect but it's quite OK. As it is a multi-DBMS tool it does not support any Postgres specific features or datatypes. To avoid the nasty registration that is required on their homepage you can also download the binaries directly from the Google code project homepage: http://code.google.com/p/power-architect/downloads/list 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] JDBC Connection Errors
Hi, - check for open server socket: netstat -tulpen | grep postgres - try to force ipv4 for java with system property (a recent jre prefers ipv6): -Djava.net.preferIPv4Stack=true regards Thomas Am 24.08.2011 00:47, schrieb Sam Nelson: Hi list, A client is hitting an issue with JDBC: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. -pg_hba.conf is set to trust 0.0.0.0/0 (IPv4 only) -listen_addresses is * -I can find no evidence of iptables running on the server. -PGAdmin connects just fine. -psql connects just fine. -I can find no errors in the log file from that day for the user that the client is trying to log in as. We're working on getting access to more details about how they're trying to connect, but in the mean time, does anyone know if JDBC has any issues connecting that psql and PGAdmin wouldn't have? Is it possible that JDBC is somehow susceptible to connection issues that JDBC and psql are not? --- === Samuel Nelson Consistent State www.consistentstate.com 303-955-0509 === -- 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 tame a gigantic (100+ lines) query in a web app?
Hi, use WITH queries, I use this regularly and it works fine. http://www.postgresql.org/docs/9.0/static/queries-with.html regards Thomas Am 14.08.2011 16:39, schrieb W. Matthew Wilson: I'm sure I'm not the first person to end up with a gigantic query that does lots of left joins and subselects. It seems to work, but I would love to break it up into smaller chunks. I'm thinking about rewriting the query to make several temporary tables that are dropped on commit, and then joining them at the end. I can't just use views for everything because I use parameters passed in from the web app. I am using a few views where I can. Is there anything dangerous about making temporary tables in this way? I started two transactions simultaneously and they were both able to make their own temporary tables. More generally, how to tame this big ol' query? The temporary tables mean I'm only pulling data from the database one time. ORMs often pull data from one query and then use that data to write the next query. This seems slow to me. Matt -- 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] Filling null values
hi, try this. If your table name is mytable: select a.homeid , a.city , coalesce(a.date, (select b.date from mytable b where b.homeid=a.homeid and b.date is not null order by b.prepost=a.prepost desc limit 1) ) as date , a.measurement , a.prepost from mytable a Thomas Am 05.08.2011 18:32, schrieb jeffrey: I have a table that looks like this: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles2/4/2005 938 pre 124 NULLNULL 266 pre 124 los angeles7/4/2006 777 post I'd like to write a query so that I get the following result: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932post 124 los angeles2/4/2005 938 pre 124 los angeles2/4/2005 266 pre 124 los angeles7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff -- 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] variant column type
salah jubeh, 26.07.2011 19:02:
Hello,
suppose the following scenario
the car speed is 240
the car has an airbag
Here the first value is integer and the second value is boolean. Consider that
I have this table structure
feature (feature id feature name)
car (car id, )
car_feature (car id, feature id, value). the value attribute might have
different domains. How can I model this using postgres and using ANSI compliant
design ?
Regards
Have a look at the hstore contrib module.
It allows you to store key/value pairs (lots of them) in a single column.
create table car
(
car_id integer,
features hstore
);
insert into car (car_id, features)
values
(1, 'speed => 240, airbag => true');
insert into car (car_id, features)
values
(2, 'speed => 140, airbag => false');
insert into car (car_id, features)
values
(3, 'speed => 140, flux_capacitor => true');
-- show the airbag attribute for all cars
-- will return null for those that don't have that attribute
select car_id, (features -> 'airbag') as airbag_flag
from car;
-- return all rows that have an attribute named flux_capacitor with the value
true
select *
from car
where features @> ('flux_capacitor => true')
Note that the only drawback of this solution is that you don't have any datatypes for the
attributes and you can't create a foreign key constraint to a "feature" table.
But it's probably the most flexible way to deal with such a requirement in Postgres.
--
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] Is there a way to 'unrestrict' drop view?
Hi, well, the reason I'm asking is that this *is* posible in Oracle DB. For me it looks like that the DB knows that the view is broken. You can't use it, *but* it is still there (and it will be usable again when the view query is valid again). I completely agree that the view should be usable again at the end of transaction (even thus Oracle DB doesn't impose that either), but drop and re-create the objects in correct order is painful. The heart of the my pain is that a program I use works like this. I would like to migrate the DB beneath it... Cheers, Thomas Am 22.07.2011 10:26, schrieb Willy-Bas Loos: > On Thu, Jul 21, 2011 at 3:20 PM, Thomas Pasch wrote: >> I would like to recreate/replace a view, but there are 'dependant >> objects' on it. Is there a way to 'unrestrict' the dependant check in >> the current transaction, like it could be done with certain constraints? > > Hi, > > Nice idea, but i think there isn't a way to do that. > You will have to drop and re-create the objects in the correct order, > best in a single transaction. > > I can imagine that that can be nasty, even apart from the hassle of > cutting and pasting + testing that code. You might be needing those > objects in a running system. > But then what would it mean to to what you suggest? The dependent > objects could never function while the view does not exist, so it ends > up being much the same as drop+create. > Except that you are changing the view, so you might also need to > change the depending objects.. > > Cheers, > > WBL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a way to 'unrestrict' drop view?
Hello, I would like to recreate/replace a view, but there are 'dependant objects' on it. Is there a way to 'unrestrict' the dependant check in the current transaction, like it could be done with certain constraints? Kind 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] An example for WITH QUERY
Durumdara, 22.06.2011 12:35: Hi! I have 3 tables. I want to run a query that collect some data from them, and join into one result table. I show a little example, how to do this in another DB with script: with tmp_a as ( select id, name, sum(cost) cost from items ... ), temp_b as ( select item_id, sum(price) price from bills ), temp_c as ( select item_id, sum(price) price from incoming_bills where... group by item_id with data ) select tmp_a.id, tmp_a.name, tmp_a.cost, tmp_b.price outgoing_price, tmp_c.price incoming_price from tmp_a left join tmp_b on (tmp_a.id = tmp_b.item_id) left join tmp_c on (tmp_a.id = tmp_c.item_id) order by name But a with is not really necessary here (although I personally find it easier to read) because you can simply put those SELECTs into the from clause: select tmp_a.id, tmp_a.name, tmp_a.cost, tmp_b.price outgoing_price, tmp_c.price incoming_price from ( select id, name, sum(cost) cost from items ) temp_a left join ( select item_id, sum(price) price from bills ) tmp_b on (tmp_a.id = tmp_b.item_id) left join ( select item_id, sum(price) price from incoming_bills where... group by item_id with data ) tmp_c on (tmp_a.id = tmp_c.item_id) order by name -- 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] Problems with to_number
Chrishelring wrote on 10.06.2011 22:45:
HI all,
below is the view i´ve tried to create on a table. The purpose was to do
some math on one of the columns (retning). The column is a double precision
number. The result is that the function is not recognized ("ERROR: function
to_number(double precision, unknown) does not exist"). I´m a bit uncertain
on how the syntax is, so perhaps someone could point me in the right
direction?
If the column is already a numeric data type there is no reason to use
to_number() (which converts a character value to a number)
CREATE OR REPLACE VIEW "husnr_view" ("KOMNR", "VEJKODE", "VEJNAVN",
"HUSNUMMER", "POSTNR", "POSTNAVN", "X", "Y", "RETNING", "TSTAND", "NKLASSE")
AS
SELECT
HUSNR.KOMNR KOMNR,
HUSNR.VEJKODE VEJKODE,
HUSNR.VEJNAVN VEJNAVN,
HUSNUMMER,
HUSNR.POSTNR POSTNR,
HUSNR.POSTNAVN POSTNAVN,
HUSNR.X X,
HUSNR.Y Y,
CASE
WHEN HUSNR.RETNING < 2 THEN (2- HUSNR.RETNING)/111
WHEN HUSNR.RETNING > 2 THEN (4- (HUSNR.RETNING -2))/111
END RETNING,
HUSNR.TSTAND TSTAND,
HUSNR.NKLASSE NKLASSE
FROM rk_grundkort.husnr;
--
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] Recurring events
Hi Craig and mailing list On 07.06.2011 00:54, Craig Ringer wrote: > On 06/06/2011 06:59 PM, Thomas Guettler wrote: >> Hi, >> >> how do you store recurring events in a database? > > I use two tables: one table that stores the recurring event, and another > that's essentially a materialized view containing instances of the event. > > It's not ideal, but performs better than using generate_series to > produce and filter the event series on the fly. > >> end_datetime can be NULL (open end). > > PostgreSQL has an ideal solution to this: the timestamp value > 'infinite'. Using it dramatically simplified my interval-related > queries. I initially used it in my design, only to discover that JDBC > doesn't support infinite dates (argh!) and neither do many languages. I > find this exceptionally frustrating. I use Python (and Django ORM) to access Postgres. Infinite is not supported. But this is no problem. I look at "Materialized Views" in the wiki: http://wiki.postgresql.org/wiki/Materialized_Views The view gets updated by a trigger. But if the date is infinite, you need to constrain the trigger to the next N years. I guess this is the best solution. Monthly you need to update the view from an external event (maybe cron), to create the missing events for N years + one month... Thank you for your answer Craig, Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- 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] Recurring events
On 07.06.2011 09:57, Vincent Veyron wrote: > Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit : > >> how do you store recurring events in a database? >> >> Selecting all events in a week/month should be fast (comming from an index). >> >> My solution looks like this: >> >> Table event: >> >> Columns: id, name, recurring, start_datetime, end_datetime >> >> recurring is weekly, monthly, yearly or NULL. >> > > Maybe you could try something like what is used in cron, the scheduling > program for GNU/Linux I know cron very well. But I need to get all events on day X between time1 and time2 very quickly. If I build a crontab like table, I need to check all entries before I can know which crontab lines get executed during this period. Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recurring events
Hi, how do you store recurring events in a database? Selecting all events in a week/month should be fast (comming from an index). My solution looks like this: Table event: Columns: id, name, recurring, start_datetime, end_datetime recurring is weekly, monthly, yearly or NULL. end_datetime can be NULL (open end). Can you create an indexed view with infinite rows? I only want to index the last three year and the next three years. An other solution would be to fill a table with "serialized" events. The recurring events would be created and inserted into a table. This can only be done in a time frame like above (last three year, next three years). If a recurring event gets altered, all its serialized events need to be updated. Any feedback? Thomas Güttler -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- 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] Access to postgres conversion
On 5/25/2011 3:42 PM, akp geek wrote: Dear all - I would like to know if any one has migrated database from MS access to Postgres . We use postgres 9.0.2 on solaris . Are there any open source tools that you have used to do this task. Can you please share your experiences ? I rolled my own. If the number of rows in the MDB table is not that many (under 100k), then I'll create a new table up on pgsql, link to it with the ODBC driver, and append from the source table to the pgsql table. You can get away with larger appends if both systems are on the same network. If it was a table with a few million rows, then I wrote a little VBA snippet that created a pgdump compatible SQL text file from the source data. To figure out the format, I just pgdump'd an existing table from PostgreSQL, then patterned my SQL file after it. While it was extremely fast at doing the conversion (both generating the SQL file and the time it took for pgdump to process the SQL file), I only recommend that method for cases where you have millions and millions of rows. Or a lot of identical tables. (The VBA module was about 100-150 lines of code in total.) -- 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 field list
Seb wrote on 29.05.2011 23:04: Hi, I've been scouring the system tables for a way to return a list of fields across all tables of a database. I see that pg_attribute is the one to query here, but I'm not sure how to rule out system fields. Thanks in advance for any pointers. information_schema.columns is probably easier to look at: http://www.postgresql.org/docs/current/static/infoschema-columns.html 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] Documentation suggestion
Hi, I'd like to suggest a little enhancement to the documentation chapter about file-system backup http://www.postgresql.org/docs/current/static/backup-file.html As I regularly see people copying files between different operating systems, I think it would be a good idea to add a third restriction to those listed on that page: that a file system backup will only work between the same OS and architecture and is not suited to migrate between different types of systems. 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] Error in the 9.1 documentation?
Thom Brown wrote on 07.05.2011 16:28: while going through the 9.1 new features, I think I have discovered an error in the manual regarding the CREATE TABLE command. The DEFAULT declaration was moved into the column_constraint section. Ah thanks, didn't see that (and I wouldn't expect the DEFAULT to be listed as a constraint...) 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] Error in the 9.1 documentation?
Hi,
while going through the 9.1 new features, I think I have discovered an error in
the manual regarding the CREATE TABLE command.
It says:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT
EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ like_option ... ] }
[, ... ]
Isn't there the element for DEFAULT missing for the column definition?
Something like:
column_name data_type [ DEFAULT default_expr ] [ COLLATE collation ] [
column_constraint [ ... ] ]
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] histogram
hi, group by 1 means group by first output column order by 2 means order by second output column ascending Am 30.04.2011 19:00, schrieb Joel Reymont: What is the meaning of group by 1 order by 2 e.g. what to the numbers 1 and 2 stand for? What would change if I do the following? group by 1 order by 1 On Apr 30, 2011, at 5:48 PM, Thomas Markus wrote: Hi, try something like this: select trunc(random() * 10.)/10. , count(*) from generate_series(1,200) group by 1 order by 2 -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- 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] histogram
Hi, try something like this: select trunc(random() * 10.)/10. , count(*) from generate_series(1,200) group by 1 order by 2 regards Thomas Am 30.04.2011 18:37, schrieb Joel Reymont: I have a column of 2 million float values from 0 to 1. I would like to figure out how many values fit into buckets spaced by 0.10, e.g. from 0 to 0.10, from 0.10 to 0.20, etc. What is the best way to do this? Thanks, Joel -- - for hire: mac osx device driver ninja, kernel extensions and usb drivers -++--- http://wagerlabs.com | @wagerlabs | http://www.linkedin.com/in/joelreymont -++--- -- 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] Converting between varchar and float when updating
I appreciate the advice. But in this particular case, other people have decided for me that I should not change the schema. I guess they have their reasons :) On Thu, Apr 28, 2011 at 5:40 PM, Alban Hertroys < dal...@solfertje.student.utwente.nl> wrote: > On 28 Apr 2011, at 15:26, Thomas Larsen Wessel wrote: > > > That leads me to two additional questions: > > > > 1) Can I specify how many decimals I want to be stored back from the > result? E.g. 2 / 3 = 0. but I want to just save 0.66. > > > > 2) Can I make a criteria that it should only update on the strings that > can be converted. Maybe smth. like: > > UPDATE foo SET bar = (bar::numeric * 2) WHERE bar::is_numeric; > > > > > > Thomas > > > > P.S.: Dmitriy asked why I save these values in VarChar. Well, I agree, > that they should be numeric, but I did not design the schema which is btw 10 > years old. > > Why don't you change that column to a new one with type numeric and offer > your application a view that converts it to varchar? With some rules (see > manuals), you could even make that "virtual column" writable. > It's quite possible that you'll have to rename the table as well, so that > the new view can have the name of the current table. > > ALTER TABLE foo RENAME TO realfoo; > ALTER TABLE realfoo ADD COLUMN realbar numeric(6,2); > UPDATE realfoo SET realbar = bar::numeric; > ALTER TABLE realfoo DROP bar; > CREATE VIEW foo AS SELECT foo, realbar::text as bar, baz FROM realbar; > CREATE RULE foo_insert AS ON INSERT TO foo >DO INSTEAD >INSERT INTO realfoo (foo, realbar, baz) VALUES (NEW.foo, > NEW.bar::numeric, NEW.baz); > CREATE RULE foo_update ...etc. > > That way you're calculating and sorting with actual numeric values, but > your application still sees a varchar field. > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:1258,4db98ab912121905226675! > > >
Re: [GENERAL] Converting between varchar and float when updating
Thanks a lot :) Both of the following work UPDATE foo SET bar = (bar::float * 2); removes trailing zeros on the decimal side, if no decimals dont show any "." UPDATE foo SET bar = (bar::numeric * 2); keeps decimals, i.e. 2.000 * 2 -> 4.000 That leads me to two additional questions: 1) Can I specify how many decimals I want to be stored back from the result? E.g. 2 / 3 = 0. but I want to just save 0.66. 2) Can I make a criteria that it should only update on the strings that can be converted. Maybe smth. like: UPDATE foo SET bar = (bar::numeric * 2) WHERE bar::is_numeric; Thomas P.S.: Dmitriy asked why I save these values in VarChar. Well, I agree, that they should be numeric, but I did not design the schema which is btw 10 years old. On Thu, Apr 28, 2011 at 12:18 PM, Vibhor Kumar < vibhor.ku...@enterprisedb.com> wrote: > > On Apr 28, 2011, at 3:41 PM, Dmitriy Igrishin wrote: > > > Only one point, Vibhor. I believe that varchar data type was chosen for > > exact storage of numeric values. According to chapter 8.1.3 of the doc. > > for this case the usage of numeric is preferred over floating data types. > Ah! Got it. This I have missed. > > Thanks & Regards, > Vibhor Kumar > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > vibhor.ku...@enterprisedb.com > Blog:http://vibhork.blogspot.com > >
[GENERAL] Converting between varchar and float when updating
I have a table with the following schema: CREATE TABLE foo (bar VARCHAR(32)); Every bar value has a format like a float, e.g. "2.5". Now I want that value multiplied by two and saved again as varchar. I was hoping to do smth like: UPDATE foo SET bar = TO_VARCHAR( TO_FLOAT(bar) * 2); -- INCORRECT How is that done? I know that the bar attribute ought to have type FLOAT, but I have to work with this legacy database. And anyway this table will rarely be updated. Sincerely, Thomas
Re: [GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect
On 4/27/2011 12:24 PM, Michael Nolan wrote: On Wed, Apr 27, 2011 at 10:42 AM, Thomas Harold mailto:thomas-li...@nybeta.com>> wrote: On 4/27/2011 9:16 AM, Thomas Harold wrote: - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubleshooting alerts in /var/log/messages either. Well, interestingly enough it is SELinux getting in the way, but not logging anything. Temporarily disabling SELinux suddenly makes it work. # echo 0 > /selinux/enforce (things now work) This does not surprise me, I've been upgrading a server to Fedora 14 and fighting SELInux every inch of the way. Setting up PostgreSQL on that box is coming up on the schedule, maybe forewarned is forearmed. :-) I've been using SELinux since '07, it still surprises me sometimes. Most issues come from mislabeled files (which gets fixed with "semanage fcontext" and "restorecon") and the targeted policies in RHEL5 are pretty bug-free after this many years. Of course, I just submitted a bug report against the SELinux policy for vsftpd this past month, so it's not perfect yet. In this case it took a full day for the lightbulb to go on and a few lucky searches later I found / remembered the booleans. The only thing that perplexes me at the moment is why SELinux is not logging an AVC denial in the audit.log file for that particular issue. I've seen it log AVC denials for mislabeled files, so the system is not 100% broken, it just was failing in this particular case. (As a follow up note: In order to make a boolean change permanent, I had to add the "-P" option to "setsebool". Things stopped working again after I restarted the server until I flipped the boolean again. # setsebool -P httpd_can_network_connect_db on Happily, everything now seems to be working with the PHP software package that I was configuring.) -- 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] PHP and PostgreSQL 9.0, pg_connect fails to connect
On 4/27/2011 11:42 AM, Thomas Harold wrote: On 4/27/2011 9:16 AM, Thomas Harold wrote: - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubleshooting alerts in /var/log/messages either. Well, interestingly enough it is SELinux getting in the way, but not logging anything. Temporarily disabling SELinux suddenly makes it work. # echo 0 > /selinux/enforce (things now work) So now I need to figure out why nothing showed up in audit.log. Turns out that it was a SELinux boolean that had not yet been turned on (specifically httpd_can_network_connect_db). # getsebool -a | grep 'http' allow_httpd_anon_write --> off allow_httpd_bugzilla_script_anon_write --> off allow_httpd_cvs_script_anon_write --> off allow_httpd_mod_auth_pam --> off allow_httpd_nagios_script_anon_write --> off allow_httpd_prewikka_script_anon_write --> off allow_httpd_squid_script_anon_write --> off allow_httpd_sys_script_anon_write --> off httpd_builtin_scripting --> on httpd_can_network_connect --> off httpd_can_network_connect_db --> off httpd_can_network_relay --> off httpd_can_sendmail --> on httpd_disable_trans --> off httpd_enable_cgi --> on httpd_enable_ftp_server --> off httpd_enable_homedirs --> on httpd_read_user_content --> off httpd_rotatelogs_disable_trans --> off httpd_setrlimit --> off httpd_ssi_exec --> off httpd_suexec_disable_trans --> off httpd_tty_comm --> on httpd_unified --> on httpd_use_cifs --> off httpd_use_nfs --> off # setsebool httpd_can_network_connect_db on (Lesson learned, when all else fails, start checking assumptions...) -- 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] PHP and PostgreSQL 9.0, pg_connect fails to connect
On 4/27/2011 9:16 AM, Thomas Harold wrote: - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubleshooting alerts in /var/log/messages either. Well, interestingly enough it is SELinux getting in the way, but not logging anything. Temporarily disabling SELinux suddenly makes it work. # echo 0 > /selinux/enforce (things now work) So now I need to figure out why nothing showed up in audit.log. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PHP and PostgreSQL 9.0, pg_connect fails to connect
I'm having trouble figuring out where this one is going wrong. It's a brand new install of PostgreSQL 9.0 from PGDG on a RHEL5 box, running Apache 2.2 and PHP 5.3 (from IUS). - PostgreSQL 9.0 is running and listening on the localhost. I can run pgAdmin III and connect to it over a SSH port-forward to the loopback. - nmap reports that pgsql is alive and listening on localhost:5432. - I can use the psql command to connect to the localhost and am able to login as the username/password that I'm using in the pg_connect() call. I can connect to the target database. I can create tables in the database/schema using that username (so pgsql roles don't seem to be the issue). - SELinux is running, but there are no denied messages in /var/log/audit/audit.log and no setroubleshooting alerts in /var/log/messages either. - The firewall doesn't seem to be the issue (due to the nmap query). - I've tried forcing IPv4 by specifying the database server as 127.0.0.1 instead of localhost. - No errors in the Apache logs either. - The server came with PHP 5.1.6 and I've tried out an early version of PHP 5.3, then upgraded to 5.3.6. All I'm getting back from pg_connect() is "Connect: failed to connect to database.". Is there a specific minimum version of the php-pgsql add-on that is needed in order to talk to a pgsql 9.0 database? Am I going to need to compile PHP from source (using the pgsql 9.0 developer packages)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Create Database automatacally after silent installation of postgresql. ?
hirenlad, 27.04.2011 09:47: Hiii Hey i m using postgresql 8.4. now i m install postgresql8.4 silently and it work properly, no issue during this process. Now problem is i want to create one database automatically after install postgresql 8.4. Can u plz inform me is it possible ? and if it is possible then how ? Simply run initdb after your installation is finished. -- 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] Table lock while adding a column and clients are logged in
Sven Haag wrote on 03.04.2011 16:13: Original-Nachricht Datum: Sun, 03 Apr 2011 15:37:17 +0200 Von: Thomas Kellerer An: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Table lock while adding a column and clients are logged in Alban Hertroys wrote on 03.04.2011 11:17: On 2 Apr 2011, at 12:44, Thomas Kellerer wrote: Even after a plain SELECT you should issue a COMMIT (or ROLLBACK) to end the transaction that was implicitely started with the SELECT. Sorry, but you're wrong about that. A statement that implicitly starts a transaction also implicitly COMMITs it. Otherwise single UPDATE and INSERT statements outside of transaction blocks would not COMMIT, and they do. AFAIK this is only true if you are running in auto commit mode. If you have auto commit turned off, a SELECT statement will leave the current transaction as "IDLE in transaction" not "IDLE" which means it *will* hold a lock on the tables involved that will prevent an ALTER TABLE. well, as we are using the default setting here (according to the manual this is ON) this shouldn't be the case?! The client defines the default behaviour, so it's your application that controls this. Did you check that you have sessions that are show as "IDLE in transaction" in pg_stat_activity? 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] Table lock while adding a column and clients are logged in
Alban Hertroys wrote on 03.04.2011 11:17: On 2 Apr 2011, at 12:44, Thomas Kellerer wrote: Even after a plain SELECT you should issue a COMMIT (or ROLLBACK) to end the transaction that was implicitely started with the SELECT. Sorry, but you're wrong about that. A statement that implicitly starts a transaction also implicitly COMMITs it. Otherwise single UPDATE and INSERT statements outside of transaction blocks would not COMMIT, and they do. AFAIK this is only true if you are running in auto commit mode. If you have auto commit turned off, a SELECT statement will leave the current transaction as "IDLE in transaction" not "IDLE" which means it *will* hold a lock on the tables involved that will prevent an ALTER TABLE. 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] Table lock while adding a column and clients are logged in
Alban Hertroys wrote on 03.04.2011 11:31: On 3 Apr 2011, at 11:22, Alban Hertroys wrote: Oracle and SQL server don't "suffer" from this because they do not handle DDL statements transactionally (I could be mistaken about SQL server, I don't know it all that well). I forgot to mention, if you perform DDL in Oracle all your currently running transactions are implicitly rolled back. Not quite. The current transaction is committed, not rolled back. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1001.htm#i2099120 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] Table lock while adding a column and clients are logged in
Sven Haag wrote on 02.04.2011 12:13: if i'm trying to add an additional column to a table in pgadmin while clients are logged in, pgadmin hangs. only if all cients are logged out it returns to the normal state. according to our consultant of the application this behavior doesn't appear in oracle or sql-server. how can i avoid this? When you add a new column to a table, the session needs an AccessExclusiveLock to this table, which means no one can have a lock on the object while you add the column. IOW, pgAdmin (and any other tool) will hang until no one works on the table. If it's a heavily used table, there's not much you can do about it, but wait. yes i saw that in the documentation too. i guess it doesn't matter what kind of lock level the odbc driver is using (row-lock)? but how can it be, that in oracle and sqlserver this is not happening? Actually SQL Server is even more prone to these kind of locks. And it will happen in Oracle just as well. PostgreSQL is more "sensible" when it comes to transactions that are not properly closed (Oracle is a bit more "forgiving" there - especially with SELECT statements). I bet you see a lot of "IDLE in transaction" entries in your pg_stat_activity (as opposed to plain "IDLE" entries). This means you are not ending (e.g. committing) your transactions properly. Even after a plain SELECT you should issue a COMMIT (or ROLLBACK) to end the transaction that was implicitely started with the SELECT. The ALTER TABLE should not be a problem if you only see "IDLE" sessions. 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] Install issues
Alex, 22.03.2011 17:33: Using Windows 7 64 bit. Tried to install 8.4 and 9.0and it fails right near the end when it tries to create or read the conf file. If I transfer my postgres 8.4 file over the upgrade takes but the postgres service doesn't exist so no communication occurs. Is there anyway to just install the postgres service? Or another solution? If you have the binaries and can start the server using pg_ctl, then you can always register the service using pg_ctl: pg_ctl register -N PostgreSQL -U windows_user -P secret_windows_password -D c:/Data/Postgres More details are in the manual: http://www.postgresql.org/docs/current/static/app-pg-ctl.html -- 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] Create unique index or constraint on part of a column
Ruben Blanco wrote on 08.03.2011 00:30: Hi: Is there anyway to create a unique index or constraint on part of a column? Something like this, but something that works ;-) ALTER TABLE invoices ADD CONSTRAINT cons UNIQUE (EXTRACT(YEAR FROM invoice_date), innvoice_number); Thanks for any help. Ruben, CREATE UNIQUE INDEX idx_cons ON invoices (EXTRACT(YEAR FROM invoice_date), innvoice_number); The only difference to a unique constraint is, that it cannot be used as the target of a foreign key constraint. 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] select DISTINCT not ordering the returned rows
Ioana Danes, 02.03.2011 21:35: Hi Everyone, I would like to ask for your help finding a temporary solution for my problem. I upgraded postgres from 8.3 to 9.0.3 and I have an issue with the order of the returned rows. The database is free to return rows in any order it thinks is most efficient and you may never rely on any implicit ordering. If you need your rows sorted in a specific way, you have to use an ORDER BY clause. Everything else is doomed to fail someday. 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] why is there no TRIGGER ON SELECT ?
Melvin Davidson, 22.02.2011 15:42: I know a function can be used, but the point is to log a table whenever "someone else" does a SELECT on it. It cannot be depended on that a user will include that (or any specific function in a SELECT. iow, when any user does "SELECT ... FROM tablex;" then logging should occur. You can force users to use the function. Remove the SELECT privilege on the table for the user, create a view that uses the function and then grant select on the view to the users. Thus they won't even notice they are going through a function and you can still audit the SELECT. The function needs to be created with SECURITY DEFINER though. The downside of this is, that this only works if the result set isn't too large. Because all rows that are returned by the function will be first buffered on the the server before they are returned to the client. 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] Hide db name and user name in process list arguments
Gavrina, Irina, 16.02.2011 15:50: Hi, On Unix systems Postgres process list can beaccessible through‘ps’ utility: ps auxww | grep ^postgres $ ps auxww | grep ^postgres postgres 9600.01.16104 1480 pts/1SN 13:17 0:00 postmaster -i postgres 9630.01.17084 1472 pts/1SN 13:17 0:00 postgres: stats buffer process postgres 9650.01.16152 1512 pts/1SN 13:17 0:00 postgres: stats collector process postgres 9980.02.36532 2992 pts/1SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle postgres10030.02.46532 3128 pts/1SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting postgres10160.12.46532 3080 pts/1SN 13:19 0:00 postgres: tgl regression [local] idle in transaction And each client connection has its command line which displays in form: postgres: /user//database//host//activity/ Is there any way to hide dbname and user name in displayed arguments of client connections? I think that's what the configuration property update_process_title is for. http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-UPDATE-PROCESS-TITLE 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] Schema version control
Bill Moran wrote on 11.02.2011 00:37: Anyway ... based on nothing more than a quick scan of their quickstart page, here are the differences I see: * Liquibase is dependent on you creating "changesets". I'm sure this works, but we took a different approach with dbsteward. dbsteward expects you to maintain XML files that represent the entire database, then dbsteward does the work of figuring out what changed. Our opinion was that svn already does the work of tracking changes, why reinvent the wheel. That sounds like a very nice feature. * Looks like liquibase requires you to talk to the database to push the changes? dbsteward outputs a DDL/DML file that you can push in whatever way is best. This is important to us because we use Slony, and DDL changes have to be submitted through EXECUTE SCRIPT() No, Liquibase can also emit the SQL that it would execute. * dbsteward has built-in Slony support (i.e. it will make slony configs as well as slony upgrade scripts in addition to DDL/DML) * liquibase has a lot more supported platforms at this time. dbsteward only supports PostgreSQL and MSSQL (because that's all that we needed) but I expect that other support will come quickly once we release it. * Does liquibase support things like multi-column indexes and multi- column primary keys? dbsteward does. Yes without problems (including of course the necessary foreing keys) Anyway ... sorry for the teaser on this, but we're trying to get through all the hoops the company is requiring us to do to release it, and we think we're on track to be ready by PGCon, so there'll be a website up as soon as we can get it. Thanks for the feedback, I would really like to see it. The approach that you do not record the changes but simply let the software find them seems like a very nifty feature. I wonder how you detect renaming a table or a column? On which programming language is dbstewart based? 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] Schema version control
Bill Moran wrote on 10.02.2011 23:59: The overview: You store your schema and data as XML (this is easy to migrate to, because it includes a tool that makes the XML from a live database) Keep your XML schema files in some RCS. When it's time for a new deployment, you run the dbsteward tool against the schema XML and it turns it into DDL and DML. When it's time for an upgrade, you run the dbsteward tool against two schema XML files, and it calculates what has changed and generates the appropriate DDL and DML to upgrade. This very much sounds like Liquibase. Do you happen to know any differences? 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] Schema version control
Royce Ausburn wrote on 10.02.2011 22:38: I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many different versions of the database. We are quite happy with Liquibase. You can simply run it against a database and tell it to migrate it to "Version x.y" As it keeps track of all changes applied it automatically knows what to do. I can handle static data as well as stored procedure and any custom SQL. 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] many schemas or many databases
Szymon Guz, 08.02.2011 09:30: Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as one database with many schemas. From the application point of view it could be easier to have different databases, as for now the applications log in into different schemas, so this behavior wouldn't change. Do you see any drawbacks of any of the solutions? I think the question is: do you have queries that retrieve data from different schemas in Oracle? If so then the only way to go in PostgreSQL is to use multiple schemas. If you don't need cross-schema/database queries then I don't think there is none of the solution is particular better than the other. Both have advantages and disadvantages (as described by the other posters) 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] many schemas or many databases
hi, i would prefer many schemas. advantages: - one backup/restore for all (or selective) - one connection pool - simple access to all schemas regards thomas Am 08.02.2011 09:30, schrieb Szymon Guz: Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as one database with many schemas. From the application point of view it could be easier to have different databases, as for now the applications log in into different schemas, so this behavior wouldn't change. Do you see any drawbacks of any of the solutions? regards Szymon -- 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 extract a value from a record using attnum or attname?
Kevin Grittner wrote on 04.02.2011 23:27: PL/pgSQL seems tantalizingly close to being useful for developing a generalized trigger function for notifying the client of changes. I don't know whether I'm missing something or whether we're missing a potentially useful feature here. Does anyone see how to fill in where the commented question is, or do I need to write this function in C? Alternatively, I guess, I could write a C-based quote_literal(record, int2) and/or quote_literal(record, name) function to use there. create or replace function tcn_notify() returns trigger language plpgsql as $tcn_notify$ declare keycols int2vector; keycolname text; channel text; payload text; begin select indkey from pg_catalog.pg_index where indrelid = tg_relid and indisprimary into keycols; if not found then raise exception 'no primary key found for table %.%', quote_ident(tg_table_schema), quote_ident(tg_table_name); end if; channel := 'tcn' || pg_backend_pid()::text; payload := quote_ident(tg_table_name) || ',' || substring(tg_op, 1, 1); for i in array_lower(keycols, 1)..array_upper(keycols, 1) loop select quote_ident(attname) from pg_catalog.pg_attribute where attrelid = tg_relid and attnum = keycols[i]::oid into keycolname; payload := payload || ',' || keycolname || '='; -- How do I append the quote_literal(value) ? end loop; perform pg_notify(channel, payload); return null; -- ignored because this is an AFTER trigger end; $tcn_notify$; It would surprise me if nobody else has wanted to do something like this. The only reason we hadn't hit it yet is that we'd been striving for portable code and had been doing such things in a Java tier outside the database. If you don't really need the key = value pairs, you can simply use: payload := payload || 'values: ' || ROW(old.*); this will append everything in one operation, but not in the col=value format 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] Subselect AS and Where clause
Uwe Schroeder, 26.01.2011 08:34: I have a query like this: SELECT a,b,c, (select problem from other_table where id=a) as problem FROM mytable WHERE a=1 So far so good. Actually "problem" always resolves to one record, so it's not the "multiple records returned" problem. What I try to do is this: SELECT a,b,c, (select problem from other_table where id=a) as problem FROM mytable WHERE a=1 and problem = 3 see the "problem=3" part in the where clause? The error I get is SQLError: (ProgrammingError) column "problem" does not exist You need to wrap the whole SELECT in order to be able to use the column alias: SELECT * FROM ( SELECT a, b, c, (select problem from other_table where id=a) as problem FROM mytable ) t WHERE a=1 AND problem = 3 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] How can I find a schema that a table belong to?
Tom Lane, 19.01.2011 19:19: Given a bare table name, how can I recover the schema qualified name with whatever the current search path happens to be? SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_table' ; That's not going to work, at least not in the interesting case where you have more than one candidate table --- that SELECT will list all of 'em. What about something like this: SELECT tbl.table_schema, tbl.table_name, pe.path_position FROM information_schema.tables tbl JOIN ( SELECT path_element, row_number() over () as path_position FROM ( SELECT trim(unnest(string_to_array(setting, ','))) as path_element FROM pg_settings WHERE name = 'search_path' ) t ) pe on tbl.table_schema = pe.path_element WHERE tbl.table_name = 'your_table' ORDER BY pe.path_position; This will list each table together with the index of the schema in the search path in the order of the schemas listed in the search path. The only thing I'm unsure about is whether unnest() will always preserve the order of the array. 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] How can I find a schema that a table belong to?
Tom Lane, 19.01.2011 19:19: SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_table' ; That's not going to work, at least not in the interesting case where you have more than one candidate table --- that SELECT will list all of 'em. Ah, right. I was a buit too quick with my answer. 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] How can I find a schema that a table belong to?
Jerry LeVan, 19.01.2011 17:35: So I guess the question is: Given a bare table name, how can I recover the schema qualified name with whatever the current search path happens to be? SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_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 generate unique invoice numbers for each day
Tomas Vondra wrote on 16.01.2011 23:41: Yes, locking may in some cases lead to deadlocks, that's true. For example creating several invoices (for different days) in a single transaction may lead to a deadlock. But that's a feature, not a bug. Hmm, a single transaction cannot deadlock itself as far as I know. A deadlock can only happen between two different transactions (T1 locks R1, waits for R2, T2 locks R2 waits for R1) 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] OOO and postgres
Rich Shepard wrote on 07.01.2011 18:56: The data type is VARCHAR(), not character varying[]. character varying is a synonym for varchar, so the definition character varying[] is valid. It defines an array of varchar and is equivalent to varchar[] But I doubt that this is what the OP meant ;) 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] Query to find sum of grouped counts from 2 tables
Satish Burnwal (sburnwal) wrote on 07.01.2011 11:15: I have 2 tables containing the data for same items: STORE1 - Id typeitems - 1 FOOD10 2 FOOD15 3 SOAP20 STORE2 - Id typeitems - 1 FOOD15 3 SOAP10 4 PAPER 25 5 SOAP12 What I am looking for is one single query that would return me TYPE-wise total number of items from both the tables. UNION does not help me. I want the result as: Hmm, I don't see why UNION shouldn't work: SELECT type, sum(items) as count FROM ( SELECT type, items FROM store1 UNION ALL SELECT type, items FROM store2 ) t GROUP BY type -- 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] Need advise for database structure for non linear data.
Hello, Am 03.01.11 14:14, schrieb Andre Lopes: Hi, Thanks for the reply's. I was tempted to accept the Rodoslaw Smogura proposal. There will be about 100 websites to capture data on daily basis. Each website adds per day(average) 2 articles. Thomas talked about the noSQL possibility. What do you think would be better? I have no experience in noSQL and that could be a weakness. Imho RDBMS do a very good job in managing data on a relational basis. However - there are alternatives and use cases for 'em and there is no holy grail... Not having any experience is a good point for not using it in production :-). However, if you've time to spare, looking into database design (plain-sql and not-only-sql) will help. I don't think that you get in trouble with a few hundered rows per day, but keep in mind, what queries are used. 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] Need advise for database structure for non linear data.
Hello, Am 03.01.11 12:46, schrieb Radosław Smogura: I can propose you something like this: website(id int, url varchar); attr_def (id int, name varchar); attr_val (id int, def_id reference attr_def.id, website_id int references website.id, value varchar); If all of your attributes in website are single valued then you can remove id from attr_val and use PK from website_id, def_id. Depending on your needs one or many from following indexes: attr_val(value) - search for attributes with value; (...) Probably you will use 2nd or 3rd index. Example of search on website select d.name, v.value from attre_def d join attr_val v on (v.def_id = d.id) join website w on (v.website_id = w.id) where d.name = '' and w.url='http://somtehing' Imho its hard - (if not impossible) to recommand a specific database scheme (incl indexes) without knowing the applications taking plance behind it. Your schema is nice for specific querying, but might blow up if lots of data is stored in the database (joins, index-building might be time consuming). On the other hand, google put some effort into their "BigTable" http://en.wikipedia.org/wiki/BigTable for storing tons of data... Thus - it all depends on the usage :-) 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] Need advise for database structure for non linear data.
Andre Lopes wrote on 03.01.2011 12:11: array( 'name' => 'Don', 'age' => '31' ); array( 'name' => 'Peter', 'age' => '28', 'car' => 'ford', 'km' => '2000' ); In a specific website search I will store only "name" and "age", and in other website I will store "name", "age", "car" and "km". I don't know If I explain weel my problem. My english is not very good. That's exactly what the hstore data type supports: http://www.postgresql.org/docs/current/static/hstore.html 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] Need advise for database structure for non linear data.
Hello, Am 03.01.11 12:11, schrieb Andre Lopes: Hi, I need advise about a database structure. I need to capture data from the web about one specific subject on few specific websites and insert that data to a database. I have done this question here before, but I think I have not explained very well. What I mean with non linear data is the following: array( 'name' => 'Don', 'age' => '31' ); array( 'name' => 'Peter', 'age' => '28', 'car' => 'ford', 'km' => '2000' ); In a specific website search I will store only "name" and "age", and in other website I will store "name", "age", "car" and "km". I don't know If I explain weel my problem. My english is not very good. In theory, using a single table having three columns (array-id,key,value) will suit your needs. However, providing a simple key/value store is not the idea behind DBMS like postgres ... See: http://en.wikipedia.org/wiki/NoSQL http://en.wikipedia.org/wiki/Relational_database_management_system Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
pgloader an Indexes / was: Re: [GENERAL] CSV-bulk import and defaults
Hello, Am 03.01.11 00:06, schrieb Adrian Klaver: On Sunday 02 January 2011 2:22:14 pm Thomas Schmidt wrote: well, I'm new to postgres and this is my post on this list :-) Anyway, I've to batch-import bulk-csv data into a staging database (as part of an ETL-"like" pocess). The data ought to be read via STDIN, however for keeping in simple and stupid, saving it to a file and importing afterwards is also an option. Sticking my nose into the docs, I noticed that copy[1] as well as pg_import[2] are able to do it. However, there are some additional columns of the staging table (job id, etc.) that have to be set in order to identify imported rows. These attributes are not part of the data coming from STDIN (since its meta-data) and I see no way for specifying default values for "missing" cvs columns. (imho copy and pg_bulkload will use table defaults for missing rows - do I miss something?). [1] http://www.postgresql.org/docs/9.0/static/sql-copy.html [2] http://pgbulkload.projects.postgresql.org/pg_bulkload.html Check out pgloader: http://pgloader.projects.postgresql.org/ Thanks a lot - that's what I need. :-) Btw. What about indexes? http://www.postgresql.org/docs/9.0/interactive/populate.html suggests to remove indexes before importing via copy (for obvious reasons). Does pgloader take indexes into account or do I need to handle 'em manually? Thanks in adance, 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] CSV-bulk import and defaults
Hello, well, I'm new to postgres and this is my post on this list :-) Anyway, I've to batch-import bulk-csv data into a staging database (as part of an ETL-"like" pocess). The data ought to be read via STDIN, however for keeping in simple and stupid, saving it to a file and importing afterwards is also an option. Sticking my nose into the docs, I noticed that copy[1] as well as pg_import[2] are able to do it. However, there are some additional columns of the staging table (job id, etc.) that have to be set in order to identify imported rows. These attributes are not part of the data coming from STDIN (since its meta-data) and I see no way for specifying default values for "missing" cvs columns. (imho copy and pg_bulkload will use table defaults for missing rows - do I miss something?). Thus - do you have any clue on designing an fast bulk-import for staging data? Thanks in advance, Thomas [1] http://www.postgresql.org/docs/9.0/static/sql-copy.html [2] http://pgbulkload.projects.postgresql.org/pg_bulkload.html -- 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] 2 versions of an entity worth distinct table?
gvim wrote on 27.12.2010 02:47: If a table representing contact details can have 2 but no more than 2 email addresses is it really worth factoring-out email addresses to a separate table. If you are absolutely sure you will never have more than two, then I agree, you don't need to create a 1:N relationship for that. Especially because guaranteeing that there will never be more than two in the N part is quite complicated. 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] Constraining overlapping date ranges
Filip Rembiałkowski, 22.12.2010 14:28: INSERT INTO tbl SELECT 1, '2010-01-01', '2010-12-31'; INSERT 0 1 I'm curious why you use this syntax as you have fixed values and could use the "standard" VALUES construct without problems: INSERT INTO tbl VALUES (1, '2010-01-01', '2010-12-31'); 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] A cronjob for copying a table from Oracle
Alexander Farber, 10.12.2010 12:53: On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer wrote: And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in PostgreSQL and everything should be fine. Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp? (I realize that this more an Oracle question, sorry) What format string should I take for Oracle's to_date() function, I don't see a format string to get epoch seconds there I have no idea what you are doing in PHP, but why don't you simply generate a valid date/time literal for Postgres using the to_char() function? Something like SELECT 'TIMESTAMP '''||to_char(QDATETIME, '-MM-DD HH24:MI:SS')||'''' FROM qtrack; That literal can directly be used in an INSERT statement for PostgreSQL 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] A cronjob for copying a table from Oracle
Alexander Farber, 10.12.2010 12:02: I'm preparing a PHP-script to be run as a nightly cronjob and will first find the latest qdatetime stored in my local PostgreSQL database and then just "select" in remote Oracle, "insert" into the local PostgreSQL database in a loop. But I wonder if there is maybe a cleverer way to do this? And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? Oracle's DATE includes a time part as well. So simply use a timestamp in PostgreSQL and everything should be fine. 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] alter table add column - specify where the column will go?
Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. I always try to convince people of this as well, but when they ask me under which circumstances this could happen, I can't think of a proper example. Does anybody have an example that would show this? 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] alter table add column - specify where the column will go?
Alexander Farber, 24.11.2010 08:49: Why do you want to do anything like that? Easier to read... login, logout I understand the "easier to read" part. But what do you mean with "login, logout"? 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] alter table add column - specify where the column will go?
Alexander Farber, 24.11.2010 08:42: is there a syntax to add a column not at the last place No, because the order of the column is irrelevant (just as there is no order on the rows in a table) Simply select them in the order you like to have. 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] Table name with umlauts
Tom Lane wrote on 22.11.2010 20:36: I had the idea that the Windows version of psql was smart enough to set client_encoding based on the console encoding it finds itself running under, but I might be wrong about that. Or maybe you did something that overrode its default? I changed to "chcp 1252" before running psql (I tried several other encodings as well) Try "set client_encoding = win1252", then. Thanks for the hint, unfortunately psql still shows the same behaviour. 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] Table name with umlauts
Tom Lane wrote on 22.11.2010 19:25: Thomas Kellerer writes: I'm curious why the following is not working: postgres=# show client_encoding; client_encoding - UTF8 (1 row) postgres=# create table umlaut_test_ö (id integer); ERROR: invalid byte sequence for encoding "UTF8": 0xf6202869 It looks to me like your console is not in fact producing UTF8; it's representing ö as 0xf6, which I think is right for Latin1. Select the proper client_encoding. I assume you mean the encoding in the console? I changed to "chcp 1252" before running psql (I tried several other encodings as well) And why does the JDBC driver return this incorrectly as well? Create table and drop table is working through JDBC, but displaying the table names does not. 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] Table name with umlauts
Hi, I'm curious why the following is not working: c:\psql postgres postgres psql (9.0.1) Type "help" for help. postgres=# select version(); version - PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 32-bit (1 row) postgres=# select pg_encoding_to_char(encoding) from pg_database where datname = 'postgres'; pg_encoding_to_char - UTF8 (1 row) postgres=# show client_encoding; client_encoding - UTF8 (1 row) postgres=# create table umlaut_test_ö (id integer); ERROR: invalid byte sequence for encoding "UTF8": 0xf6202869 postgres=# (it doesn't work either when I quote the table name using "umlaut_test_ö") When I run the same create table using a JDBC based tool the table *is* created but the table name does not show up correctly when I use DatabaseMetaData.getTables(). pgAdmin does not show this table correctly and after creating it through JDBC, psql doesn't show the table name correctly either: postgres=> \d umlaut* Table "public.umlaut_test_ã¶" Column | Type | Modifiers +-+--- id | integer | I initially posted this on the JDBC mailing list because I noticed this with Java, but it seems that it's not a JDBC problem. Could this be a Windows problem? Note: I don't really want to use such a table name, I'm just wondering if this _should_ work. 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] newbie question - delete before insert
Grant Mckenzie wrote on 20.11.2010 07:00: How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's key does not exist in the database else update the existing row? You can simply send the UPDATE, if nothing was updated, it's safe to send the INSERT 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] More then 1600 columns?
Peter Bex, 12.11.2010 08:36: What can also work extremely well is storing the data in an array. If you need to access the array based on more meaningful keys you could store key/index pairs in another table. The hstore module would also be a viable alternative - and it's indexable as well. 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] Schema tool
Aram Fingal wrote on 11.11.2010 22:45: I was thinking of reporting back to this forum with advantages/disadvantages of each tool, as I see it, but realized that I was rapidly getting too far off topic for a list focused specifically on PostgreSQL. I don't think this woul be off-topic here if you post your experience using those tools together with PostgreSQL Actually I think it would be worthwhile documenting your experience in the PostgreSQL Wiki as well: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools 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] Syntax of: alter table ... add constraint ...
Alexander Farber, 08.11.2010 15:50:
And then I realized that I actually want
medals smallint default 0 check (medals>= 0)
So I've dropped the old constraint with
alter table pref_users drop constraint "pref_users_medals_check";
but how can I add the new contraint please? I'm trying:
alter table pref_users add constraint pref_users_medals_check (medals>= 0);
ERROR: syntax error at or near "("
LINE 1: ...pref_users add constraint pref_users_medals_check (medals>=...
^
and many combinations of quotes and "check" inbetween,
but can't find the correct syntax
That should work:
alter table pref_users add constraint pref_users_medals_check check check (medals
>= 0);
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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
Adrian Klaver, 02.11.2010 23:23: Before I move or rename '/var/lib/postgres/data', what version of PostgreSQL should I be at? 8.4 or 9.0? Actually both, because pg_upgrade needs the binaries of the old *and* new version. Part of the confusion Carlos is experiencing is that he is caught between two upgrade suggestions. At this point he is most of the way to doing it the traditional way, dump/restore. While pg_upgrade could be of use, it does not solve the immediate problem, which how to restore the dump file :) Ah, right. But on the other hand, he only dumped a single database which will not include e.g. users and roles. So if he needs to restore users and privileges from the original 8.4 installation there is no way around re-installing the 8.4 binaries. 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
Carlos Mennens, 02.11.2010 22:37: Before I move or rename '/var/lib/postgres/data', what version of PostgreSQL should I be at? 8.4 or 9.0? Actually both, because pg_upgrade needs the binaries of the old *and* new version. -- 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] Temporary schemas
Merlin Moncure wrote on 01.11.2010 23:13: On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer wrote: The problem is, that the JDBC driver only returns information about the temp tables, if I specify that schema directly. Have you filed a bug report to jdbc yet? :-D. I thought about it initially, but then realized that it works as documented by the JDBC API. When requesting the table information without specifying a schema, it is returned. But in my application I use the current schema to request information about non-qualified tables which obviously fails as the current schema is usually public or another user schema but never pg_temp_xxx. So even though a select from a temp table (whithout a schema) works fine from within JDBC, retrieving metadata only works when either specifying no schema, or the correct one - which is a bit confusing but absolutely according to the JDBC specs. 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] Temporary schemas
Merlin Moncure wrote on 01.11.2010 21:13: On Mon, Nov 1, 2010 at 6:46 AM, Thomas Kellerer wrote: Hello, I have created a temporary table using create temporary table foo ( id integer ); and noticed this was created in a schema called "pg_temp_2" My question is: is this always "pg_temp_2"? Or will the name of the "temp schema" change? If it isn't always the same, is there a way I can retrieve the schema name for temporary tables? Curious why you'd want to do this -- temporary magic schemas are an implementation artifact, and there shouldn't ever be a reason to directly reference them. Yes and no ;) The problem is, that the JDBC driver only returns information about the temp tables, if I specify that schema directly. 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] Replication
Jonathan Tripathy wrote on 01.11.2010 21:12: 9.0 has streaming replication and "Hot Standby" http://www.postgresql.org/docs/current/static/hot-standby.html http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION But does that not only allow "read-only" things to work on the standby? But you didn't ask for read/write on the standby, only for a standby that can take of the master once the master fails: "must support INSERTS and UPDATES as well (once the master has failed)" That's exactly what the hot standby does: As long as it is in standby mode it's read-only. Once the failover has happened the standby is the new master and will allow read/write access. 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] Replication
Jonathan Tripathy wrote on 01.11.2010 20:53: Hi Everyone, I'm looking for the best solution for "Hot Standbys" where once the primary server fails, the standby will take over and act just like the master did. The standby must support INSERTS and UPDATES as well (once the master has failed) Are there any solutions like this? Looking on the Postgresql site, all the standby solutions seem to be read only.. 9.0 has streaming replication and "Hot Standby" http://www.postgresql.org/docs/current/static/hot-standby.html http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION 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] Temporary schemas
Thom Brown wrote on 01.11.2010 12:33: You can use: SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema(); to get the name of the current temporary schema for your session. Thanks that's what I was looking for. 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] Temporary schemas
Hello, I have created a temporary table using create temporary table foo ( id integer ); and noticed this was created in a schema called "pg_temp_2" My question is: is this always "pg_temp_2"? Or will the name of the "temp schema" change? If it isn't always the same, is there a way I can retrieve the schema name for temporary tables? 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] Implementing replace function
Alexander Farber wrote on 31.10.2010 09:22: Hello Postgres users, to mimic the MySQL-REPLACE statement I need to try to UPDATE a record and if that fails - INSERT it. There is actually an example of this in the PG manual ;) http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Here is another solution based on triggers: http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html 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] Generate a dynamic sequence within a query
Alban Hertroys, 21.10.2010 13:43: I'm currently using WebFOCUS at work and they have a LAST operator, referring to the value a column had in the last returned row. That's pretty good for stuff like this, so I wonder if it wouldn't be beneficial to have something like that in Postgres? Already there since 8.4 ;) Look into the windowing functions (in Oracle they are called analytical functions) http://www.postgresql.org/docs/current/static/tutorial-window.html 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] Re: Updates, deletes and inserts are very slow. What can I do make them bearable?
Tim Uckun, 21.10.2010 07:05: No, it isn't. This is a three-way join between consolidated_urls, cu, and tu --- the fact that cu is the same underlying table as cu is an alias for consolidated_urls. tu is an alias for trending_urls. There are only two tables in the query. Yes, but consolidated_urls is there twice. Which makes it three relations involved in the update (consolidated_urls, cu and tu) That's what Tom meant and that's where your cartesian product comes from. select count(cu.id) from consolidated_urls cu inner join trending_urls tu on tu.consolidated_url_id = cu.id That select is not the same as your UPDATE statement. If your update statement was re-written to a plain SELECT it would be something like select count(consolidated_urls.id) from consolidated_urls, consolidated_urls cu inner join trending_urls tu on tu.consolidated_url_id = cu.id See the difference? 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] Adding a New Column Specifically In a Table
Carlos Mennens, 13.10.2010 20:06: OK so I have read the docs and Google to try and find a way to add a new column to an existing table. My problem is I need this new column to be created 3rd rather than just dumping this new column to the end of my table. I can't find anywhere how I can insert my new column as the 3rd table column rather than the last (seventh). Does anyone know how I can accomplish this or if it's even possible. Seems like a common task but I checked the documentation and may have missed it in my reading. ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; Ah sadly I just found this after I pressed 'send' and realized PostgreSQL doesn't support it...that sucks :( The position of a column in a table has no meaning whatsoever - just like rows have no "position" as well. If you want columns returned in a specific order, simply put them in the desired order in your SELECT statement. 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] IEEE 754-2008 decimal numbers
Hi Has anyone done any work on IEEE 754-2008 decimal types for PostgreSQL? I couldn't find anything, so I was thinking it might be a fun exercise for learning about extending PostgreSQL with user defined types. My first goal is to be able to store decimal numbers with a smaller disk footprint than NUMERIC. I was thinking I would start out by defining types DECIMAL32 and DECIMAL64 and some casts between those types and NUMERIC. (A more ambitious project for later would be defining arithmetic operators etc using compiler/hardware support). 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
Re: [GENERAL] How to I relocate the Postgresql data directory
Vorpal, 07.10.2010 02:53: PostgreSQL was installed as part of other software. The data folder is a subfolder of D:\Program Files\ Specifically: "D:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "D:\Program Files\PostgreSQL\8.3\data\" For various reasons I would like the data directory to be: G:\PostgreSQL\8.3\data\" I created this directory, and changed the line in the service startup, however the service threw an error on startup. What is the correct procedure for re-positioning the data directory. Did you make sure the Postgres Windows user (which is starting the service) has "full access" to the new directory? 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] Trying to figure out why these queries are so slow
Hi, just a guess: Counting is slow, since it needs to check all rows. Explained here: http://wiki.postgresql.org/wiki/Slow_Counting Thomas Güttler Tim Uckun wrote: > I have two tables. Table C has about 300K records in it. Table E has > about a million records in it. Today I tried to run this query. > > update C > set result_count = X.result_count > from C > inner join (select c_id, count(c_id) as result_count > from E > where c_id is not null > group by c_id) as X > on C.id = X.c_id > > All the fields mentioned are indexed. In the case of Table C it's the > primary key. In the case table E it's just an index (non unique). > > I let this query run for about three hours before I cancelled it. > ... -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- 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] PG website testing
Thom Brown wrote on 04.10.2010 23:24: Do you see the reduction in size compared to the live site an issue? No, not at all. I just wanted to mention it, in case you are interested. I think both sizes are just fine. 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] PG website testing
Thom Brown wrote on 04.10.2010 20:40: Hi all, We're currently testing a new javascript change on the PostgreSQL docs. This is to make sure monospaced fonts still appear at a reasonable size between browsers. I'd appreciate it if some of you could do some browser testing. http://magnus.webdev.postgresql.org/ Only docs for 8.3 and below are available. Please also check the main site for font issues, and post any issues you find here. Looks good on Firefox 3.6, Windows XP But I can't see a big difference to the live documents In fact the fixed font e.g. on http://www.postgresql.org/docs/current/static/ddl-default.html is a tiny bit bigger than on http://magnus.webdev.postgresql.org/docs/8.3/static/ddl-default.html Looking at the CSS, the current live site is configured to use 1.4em for the fixed font, whereas your site uses 1.2em so it does make sense that it's a little bit large on the live site. 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] PostgreSQL 9.0 (x86-64) and Windows 7 (x86-64) - Unable to install
(This is the second time I send this, as the first message apparently did not make it) Dr. Peter Voigt, 30.09.2010 14:42: If there are no other users out there with comparable problems I could give the ZIP-installer a try under: http://www.enterprisedb.com/products/pgbindownload.do There is a file postgresql-9.0.0-1-windows_x64-binaries.zip. I did not yet try this because I am new to PostgreSQL. It's actually not that hard ;) You first need to create a "datadirectory" using "initdb.exe" http://www.postgresql.org/docs/current/static/app-initdb.html Make sure the user account under which the server will be running has full (write) access to that directory. For me it is enough to run initdb" -D "/path/to/datadir" --lc-messages=English -U postgres E UTF8 -A md5 - how to start the database from the command line, Once the data directory has been created, simply use pg_ctl: pg_ctl -D "/path/to/datadir" start http://www.postgresql.org/docs/current/static/app-pg-ctl.html - how to setup the PostgreSQL service from the command line, pg_ctl -D "/path/to/your/datadir" register (see the above link to the manual) - what registry entries are required. None. If you can answer the above three questions (each with one sentence), I will immediately start installation and tests, because I hope - from my short but good PostgreSQL 9.0 experiences under Linux - that just the installer fails on my system but not the database system itself. I have put the above statements in some very simple batch files to be able to easily repeat these steps 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] Documentation enhancement
Hi, I would like to suggest to enhance the documentation of the CREATE VIEW statement. I think the fact that a "SELECT *" is internally stored as the expanded column list (valid at the time when the view was created) should be documented together with the CREATE VIEW statement. Especially because the example does use SELECT * to create the view. 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] Post Install / Secure PostgreSQL
Carlos Mennens wrote on 10.09.2010 17:53: On Fri, Sep 10, 2010 at 11:33 AM, Richard Broersma wrote: I don't believe there is a script like this. However, I would say that out of the box, PostgreSQL is so secure that some people cannot figure out how to log in. :) I agree and I am just now learning this. I can't seem to find out how to login to the database. I am using 'psql -U root' however during my installation there may have been a default password used which I am not aware of. I need to read the docs and see how to login to the database. Normally the superuser is called "postgres". I don't think there is a account named "root" after a default installation. 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] PostgreSQL article online - PDF
Hello, Pavel Stehule wrote an article for Linux Technical Review which is published now. The language is German. It's regarding PostgreSQL and its possibilities with e.g. Stored Procedures. The first 100 downloads are sponsored by Linux Technical Review. Feel free to download the PDF and to read it. Its available here: http://www.linuxtechnicalreview.de/Vorschau/%28show%29/Themen/Datenbanken/PostgreSQL-erweitern Bests Thomas -- Thomas Uzunoff Linux New Media AG, Putzbrunnerstr. 71, 81739 München, Germany Phone: +49 89 9934 1137 Fax: +49 89 9934 1199 tuzun...@linuxnewmedia.de -http://www.linuxnewmedia.de Linux New Media - The Pulse of Linux Malaga - Manchester - München - Sao Paulo - Warszaw --- Sitz der Gesellschaft: Putzbrunnerstr. 71, 81739 München Amtsgericht München: HRB 129161 Vorstand: Brian Osborn, Hermann Plank Aufsichtsratsvorsitzender: Rudolf Strobl -- 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] Jira and PostgreSQL
Jayadevan M, 30.08.2010 12:13: Our admin team just moved our Jira from MySQL to PostgreSQL, but I can't recall the exact reasons anymore. So far we do not have any problems (from an end-user perspective that is) Thanks for the reply. We plan to use it for our helpdesk, expecting it to be up and running 24*7 (or as close as possible), may be with db replication to manage db crashes , paid support from Jira to take care of issues at the product side and so on. Are you using Jira with PostgreSQL in an env with similar availability requirements? No. We are only doing project tracking with it, so it's definitely not 24*7 We have about 250 users, but of course not all of them are active all the time 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] Jira and PostgreSQL
Jayadevan M, 30.08.2010 11:26: Hello all, Has any one worked with Jira on PostgreSQL? We are considering Jira implementation for our organization (about 1500 users). The question is - "Jira on MySQL or Jira on PostgreSQL?" Any tips/suggestions are welcome. We do not have much expertise in either of these databases. Oracle, the database we have expertise in, cannot be considered because of the license costs :) Our admin team just moved our Jira from MySQL to PostgreSQL, but I can't recall the exact reasons anymore. So far we do not have any problems (from an end-user perspective that is) 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] Too much logging
Mike Christensen, 27.08.2010 11:39: Hi all - I've noticed my log files for Postgres are getting way too big, since every single SQL statement being run ends up in the log. However, nothing I change in postgresql.conf seems to make a bit of difference.. I've tried restarting postgres, deleting all the existing logs, etc. No matter what I do, every statement is logged. What I want is to only log SQL statements that result in errors. Here's my config options: log_destination = 'stderr' logging_collector = on client_min_messages = error log_min_messages = error log_error_verbosity = default log_min_error_statement = error log_min_duration_statement = 3000 log_statement = 'all' Pretty much everything else log related is commented out.. What am I doing wrong? Thanks! log_statement = 'all' should be log_statement = 'none' 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] Wrong "ORDER BY" on a numeric value result
Stefan Wild wrote on 15.08.2010 10:36: column is numeric, but upper() works on text, and returns text, so your numeric column got casted to text by using upper (which is pointless anyway - there is no "upper" version of digits). remove upper() and you'll be fine. Thank you guys! That was the point. The real question is: what did you try to accomplish with the UPPER() on a numeric column? 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] MySQL versus Postgres
Sandeep Srinivasa wrote on 09.08.2010 08:54: The way I see it - for those who want to truly learn, there is the documentation. For those who dont, there are ORMs. Another of those ORM myths ;) ORMs are not an alternative to learning SQL or understand how a DBMS works. You need to be good at SQL and you need a good understanding of relational databases in order to use an ORM efficiently. One of the first sentences in the Hibernate manual is: "If you have a limited knowledge of JAVA or SQL, it is advised that you start with a good introduction to that technology prior to attempting to learn Hibernate" 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] Using AND in query
Alban Hertroys wrote on 08.08.2010 10:46:
On 7 Aug 2010, at 23:18, Thomas Kellerer wrote:
Or as an alternative:
SELECT tid, purchase_date
FROM orders
WHERE item in ('Laptop', 'Desktop')
GROUP BY tid, purchase_date
HAVING count(*) = 2
This one is incorrect, it will also find people who bought two laptops or two
desktops on the same date.
Right. I didn't think about that ;)
But I think David's solution is more readable, as it leaves the item names in
tact.
I absolutely agree. Another example of PG's cool array handling :)
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] Using AND in query
aravind chandu wrote on 07.08.2010 21:40:
Hello every one,
I have encountered a problem while working .I have a sample table with
the following data
*TID* *Date* *Item*
T1008/1/2010Laptop
T1008/1/2010Desktop
T1018/1/2010Laptop
T1028/1/2010Desktop
T1038/2/2010Laptop
T1038/2/2010Desktop
T1048/2/2010Laptop
need the data when a person bought laptop & desktop on the sameday.I
used a condition in where clause but its not working,it is returning no
rows.Can any one please help me to resolve this issue ?
condition in where clause :
table.date in date() to date() and table.item = "laptop" and table.item
= "Desktop"
You should first understand why your query is not working.
The condition
and table.item = 'laptop' and table.item = 'Desktop'
says: I want all rows where the column item has the value 'Laptop' and *at the
same time* has the value 'Desktop'
Which clearly cannot be the case (a column can only have a single value)
So you need to join all "Laptop" rows to all "Desktop" rows to get what you
want.
SELECT l.tid, l.purchase_date
FROM the_table_with_no_name l
JOIN the_table_with_no_name d
ON l.tid = d.tid AND l.purchase_date = d.purchase_date AND d.item =
'Desktop'
WHERE l.item = 'Laptop'
Or as an alternative:
SELECT tid, purchase_date
FROM orders
WHERE item in ('Laptop', 'Desktop')
GROUP BY tid, purchase_date
HAVING count(*) = 2
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] MySQL versus Postgres
John Gage wrote on 06.08.2010 04:41: But most people, including myself, don't even want to know the documentation exists (for anything). We just want to plunge in and do it. That just doesn't work and is an attitude that won't get you far. In order to do things properly you need to learn and understand what you are dealing with. "Plunging" into something might look easy at the start but will get you into problems later when you need to understand *why* and *how* things are working. This is not something unique to Postgres or databases in general. It's not even unique to software. Learn what youare doing (or dealing with) is a "strategy" that applies to everything you do. Do take the time to read the manuals - including the MySQL manual (because just "plunging" into MySQL simply doesn't work either) It'll make you a lot more proficient in the long run. 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] Comparison of Oracle and PostgreSQL full text search
Howard Rogers, 28.07.2010 03:58: Thanks to some very helpful input here in earlier threads, I was finally able to pull together a working prototype Full Text Search 'engine' on PostgreSQL and compare it directly to the way the production Oracle Text works. The good news is that PostgreSQL is bloody fast! The slightly iffy news is that the boss is now moaning about possible training costs! Why is it that managers always see short term savings but fail to see longterm expenses? For what it's worth, I wrote up the performance comparison here: http://diznix.com/dizwell/archives/153 Maybe it will be of use to anyone else wondering if it's possible to do full text search and save a couple hundred thousand dollars whilst you're at it! Very interesting reading. Would you mind sharing the tables, index structures and search queries that you used (both for Oracle and Postgres)? 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] pg_dump and --inserts / --column-inserts
Tom Lane wrote on 17.07.2010 19:35: Thomas Kellerer writes: Tom Lane wrote on 17.07.2010 16:36: Well, nobody's offered any actual *numbers* here. I measured the runtime as seen from the JDBC client and as reported by explain analyze (the last line reading "Total runtime:") The "runtime" from explain analyze really should not be measurably different, since it doesn't include parse time or data transmission time, and you ought to get the same execution plan with or without the column names. Interesting. My intend _was_ to exclude data transmission from the test by using explain analyze, but I'm surprised that it doesn't include the parsing in the execution time reported from that. I'd dismiss those numbers as being within experimental error, except it seems odd that they all differ in the same direction. And it's reproducable (at least on my computer). As I said I ran it 20 times (each run did it for 5,10,... columns) and the values I posted were averages of those runs. 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
