Re: [GENERAL] Installation Size showing huge size in postgres installed on windows os
On 19/03/2015 12:39, jaime soler wrote: > El mié, 18-03-2015 a las 23:05 -0700, Rajagopal NS escribió: >> I have installed Postgres 9.0 in my machine. When I look at Programs and >> Features under Control Panel, >> I see the Size for Postgres 9.0 is shown as 121GB. >> >> I feel neither the installation or the small postgres databases I would have >> created use 121GB. >> >> Any reason why it shows 121GB > > Could you share with us \l+ command in psql session ? Also, see what Windows reports as the space taken up by the installation and data directories. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Load Data with COPY
On 24/03/2015 21:00, dpop...@uvic.ca wrote: > I have 7,000+ rows of data in Numbers. Exported to .csv file, tried to > load into postgres table with COPY FROM, failed. Postgres does not > recognize the format: > > ERROR: invalid input syntax for integer: "project_name,... > > Is there any way to export/convert the data in Numbers into a format > recognizable by postgres? > Mac OS 10.10.6; postgres 9.3.5.2 Well, the error says that you're trying to load a string, "project_name", into an integer column. Is there a header row in the CSV file? - if so, delete it and try again. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] :Posgres - performance problem
On 25/03/2015 14:30, ginkgo36 wrote: > @Gary > I'm working on big data, because of the demands of the job so I > export/import/update data on this table every day. > > I guess it's possible that each query would need all 417 columns but it > seems unlikely... --> Yes, not at all but 2/3 of 417 columns :) > > I need gather data into one table for consistency and easy for export and > import, it's ok if I split data to smaller tables, but when > export/import/update, i must excute query on alot of table. And this way > lead data to inconsistency if I forget update/export/import on 1 or more > table. It is terrible. You could create a view specifically for export, which would gather together data from all the tables. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] check data for datatype
On 27/03/2015 18:08, Suresh Raja wrote: > Hi All: > > > I have a very large table and the column type is text. I would like to > convert in numeric. How can I find rows that dont have numbers. I > would like to delete those rows. Use a regular expression: select from where ~ http://www.postgresql.org/docs/9.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Best way to migrate a 200 GB database from PG 2.7 to 3.6
On 15/04/2015 20:03, Filip Lyncker wrote: > Dear List, > > I need to migrate my database from a 2.x to 3.x. Usually Im using > pg_basebackup , but this is not possible with different versions. > Pg_dump seems to be an option but it is slow like hell and I dont want > to stay offline all the time. > Is there another possibility to migrate a database with 200 GB ? Those version numbers aren't correct... current PG major versions run from 9.0 to 9.4. I don't think there was ever any version less than 6 (though I could be wrong). Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Moving Specific Data Across Schemas Including FKs
On 23/04/2015 18:09, Cory Tucker wrote: > I have the need to move a specific set of data from one schema to > another. These schemas are on the same database instance and have all > of the same relations defined. The SQL to copy data from one table is > relatively straightforward: > > INSERT INTO schema_b.my_table > SELECT * FROM schema_a.my_table WHERE ... > > What I am trying to figure out is that if I also have other relations > that have foreign keys into the data I am moving, how would I also move > the data from those relations and maintain the FK integrity? I'd create the tables in the new schema without the FK constraints, copy the data, then add the constraints afterwards. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Moving Specific Data Across Schemas Including FKs
On 23/04/2015 19:08, Raymond O'Donnell wrote: > On 23/04/2015 18:09, Cory Tucker wrote: >> I have the need to move a specific set of data from one schema to >> another. These schemas are on the same database instance and have all >> of the same relations defined. The SQL to copy data from one table is >> relatively straightforward: >> >> INSERT INTO schema_b.my_table >> SELECT * FROM schema_a.my_table WHERE ... >> >> What I am trying to figure out is that if I also have other relations >> that have foreign keys into the data I am moving, how would I also move >> the data from those relations and maintain the FK integrity? > > I'd create the tables in the new schema without the FK constraints, copy > the data, then add the constraints afterwards. Meant to add, you'll also need to do select setval(...); on the sequence(s) in the new schema supplying the ID values, to set them to something higher than any extant values copied in from the old schema... but I'm sure you thought of that. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Restarting DB after moving to another drive
On 11/05/2015 12:03, Daniel Begin wrote: > I am working on windows and I had to move my database on another hard > drive after the original one started overheating. In order to move the > DB I did the following… > > > > -Stop postgresql-x64-9.3 service – and wait until there were no more > system access to on the original drive > > -Copy the entire content of the original drive to the new one (the drive > is dedicated to the DB) > > -Turn off the original hard drive and reassign the old drive letter to > the new one > > -Restart the DB > > > > I tried to connect to the database by using PgAdmin III and I got the > following error message: > > “Could not read symbolic link “pg_tblspc/100589”: Invalid argument” > > > > I concluded something went wrong and I decided to get back to the old > drive. I stopped the DB, turned off the new drive, turned on the old one > and tried to restart the postgresql service but it does not start > anymore. The only message I get is: > > “Postgresql-x64-9.3 service on local computer started and then stopped. > Some services stop automatically if they are not in use by other > services or programs” Hi there, Sounds like you're on Windows - you can get more information from PostgreSQL's own logs, which by default on Windows are in a directory called pg_log under the data directory. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Restarting DB after moving to another drive
On 11/05/2015 13:38, Daniel Begin wrote: > I just get it back running with the old drive - was some Windows hidden > behavior! > > However, does someone could tell me what went wrong with the procedure I > used to move the DB? > And/or what procedure I should have used in order to get it right? I've never done it myself, but I understand that one way to do it is with tablespaces: http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html As I understand it, you create a new tablespace on the new disk, then move your database objects to it. How that plays with Windows, I've no idea I'd guess that changing the drive letter might well cause Bad Things to happen. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Between with a list of ranges possible ?
On 29/05/2015 17:32, Arup Rakshit wrote: > Hi, > > Can I do the below 3 queries in a single query ? > > select * from table where number * 3 between start_value1 and end_value2; > select * from table where number * 3 between start_value2 and end_value2; > select * from table where number * 3 between start_value3 and end_value3; If they're all the same table, just use OR: ... where (number * 3 between start_value1 and end_value1) or (number * 3 between) etc. Or am I missing something? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] My postgres is not logging anymore
On 18/06/2015 10:34, Maila Fatticcioni wrote: > Hello. > I have a postgres cluster made by two nodes with OS Ubuntu 14.04. > About two weeks ago I did a minor update of postgres to the version > 9.4.1-1.pgdg70+1. After I had to shutdown the machines and I could > start them just last week. > Since then postgresql has been stopping logging on both nodes. If I > destroy the log file and I do a restart, a new file is created but it > remains empty. I have tried to change some configuration parameters > with no success. I have tried to restart postgres on both nodes and > relocate the service as well - nothing. Apart from this everything is > working fine and my applications don't show any errors during the > connection to the database. Any ideas about how to have back the log? > > Here my configuration file postgresql.conf: What have you got set for the following? - Here's what they're set to on my laptop (Windows 7, PG 9.4): log_destination = 'stderr' logging_collector = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_file_mode = 0600 log_truncate_on_rotation = off log_rotation_age = 1d log_rotation_size = 10MB The comments in postgresql.conf indicate that log_destintion and logging_collector, at least, need to be set as above to generate log files. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] My postgres is not logging anymore
On 18/06/2015 13:13, Maila Fatticcioni wrote: > On 06/18/2015 11:48 AM, Raymond O'Donnell wrote: >> On 18/06/2015 10:34, Maila Fatticcioni wrote: >>> Hello. I have a postgres cluster made by two nodes with OS Ubuntu >>> 14.04. About two weeks ago I did a minor update of postgres to >>> the version 9.4.1-1.pgdg70+1. After I had to shutdown the >>> machines and I could start them just last week. Since then >>> postgresql has been stopping logging on both nodes. If I destroy >>> the log file and I do a restart, a new file is created but it >>> remains empty. I have tried to change some configuration >>> parameters with no success. I have tried to restart postgres on >>> both nodes and relocate the service as well - nothing. Apart from >>> this everything is working fine and my applications don't show >>> any errors during the connection to the database. Any ideas about >>> how to have back the log? >>> >>> Here my configuration file postgresql.conf: > >> What have you got set for the following? - Here's what they're set >> to on my laptop (Windows 7, PG 9.4): > >> log_destination = 'stderr' logging_collector = on log_filename = >> 'postgresql-%Y-%m-%d_%H%M%S.log' log_file_mode = 0600 >> log_truncate_on_rotation = off log_rotation_age = 1d >> log_rotation_size = 10MB > >> The comments in postgresql.conf indicate that log_destintion and >> logging_collector, at least, need to be set as above to generate >> log files. > >> Ray. > > > I set up the logs using the parameters: > log_connections = on > log_disconnections = on > log_duration = on > log_error_verbosity = terse > log_statement = 'all' > log_timezone = 'localtime' > > I think it would be enough to get the log in the file > /var/log/postgresql/postgresql-9.4-main.log . If I set the Well, according to the comments in postgresql.conf - which you really should read if you haven't already - you need logging_collector to be on in order to log to a file at all. Read this too: http://www.postgresql.org/docs/9.4/static/runtime-config-logging.html > logging_collector up the log would became > /var/lib/postgresql/9.4/main/pg_log/postgresql-2015-06-18_X.log . Just change log_filename to whatever you like. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] PL/pgSQL: How to return two columns and multiple rows
On 18/06/2015 13:36, Sven Geggus wrote:
> Hello,
>
> I supose this is simple, but I did not find a solution in the documentation.
>
> I would like to be able to do something like this:
>
> select myfunc('foo','bar');
> or
> select myfunc(foo, bar) from foobartable;
> or even
> select myfunc(foo, bar), 'baz' as baz from foobartable;
>
> Which should return something like this:
> foo | bar
> --+--
> foo1 | bar1
> foo2 | bar2
> foo3 | bar3
> foo4 | bar4
> (4 rows)
>
> So the output should be at least two columns and (usually) more than one row.
>
> What I currently have is the following, which is mostly it. Unfortunately
> it gives me only one column (I really need two) and I would have to create a
> custom type:
>
> CREATE TYPE t_foobar AS (foo text, bar text);
>
> CREATE or REPLACE FUNCTION myfunc(foo text, bar text)
> returns SETOF t_foobar as $$
> BEGIN
> FOR i IN 1..4 LOOP
> RETURN NEXT (foo || i::text, bar || i::text);
>
> END LOOP;
> RETURN;
> END;
> $$ language 'plpgsql';
>
> mydb=> select myfunc('foo','bar');
You need to do:
select * from myfunc('foo','bar');
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] timestamp check
On 10/07/2015 13:54, Ramesh T wrote: > > select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD > HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM > pg_catalog.pg_timezone_names > WHERE name='US/Eastern'),'-MM-DD > HH24'||':'||'MI'||':'||'SS')::timestamptz; > > it's not displaying timezone..any help..? > Because TIMESTAMPTZ - TIMESTAMPTZ = INTERVAL, not TIMESTAMPTZ. Also, why on earth are you doing all those string concatenations in the to_char() calls? Why not just do to_char(..., '-MM-DD H24:MI:SS')? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 table of magic constants
On 11/07/2015 20:07, Dane Foster wrote: > As a recent convert to the Church of Postgres I've been consuming vast Welcome to the One True Faith! :-) > amounts of information on PostgreSQL, and SESSION_USER is not the first > nor only, what I'm calling magic constant, that I've seen. Off the top > of my head, other examples that I've encountered are CURRENT_USER and > CURRENT_TIMESTAMP. > > So my question is this, is there a reference table in the documentation > that I haven't found yet that lists all magic constants and their > meaning? And if not in the official documentation is it in the wiki? session_user, current_timestamp and current_user are all functions, not magic constants: http://www.postgresql.org/docs/9.4/static/functions-datetime.html http://www.postgresql.org/docs/9.4/static/functions-info.html I hope this helps, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] timestamp check
On 11/07/2015 17:11, Ramesh T wrote: > okay,i'm executing a query from pgadmin3. > > i want display time with timezone.But above query displaying date and > time not timezone... > > On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston > mailto:david.g.johns...@gmail.com>> wrote: > > On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T > <mailto:rameshparnandit...@gmail.com>>wrote: > > > select > current_timestamp-to_timestamp(to_char(current_date,'-MM-DD > HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM > pg_catalog.pg_timezone_names > WHERE name='US/Eastern'),'-MM-DD > HH24'||':'||'MI'||':'||'SS')::timestamptz; > > it's not displaying timezone..any help..? Hi there, This question was answered a few days ago, but perhaps you didn't see it. The reason you don't get timezone information is that subtracting two timestamps results in an interval value. See table 9-27 on this page: http://www.postgresql.org/docs/9.4/static/functions-datetime.html Also, please don't top-post; the convention on this list is to bottom-post. Thanks! :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] I lost my password
On 27/07/2015 04:15, p...@arbolone.ca wrote: > OK, I think I should tell you folks that I am a newbie. I am using > postgresql to learn the SQL for the purpose of learning storing the data > in my c++ application. I have near zero knowledge of SQL or PostgreSQL > for that matter. > When I type C:\pgsql, I am asked to entered a password, but I don't > recall the any passwords I might've set up at installation time. Hello there, Adrian's advice about re-installing is probably the easiest way to do it. However, here's another route, just for completeness (I'm assuming that you're installing on your own laptop or similar, not a server): Find the file pg_hba.conf in the data directory. Look for a "host" line like this: host all all 127.0.0.1/32md5 Change "md5" to "trust", save the file and re-start the PostgreSQL service. You should then be able to connect without a password: psql -U postgres NB - If you don't specify a Postgres user with -U, Postgres assumes that you're connecting as the current operating system user. You can then reset the password for the user "postgres" (or whatever user you used to connect): alter role postgres with password 'whatever'; Don't forget to edit pg_hba.conf once again and set "trust" back to "md5' (and re-start the service). Finally, note that the "postgres" user is the super-user, so the usual caveats apply... better to create another ordinary user for normal connections. I hope this helps, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] I lost my password
On 27/07/2015 14:54, p...@arbolone.ca wrote: > It worked! > Thanks kids, that was awesome! Glad it worked. It's a while since I've been called a kid! :-) > One last question, is there a tutorial about accessing the back end > using c++ I've done it using Qt, and there are tons of documentation and examples out there - Google is your friend. Qt's own documentation is very good. For plain C, read PostgreSQL's own docs: http://www.postgresql.org/docs/9.4/static/libpq.html Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] pghba.conf
On 03/08/2015 10:52, Ramesh T wrote: > Hi All, > I changed in pg_hba.conf like,in postgres 9.3 under linux > host replication base_backup_user 127.0.0.1/32 > <http://127.0.0.1/32> trust > > after above changes ,restarted it . > > when i try pgbasebackp getting error like, > > pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry > for replication connection from host "192.168.02.64", user > "base_backup_user", SSL off Well, the error says it all: your replication connection is coming from 192.168.02.64, whereas you specified 127.0.0.1 in pg_hba.conf. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 Developer Best Practices
On 26/08/2015 19:54, Gavin Flower wrote: > On 27/08/15 00:03, Vincent de Phily wrote: >> On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: >>> Actually I would suggest standardising on singular names, not JUST >>> because that this the standard I prefer! :-) >>> >>> But (also) because: >>> >>> 1. Singular words tend to be shorter >>> >>> 2. plurals are more ambiguous wrt spelling >>> >>> 3. there other good reasons, that I've forgotten for now :-( >>> (but I remember having them!!!) >> Oh, so you name your tables 'fish' and 'crow' instead of 'school' and >> 'murder' ? Such wasted opportunities :p >> > ??? A school of fish and a murder of crows... wonderfully evocative collective nouns. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 dependencies
On 06/09/2015 22:59, FarjadFarid(ChkNet) wrote: > No worries. > > I found a way. > Would you share it, for the archives? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Selecting pairs of numbers
Hello all, I have an SQL problem which ought to be simple, but I can't get my head around it. I have pairs of integers - let's call them (x, y). In effect, x is a category, while y is an item within that category. For every x, there is always the same number of integers y; and both x and y are always numbered sequentially starting from 1. My problem is that I need to select a list of these pairs, ordered first on x and then on y, from a given starting point to a given finishing point and including all pairs in between. For example, I might have: x | y - 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 (etc) I then might want to extract a list from, say, (1, 3) to (3, 2), giving: x | y - 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 For the life of me, I can't figure out how to do this. Any help will be appreciated, or even just a pointer in the right direction. There's probably something simple that I'm just not seeing If anyone's interested, these numbers represent channels and pistons on the combination system of a largish pipe organ... it's for a hobby project. Many thanks in advance! Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Selecting pairs of numbers
On 05/10/2015 19:53, Adrian Klaver wrote: > On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: >> Hello all, >> >> I have an SQL problem which ought to be simple, but I can't get my head >> around it. >> >> I have pairs of integers - let's call them (x, y). In effect, x is a >> category, while y is an item within that category. For every x, there is >> always the same number of integers y; and both x and y are always >> numbered sequentially starting from 1. >> >> My problem is that I need to select a list of these pairs, ordered first >> on x and then on y, from a given starting point to a given finishing >> point and including all pairs in between. >> >> For example, I might have: >> >> x | y >> - >> 1 | 1 >> 1 | 2 >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> (etc) >> >> I then might want to extract a list from, say, (1, 3) to (3, 2), giving: >> >> x | y >> - >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 >> >> For the life of me, I can't figure out how to do this. Any help will be >> appreciated, or even just a pointer in the right direction. There's >> probably something simple that I'm just not seeing > > aklaver@test=> create table pr_test(x int, y int); > > aklaver@test=> select * from pr_test where (x, y) between (1, 3) and > (3,2) order by x,y; > x | y > ---+--- > 1 | 3 > 1 | 4 > 2 | 1 > 2 | 2 > 2 | 3 > 2 | 4 > 3 | 1 > 3 | 2 As simple as that? Thank you! I knew there had to be an easy way. Thanks also to the others who replied with a slightly different approach, which involved multiplying x by 10. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Selecting pairs of numbers
On 05/10/2015 20:03, John McKown wrote: > When in doubt, cheat! Why not something like: > > SELECT x, y FROM organ_pipes WHERE point(x,y) <@ > box(point(?x1,?y1),point(?x2,?y2)) ; > > This is definitely a different approach from the others that I've seen. > > > Basically, think of your channel / piston as a point in a Cartesian > plane. And your boundaries as a box in that graph. So you see if the > "point" is inside the "box" Ohhh - that's nice! Reminds of one of my lecturers from college days, who said there was often a geometrical solution to an algebraic problem. Thanks! Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Selecting pairs of numbers
On 05/10/2015 20:08, Adrian Klaver wrote: > On 10/05/2015 12:00 PM, Raymond O'Donnell wrote: >> On 05/10/2015 19:53, Adrian Klaver wrote: >>> On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: >>>> Hello all, >>>> >>>> I have an SQL problem which ought to be simple, but I can't get my head >>>> around it. >>>> >>>> I have pairs of integers - let's call them (x, y). In effect, x is a >>>> category, while y is an item within that category. For every x, >>>> there is >>>> always the same number of integers y; and both x and y are always >>>> numbered sequentially starting from 1. >>>> >>>> My problem is that I need to select a list of these pairs, ordered >>>> first >>>> on x and then on y, from a given starting point to a given finishing >>>> point and including all pairs in between. >>>> >>>> For example, I might have: >>>> >>>> x | y >>>> - >>>> 1 | 1 >>>> 1 | 2 >>>> 1 | 3 >>>> 1 | 4 >>>> 2 | 1 >>>> 2 | 2 >>>> 2 | 3 >>>> 2 | 4 >>>> (etc) >>>> >>>> I then might want to extract a list from, say, (1, 3) to (3, 2), >>>> giving: >>>> >>>> x | y >>>> - >>>> 1 | 3 >>>> 1 | 4 >>>> 2 | 1 >>>> 2 | 2 >>>> 2 | 3 >>>> 2 | 4 >>>> 3 | 1 >>>> 3 | 2 >>>> >>>> For the life of me, I can't figure out how to do this. Any help will be >>>> appreciated, or even just a pointer in the right direction. There's >>>> probably something simple that I'm just not seeing >>> >>> aklaver@test=> create table pr_test(x int, y int); >>> >>> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >>> (3,2) order by x,y; >>> x | y >>> ---+--- >>> 1 | 3 >>> 1 | 4 >>> 2 | 1 >>> 2 | 2 >>> 2 | 3 >>> 2 | 4 >>> 3 | 1 >>> 3 | 2 >> >> As simple as that? Thank you! I knew there had to be an easy way. > > Yea, surprised me to. Just to complete my understanding of what's going on here, how does Postgres see the construct (x, y)? Is it some sort of anonymous or temporary composite type? Thanks, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Hierarchical Query Question (PHP)
On 30/10/2015 22:10, David Blomstrom wrote: > Just so I understand what's going on, I can create a lookup table by > pasting this code... > > create table taxon ( > taxonid serial, > descr text > ); > create table gz_life_mammals ( > id serial, > taxonid integer, -- use the lookup table > parentid integer -- use the lookup table > ); > > ...into pgAdmin III, right? (I can't use the shell/terminal at the > moment, and it will be at least a few weeks before I can get it fixed.) > And this script will create TWO tables - gz_life_mammals and a matching > "lookup table"? Yes, it will. I haven't seen, what went before in this thread so may have missed stuff, but you should also add a foreign key constraint between the tables (for taxonid anyway, dunno what parentid refers to): create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid integer, -- use the lookup table constraint mammals_taxon_fk foreign key (taxonid) references taxon(taxonid) ); If parentid also references taxon.taxonid, add a similar constraint for it too, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Hierarchical Query Question (PHP)
On 30/10/2015 22:29, David Blomstrom wrote: > Thanks for the tips. > > In pgAdmin III, I can create a table step-by-step by choosing Edit > > Object > New Table > > But is there a pace for me to past in a block of code that creates the > table with just one process? If I click on the SQL icon, a SQL window > opens up, and I can past the code into SQL Editor, but I don't know how > to "activate" it; pressing ENTER does nothing. There's an "Execute query" button in the toolbar, and pressing F5 does the same. If the toolbar isn't visible, you can make it visible under the "View" menu. As a bonus, you can select a block of SQL and only the selected code will get executed. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Selectively Importing Data
On 31/10/2015 00:24, David Blomstrom wrote: > First consider the following table: > > create table taxon ( > taxonid serial, > descr text > ); > > As I understand it, "serial" means that column will automatically > populate with a numerical key. > > If I want to fill the field 'descr' with a list of scientific names > stored in a spreadsheet, then how would I proceed? > > I have a spreadsheet that has about a dozen columns. I copied the field > with scientific names into a new spreadsheet. Then I created an empty > field on the left. So I have a spreadsheet with two columns. I saved it > as a CSV file. > > When I try to import it with pgAdmin III, I get the error message > > WARNING: null value in column "taxonid" violates non-null constraint > > How can I import that single column into the second column in this > table? Thanks. Serial columns will only get populated if there's no value (or no NULL either) inserted into that column. "Serial" [1] is not a real data type; it's just a handy shorthand for creating a sequence, setting a DEFAULT of nextval([sequence name]) on the column, and making that column NOT NULL. At a guess, I think that - by creating the blank column in the spreadsheet and then importing from it - you were actually inserting NULL into the taxonid column, hence violating the NOT NULL constraint. You need to find some way of excluding the taxonid column from the import, so that the DEFAULT mechanism will populate it for you. I don't know how you do that with pgAdmin; I know it can be done with COPY from the command-line. You could try posting to the pgAdmin mailing list [2] about it. I hope that helps, Ray. [1] http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL [2] http://www.pgadmin.org/support/list.php -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] MediaWiki + PostgreSQL is not ready for production?
On 20/07/16 16:57, John DeSoi wrote: On Jul 18, 2016, at 11:47 PM, John R Pierce wrote: Drupal even tried to offer a database API so plugin developers wouldn't touch SQL directly, but too many ignored it. I have been using Drupal with PostgreSQL for more than 10 years without too many problems. Since version 7 all of Drupal core works with PostgreSQL and I have encountered very few non-core modules that are MySQL specific. SQLite is also a core-supported database for Drupal. That's been my experience too. I remember some years ago a MySQL-ism in a query in the Nodequeue module was causing crashes on my Drupal/PostgreSQL installations; it was logged as a bug by the developers and fixed fairly quickly. Ray. -- 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] please remove outdated site from 2005
On 04/09/16 19:33, Lukas Lehner wrote: Hi when I use a search engine for the term "Postgres Certification" this page shows up https://www.postgresql.org/about/news/333/ please remove that, very much outdated. The referenced link doesn't exist anymore (404 No such domain) In all fairness, the page on which you find that item is an archive of all past news items going back to October 2002. I'm sure there are a lot of out-of-date items on it, but there's no real reason to remove them; an archive is just that. Ray. -- 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] Installing 9.6 RC on Ubuntu
On 13/09/16 19:03, Steve Crawford wrote: I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it seems broken. Installation of 9.6 RC1 on Centos was straightforward by comparison - just add the 9.6 yum package and install. Unfortunately Ubuntu seems second-class by comparison. I already have /etc/apt.repos.d/pgdg.list with: deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main This works and I previously installed the PGDG 9.5 package without issue. Apt lists numerous 9.6 support packages (unit, repmgr, repack, plv8, ...) but with unsatisfied dependencies due to the lasck of postgresql-9.6. I tried adding -testing to the repo but no joy: deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg-testing main Is packaging just not complete or am I missing something? (I had hoped that getting configured for testing would be more friction-free.) Define "No joy" - what happened? What errors or other messages did you get? Ray. -- 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] isnull() function in pgAdmin3
On 03/10/16 14:39, dudedoe01 wrote: What is the most feasible way to emulate the below MySQL function into postgreSQL. Since the isnull() function is no longer supported in 9.6 version. I have tried every trick in the hat to get the desired results. Still 'RPG INV' doesn't show only the other two then options show up. (case when ((`s`.`Funding_Date` = '') and (isnull(`s`.`Actual_Close_Date`) or (`s`.`Actual_Close_Date` = ''))) Maybe I missed something earlier in the thread (only tuned in now), but what's wrong with ... and s.actual_close_date is null ? Ray. -- 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] Generic way to test input arguments
On 17/10/16 16:40, said assemlal wrote: Hello, I am looking for a way to test generically input arguments to raise an exception if one is either null or empty. I was thinking to create a function who takes an array to check them but not sure if it's really good. It's not clear what you want to do here. Can you explain in more detail? What do you mean by "input arguments"? Are you writing functions in the database? If so, which language? Some examples of what you've tried so far would help too. Ray. -- 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-Shop with PostgreSQL
On 08/11/16 18:24, Joshua D. Drake wrote: Hello, Drupal + Ubercart should service your needs. +1 to what the others said about Drupal + Ubercart: easy to set up, but very customisable too if you need to. There are a number of freely-available themes which are aimed at online shops and work very well. Ray. -- 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] Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16)
On 10/11/16 10:34, Juliano wrote: Hi, I'm trying to import some data from a MS SQL Server 2014 sequential database to Postgresql using dbi-link. Have you tried the foreign data wrapper for MS SQL Server? It's here: https://wiki.postgresql.org/wiki/Foreign_data_wrappers#Specific_SQL_Database_Wrappers Not something I've done, but it may be worth a try. Ray. -- 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] About the MONEY type
On 30/11/16 12:05, Thomas Kellerer wrote: Tobia Conforto schrieb am 30.11.2016 um 12:15: I think MONEY is a great datatype, at least in theory. I personally find it pretty useless to be honest - especially because the currency symbol depends on the client. So if I store a money value in the database, some clients see CHF, some see Kč, some see £ and others might see € - all see the same amount. Which seems totally wrong because 10€ is something completely different then 10Kč or 10£. Plus: inside a programming language (e.g. Java/JDBC) it's hard to work with the values because the database sends the values as a string (it has to because of the currency symbol) but in reality it is a number - but you can't just convert the String to a number again because of the symbol. So I always recommend to not use it (in Postgres just as well as in other DBMS, e.g. SQL Server) I seem to remember that it was actually deprecated at some point - this is going back quite a few years. This was later reversed, though I don't know why. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] No select privileges when not connecting from login postgres
On 03/12/16 22:55, Joseph Brenner wrote: The version in the Debian stable repos right now is 9.4, and I saw an issue with it I wanted to check against the latest version, so I did a build of it from a tarball. You can get the latest version from the PostgreSQL apt repo: http://wiki.postgresql.org/wiki/Apt Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 loop through multi-dimentional array in PL/PGSQL
On 09/12/16 06:35, VENKTESH GUTTEDAR wrote: Hello, Please help me in accessing multi-dimentional array in postgresql PL/PGSQL. for i in array_lower(product_list, 1) .. array_upper(product_list, 1) LOOP product_list[i][0]; END LOOP; Is the above code right? Or is there any other way to access, i am getting null for product_list[i][0]; By default, PostgreSQL uses 1-based arrays (i.e. no [0] slot), so maybe that's your problem. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 loop through multi-dimentional array in PL/PGSQL
On 09/12/16 10:42, Raymond O'Donnell wrote:
On 09/12/16 06:35, VENKTESH GUTTEDAR wrote:
Hello,
Please help me in accessing multi-dimentional array in postgresql
PL/PGSQL.
for i in array_lower(product_list, 1) .. array_upper(product_list, 1)
LOOP
product_list[i][0];
END LOOP;
Is the above code right?
Or is there any other way to access, i am getting null for
product_list[i][0];
By default, PostgreSQL uses 1-based arrays (i.e. no [0] slot), so maybe
that's your problem.
I just tried it myself: doing this -
select ('{a, b, c}'::text[])[0]
- gives me NULL.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] Multidimentional array access
On 09/12/16 15:30, Tom Lane wrote: As of 9.6 you could use the shorthand array_value = Eg[i][:] regards, tom lane I hadn't spotted that - very handy - will go and read up on it. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] [OT] Postgresql and PHP
On 23/12/16 15:44, Alessandro Baggi wrote:
Hi list,
sorry for this OT.
I have a table on postgresql like this:
id serial not null,
srcaddr varchar(16) not null
I use this table to store ip address. I've used also inet type but
changed to see if this solves my problem.
From psql, I run:
select count(*) from bs_ipsource where srcaddr = '192.168.1.1';
and query is performed.
From php I'm trying to do the same with this code:
$ipsrc = "192.168.1.1";
$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}
Are you sure it's failing? Try this:
if ($query === false) {
}
PHP has funny ideas about what's true and false.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] Fwd: I could not see any row in audit table
On 25/01/17 11:03, Shailesh Singh wrote:
Dear Group Member ,
I had configured the audit trigger for my datbase following the below
document url:
*https://wiki.postgresql.org/wiki/Audit_trigger_91plus
<https://wiki.postgresql.org/wiki/Audit_trigger_91plus>
*
Now my audit table :
CREATE TABLE audit.logged_actions (
event_id bigserial PRIMARY KEY,
schema_name text NOT NULL,
TABLE_NAME text NOT NULL,
relid oid NOT NULL,
session_user_name text,
action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL,
action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL,
transaction_id BIGINT,
application_name text,
client_addr inet,
client_port INTEGER,
client_query text NOT NULL,
action CHAR(1) NOT NULL CHECK (action IN ('I','D','U', 'T')),
row_data hstore,
changed_fields hstore,
statement_only BOOLEAN NOT NULL
);
Now this table contains 50 GB of data , But when taking its backup using
pg_dump and after restoring , it show that it has zero row.
How did you restore it?
What is showing that there are zero rows? Did you do "select count(*)
from ...", or something else?
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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 table not found?
On 31/01/17 13:45, Egon Frerich wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I created a db 'Hausrat' with a table "Raum". If I look in pgadmin I
find the the table. In sql field is shown:
-- Table: "Raum"
-- DROP TABLE "Raum";
CREATE TABLE "Raum"
(
"RaumID" serial NOT NULL, -- Automatisch vergebenes
Identifizierungsmerkmal für den Raum
"Raum" character varying(15), -- Bezeichnung des Raums
CONSTRAINT "Raum_pkey" PRIMARY KEY ("RaumID")
)
WITH (
OIDS=FALSE
);
ALTER TABLE "Raum"
OWNER TO egon;
COMMENT ON COLUMN "Raum"."RaumID" IS 'Automatisch vergebenes
Identifizierungsmerkmal für den Raum';
COMMENT ON COLUMN "Raum"."Raum" IS 'Bezeichnung des Raums';
-- Index: "Raum_RaumID_idx"
-- DROP INDEX "Raum_RaumID_idx";
CREATE INDEX "Raum_RaumID_idx"
ON "Raum"
USING btree
("RaumID");
But psql tells me "Raum" is not existent:
egon@xfEinzel ~ $ psql Hausrat
psql (9.3.15)
Type "help" for help.
Hausrat=# SELECT * FROM Raum;
ERROR: relation "raum" does not exist
LINE 1: SELECT * FROM Raum;
Object names are folded to lower-case automatically, unless you enclose
them in double-quotes:
select * from "Raum";
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] (solved) R: Very strange problem on index
On 02/02/17 12:53, Job wrote: I create a table copy, with indexes, and it worked perfecly. Then i dropped the original table and recreated it back by a copy of the newest. Same problems, lack during searches and indexes not used. I didn't see your original message so may have missed this, but did you run ANALYZE on the table after creating it? - Depending on how you created the table, the statistics needed by the planner may or may not have been up to date. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Querying same lookup table with multiple columns based on another view
On 17/11/2015 15:10, Killian Driscoll wrote: > I have a view with 15 columns and want to create another view based on a > join with another table with 15 columns that includes three columns that > reference one lookup table. > > If I use the the below sql I get the error "column "macro_lookup_id" > specified more than once". I have read that I can rename the columns (I > renamed the tables as ml1, ml2, ml3) but can't figure out how to do this > but also use the select * to avoid writing out all rest of the column names. > > CREATE OR REPLACE VIEW sample_macro AS > SELECT * > FROM sample > LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id > LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id > LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id = macroscopic.lustre_id > LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id = > macroscopic.translucency_id > WHERE samp_id is not null; > > What is the most efficient way (in terms of typing out column names) to > create this type of view? Dunno about efficient, but you're going to need to type out your column names - I'm only guessing without seeing the DDL, but it sounds as if a column called macro_lookup_id exists in more than one table. You'll need to do something like this: select sample.macro_lookup_id as col_name_1, macroscopic.macro_lookup_id as col_name_2, [] This is how you specify column aliases. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Querying same lookup table with multiple columns based on another view
On 17/11/2015 15:33, Killian Driscoll wrote: > > In terms of database structure and typing efficiency, it might be better > to restructure the lookup tables and create a new lookup table for each > of the three columns instead of one combined one? [Please keep you replies on-list - thanks!] You'd better show us your table structures - we're only guessing otherwise. However, I think typing efficiency isn't important, but rather what your application needs to do with the data - you only type the query once. :-) -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Querying same lookup table with multiple columns based on another view
On 17/11/2015 16:07, Killian Driscoll wrote: > > > > > In terms of database structure and typing efficiency, it might be better > > to restructure the lookup tables and create a new lookup table for each > > of the three columns instead of one combined one? > > [Please keep you replies on-list - thanks!] > > > Sorry! Didn't mean that > > > You'd better show us your table structures - we're only guessing > otherwise. However, I think typing efficiency isn't important, but > rather what your application needs to do with the data - you only type > the query once. :-) > > > In terms of structure I'm going to go with separate lookup tables as > they are and should be distinct really. > > I'm using pstgresql 9.3. I have set up the new lookup tables and now > want to alter the fk constraints but keep the data already there (the > new lookup tables with maintain the old pks). Below is one of the > constraints: > > CONSTRAINT macro_lookupg_macroscopic FOREIGN KEY (grain_id) > REFERENCES irll.macro_lookup (macro_lookup_id) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT. > > In 9.3 is there an alter constraint option or do I have to drop the > constraint and add the new one? I just had a quick look at the docs for 9.3: http://www.postgresql.org/docs/9.3/static/sql-altertable.html I don't see ALTER CONSTRAINT in there, so it looks as if you need to drop and re-create it. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Text parameter is treated as sql query in postgresql function
On 12/01/2016 17:07, Yash Gajbhiye wrote:
> Hello Adrian,
>
> Thank you for your response. Sorry about the typos in the previous post.
>
> I will try to explain myself more clearly.
>
> This is my first function to create a dynamic query and it is as follows:
>
> CREATE OR REPLACE FUNCTION dynamic_crosstab(
> source_sql text,
> category_sql text,
> v_matrix_col_type text,
> v_matrix_rows_name_and_type text,
> debug boolean DEFAULT false)
> RETURNS text AS
> $BODY$
> DECLARE
> v_sql text;
> curs1 refcursor;
> v_val text;
> BEGIN
> v_sql = v_matrix_rows_name_and_type;
> OPEN curs1 FOR execute category_sql;
> Loop
> FETCH curs1 INTO v_val;
> exit when v_val IS NULL;
> v_sql = v_sql ||' , "'||v_val||'" '||v_matrix_col_type;
> IF debug THEN
> RAISE NOTICE 'v_val = %',v_val;
> END IF;
> END LOOP;
> CLOSE curs1;
> v_sql := 'SELECT * from crosstab(' || chr(10) || E' \''||source_sql ||
> E'\','||chr(10) || E' \''||category_sql || E'\'' || chr(10)|| ' ) AS ('
> || v_sql ||')';
> IF debug THEN
> RAISE NOTICE 'v_sql = %',v_sql;
> END IF;
> RETURN v_sql;
> END;
>
> This works fine. It accepts 2 sql queries and other parameters as inputs
> and output is a sql query which looks like this:
>
> SELECT * from crosstab( sql query 1, sql query 2) AS ();
>
> and this query works fine too.
>
> I want to execute and return rows from this query. Hence I am using
> another function to accomplish, which is :
>
> CREATE OR REPLACE FUNCTION leavetypeaccrual(
> cur refcursor,
> text,
> text,
> text)
> RETURNS SETOF refcursor AS
> $BODY$
> declare
> val_1 text;
> begin
> select * from dynamic_crosstab( 'select
> p.location_id, p.employee_id, pt.description, sum(p.hours_allocated) as
> hours_allocated
> from
> preference_type pt, preference p, preference_date_etl pde, date_etl de
> where
> pt.id <http://pt.id> = p.preference_type_id and
> pde.preference_id = p.id <http://p.id> and
> pde.corporation_id = $4 and
> de.id <http://de.id> = pde.date_etl_id and
> pde.deleted = ''''N'''' and
> p.deleted = ''''N'''' and
> pt.deleted = ''''N'''' and
> de.local_date between ''''$2'''' and ''''$3'''' and
I missed whatever passed upthread, but at a guess I'd say all the
quoting is causing problems here. Why not use the quote_ident() and
quote_literal() functions? By the same token, I don't think you need to
put quotation marks around the parameters.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] Possible to dump/load a database from within psql?
On 25/01/2016 10:16, Brian Cardarella wrote: > Is it possible, and if so how, to dump and then load a database to/from > a file from within a psql connection? You can use the COPY command to do a table at a time, but you'll get just the data - you won't get permissions etc. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 from two tables & concat the result
On 03/02/2016 12:18, arnaud gaboury wrote: > $ psql -V > psql (PostgreSQL) 9.4.5 > > I am quite a newbie in psql. I am setting an email server and need to > create then query psql tables to get some information: mainly email > address and mail directory. > > For now I have created two tables this way. Both tables are in same > database and schema. I only insert one row in each. > > 1- the first one is just a list of all my domains. > > - > CREATE TABLE email.domain ( > id SERIAL, > domain TEXT NOT NULL, > PRIMARY KEY (id) > ); > - > > 2- second is a list of users > > -- > CREATE TABLE email.mailusers ( > id SERIAL PRIMARY KEY, > domain_id INTEGER DEFAULT 1, <<-- This ref to email.domain id=1 > password TEXT NOT NULL, > username TEXT UNIQUE NOT NULL, > created TIMESTAMP WITH TIME ZONE DEFAULT now(); > > > 3- Then I added a constraint: > > - > ALTER TABLE email.mailusers > ADD CONSTRAINT mailuser_domain_id_fkey > FOREIGN KEY (domain_id) > REFERENCES email.domain(id) > ON UPDATE CASCADE > ON DELETE RESTRICT; > - > > > Then I need to retrieve from psql to postfix this parameter value: > - email adress . > The email address is obviously something like a a concat > (username,'@',domain). You can do a simple join between the tables (the string concatenation operator is ||): select u.username || '@' || d.domain as email_address from mailusers u inner join domain d on (u.domain_id = d.domain_id) where . Note that "domain" is a reserved work, so you'll probably have either to double-quote it or else rename that column to something else. Also, you really don't need so many id-type columns... given that the domain and username are presumably unique in their respective tables, having additional serial and domain_id columns seems like overkill. Why not ditch them and use the domain name and username as the primary keys? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 from two tables & concat the result
On 03/02/2016 13:11, arnaud gaboury wrote: > Hum hum... > -- > SELECT u.username ||'@'||d.domain as email_address > FROM email.mailusers u > INNER JOIN > email.domain d > ON > (u.domain_id=d.domain.id) > WHERE id=1; > > ERROR: missing FROM-clause entry for table "domain" > LINE 6: (u.domain_id=d.domain.id) > -- > > What did I wrong following your solution? In the join condition, replace "d.domain.id" with "d.id" (partly my mistake, I missed that the column is called "id" and not "domain_id" in the domains table). Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 from two tables & concat the result
On 03/02/2016 13:57, arnaud gaboury wrote: > On Wed, Feb 3, 2016 at 2:19 PM, Raymond O'Donnell wrote: >> On 03/02/2016 13:11, arnaud gaboury wrote: >> >>> Hum hum... >>> -- >>> SELECT u.username ||'@'||d.domain as email_address >>> FROM email.mailusers u >>> INNER JOIN >>> email.domain d >>> ON >>> (u.domain_id=d.domain.id) >>> WHERE id=1; >>> >>> ERROR: missing FROM-clause entry for table "domain" >>> LINE 6: (u.domain_id=d.domain.id) >>> -- >>> >>> What did I wrong following your solution? >> >> In the join condition, replace "d.domain.id" with "d.id" (partly my >> mistake, I missed that the column is called "id" and not "domain_id" in >> the domains table). >> >> Ray. > > I noticed your mistake but made a wrong change myself :-( > Now working perfectly: > > thetradinghall=> SELECT u.username ||'@'||d.domain as email_address > FROM email.mailusers u > INNER JOIN > email.domainlist d > ON > (u.domain_id=d.id); > >email_address > ----------- > arnaud.gabo...@thetradinghall.com > (1 row) > - > > As for the cleaning of ID, I dropped id and changed both primary keys. > Thank you so much for your prompt answer and help. You're very welcome - glad to be able to help. R. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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 from two tables & concat the result
On 03/02/2016 14:05, arnaud gaboury wrote: >>> >>> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address >>> FROM email.mailusers u >>> INNER JOIN >>> email.domainlist d >>> ON >>> (u.domain_id=d.id); >>> >>>email_address >>> --- >>> arnaud.gabo...@thetradinghall.com >>> (1 row) >>> - >>> >>> As for the cleaning of ID, I dropped id and changed both primary keys. >>> Thank you so much for your prompt answer and help. > > In fact I kept the id for table domainlist (changed the name > accordingly your advise). If I remove the id column, I will not be > able anymore to do the above SELECT , no? > The condition (u.domain_id=d.id) will no more be possible. > > Am I wrong? You're right - you'll need to use the domain name as the foreign key instead. So your tables will look like this: CREATE TABLE domains ( domain_name text not null primary key, ); CREATE TABLE mailusers ( username text not null, password text not null, domain_name text not null, created timestamp with time zone not null default now(), constraint users_pk primary key (username, domain_name), constraint users_domains_fk foreign key (domain_name) references domains(domain_name) ); And then your query would look something like this: select u.username ||'@'||d.domain as email_address from mailusers u inner join domains d on (u.domain_name = d.domain_name) ... HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Please let me know the latest PostgreSQL version available on Solaris 11?
On 08/04/2016 11:50, M Tarkeshwar Rao wrote: > Hi all, > > > > Please let me know the latest PostgreSQL version available on Solaris 11? > > > > Which PostgreSQL version will be supported on Solaris 11.x version and > when the same will be available ? http://www.postgresql.org/download/solaris/ -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Bypassing NULL elements in row_to_json function
On 08/04/2016 16:31, Michael Nolan wrote: > I'm looking at the possibility of using JSON as a data exchange format > with some apps running on both PCs and Macs. . > > The table I would be exporting has a lot of NULL values in it. Is > there any way to skip the NULL values in the row_to_json function and > include only the fields that are non-null? You could use a CTE to filter out the nulls (not tested - I haven't used JSON in PG (yet!)): with no_nulls as ( select ... from my_table where whatever is not null ) select row_to_json() from no_nulls; Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Allow disabling folding of unquoted identifiers to lowercase
On 08/05/2016 10:41, Klaus P. Pieper wrote: > For me is the way Sybase works is just more convenient: > > CREATE MyTable (MyColumn varchar); > > creates a camel cased table MyType and field MyColumn. > > SELECT * FROM SYSCATALOG gives MyTable. > > This is better readable when you use long table / fiel names. I reckon this is just a matter of style... I use underscores to make long names readable... create table my_table_with_a_really_long_name; and it works fine. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Release Notes Link is broken on the website
On 12/05/2016 15:01, Daniel Westermann wrote:
>>>
>>>Provide a link to the source document where you found the link you
> have posted
>
> its the homepage
>
> http://www.postgresql.org
>
Looks like the link URL is missing a "6":
http://www.postgresql.org/docs/9./static/release-9-6.html
^^^
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] Thoughts on "Love Your Database"
On 17/05/2016 18:38, Guyren Howe wrote: > On May 17, 2016, at 2:22 , Achilleas Mantzios > wrote: >> >> Sorry if I missed something but what's wrong with pgadmin3 ? > > Apart from it's awful, clunky, bug-ridden and crash prone, nothing. In fairness to pgAdmin 3: - It's not a development platform, such as MS Access tries to be; it's an admin tool, pure and simple. - pgAdmin 4 is in heavy development, and not too far from a beta; pgAdmin 3 has only been receiving bug fixes for quite some time now. - A lot of the problems in pgAdmin 3 are due to upstream bugs in wxWidgets, over which the pgAdmin team has no control (hence pgAdmin 4). Having said all that, I've rarely had any trouble with pgAdmin 3 on Windows 7 and XP, Ubuntu and Debian; just a very occasional crash (maybe one every six months). Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive_command during database shutdown
On 25/05/16 20:57, Jeff Janes wrote: On Wed, May 25, 2016 at 10:31 AM, Sameer Kumar wrote: On Thu, 26 May 2016, 1:25 a.m. Jeff Janes, wrote: I've recently wanted to run a different archive_command during database shutdown than during normal operations. In particular, if the normal archive process fails during normal operations, I want it to be retried later (as it currently does). But if it fails during shutdown, I want it to run a fallback archive_command. What version of PostgreSQL are you using? 9.2, 9.4, 9.5, 9.6beta. The only way I can see to accomplish this is to have the archive_command try to connect back to the database and see if it gets an error. That seems pretty ugly. Is there a better way? What's your goal here? I want my database to shut down cleanly when I tell it to. During a shutdown, if you don't so much care about checkpoint and fsync of buffers to disk, you can do an immediate shutdown. But I do care about the checkpoint. Otherwise you lose all your unlogged tables. And probably other unfortunate things happen, as Isn't that the point of unlogged tables? Or rather, isn't that the risk you knowingly take with them - you trade reliability for speed? Ray -- 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] SQL Manager 2007 for PostgreSQL released
On 23/05/2007 11:40, L. Berger wrote: Thanks for this, but is there any plan to launch something like this for use on Linux admin servers? Something that I could install on a server, and perhaps work with a web interface? I would love some recommendations. Have you tried PHPPgAdmin? I use it all the time and am very happy with it. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] 8.3
I realise that (i) this is something of a FAQ, and (ii) the definitive answer is "When it's ready", but when (roughly) is 8.3 planned to be released? I'm planning a server upgrade in the next 4-5 months, and a ballpark guess would be helpful. Thanks, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.3
On 30/05/2007 00:11, Guy Rouillier wrote: I'm not one of the developers, but I found the following information here http://www.postgresql.org/developer/roadmap: That's great - thanks. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PHP sucks!! - was: persistent db connections in PHP
On 16/06/2007 16:46, PFC wrote: Also note that PHP, being PHP, sucks, and thusly, will not reconnect persistent connections when they fail. You have to kick it a bit. I've seen similar negative comments before on this list about PHP, and I'm curious to know what informs them. I use PHP quite a bit, and though I wouldn't claim to be any sort of an expert, I like it a lot: IMHO it's powerful, fast and easy to use. Mind you, I came to PHP from an ASP/VBscript background, so anything would have been an improvement. Having said that, the main gripes I would have with PHP are (i) variables aren't strongly typed, which can bite you unless you're careful, and (ii) you don't have to declare variables before using them, which can also cause trouble - in VBScript you have "option expicit" which forces you to declare your variables; I'd like to see something similar in PHP. Apologies if this is off-topic for this list, but I'm curious as to why others reckon that PHP sucks. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] persistent db connections in PHP
[EMAIL PROTECTED] wrote: However, with this new Postgres site, I don't have access to my temp tables after I've traversed another pg_connect. So PHP is either creating a new connection, or giving me another session, not the one which I created my tables in. You wouldn't expect to be given back the same connection (and hence the same temp tables) from a pool of connections - they're returned randomly. Scott Marlowe wrote: MySQL reuses old connections within the same script. PostgreSQL's php extension does not, it starts a new connection each time. Isn't pg_pconnect supposed to recycle a pooled connection? Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Proposed Feature
On 23/06/2007 10:30, Magnus Hagander wrote: Does Delphi bring in any additional runtime requirements, though? I don't think we'd want to add a big extra runtime for such a small thing. Delhpi executables are completely self-contained - they don't need any extra runtime libraries or DLLs (unless you create them yourself, of course). That does mean that a Win32 GUI app will be around 300k or so minimum. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] minimum function
On 23/06/2007 17:17, Gunther Mayer wrote: Any way I can achieve that on one line? I.e. I want it simpler than IF arg1 < arg2 THEN RETURN arg1; ELSE RETURN arg2; END IF; That looks pretty simple already, but why not enclose it in a pl/pgsql function - something like: create function minimum(a1 integer, a2 integer) returns integer as $$ begin if a1 < a2 then return a1; else return a2; end if; end; $$ language plpgsql; - and then you can call it in one line: select minimum(5, 4); Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Image Archiving with postgres
On 28/06/2007 00:58, Eddy D. Sanchez wrote: I want to scan a large quantity of books and documents and store these like images, I want use postgres, anyone have experience with this kind of systems, can you suggest me an opensource solution ?? There have been several lively discussions on this list in the last eighteen months or so about storing binary files in a database vs storing them in the filesystem - you may find it useful to have a look through the archives. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] i need a rad/ide open source for work with postgresql
On 28/06/2007 18:47, Mario Jose Canto Barea wrote: why are you can make a good database relational server as postgresql 8.1, and dont make a rad/ide open source for programming with postgresql 8.1 as delphi\c++builder\progress 4gl ? Because they do different jobs. The languages you mention are for making front-end GUIs (for the most part), whereas PostgreSQL is a database. Have you looked at pgAdmin? Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] date time function
On 28/06/2007 21:04, Jasbinder Singh Bali wrote: How can i convert this result into absolute number of days. Cast your result to type INTERVAL - something like this: postgres=# select (current_timestamp - timestamp '2007-05-01')::interval; interval -- 58 days 21:10:36.748 (1 row) Of course, you'll need to decide how to handle the part of a day left over. HTH, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to: psql -U user --password password ?
On 03/07/2007 13:47, Stefan Zweig wrote: i know already that there is a way to connect to a remote postgres using psql with the options --host 123.123.123.1 --port 5432 --username --password You can specify the username on the command line, but not the password: the --password option only ensures the psql will prompt for the password. The usual thing, as I understand it, is to use a .pgpass file: have a look at the docs, here: http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html very large *.sql files to import) and more convenient to do the import directly via psql instead of using jdbc/odbc. Also, if the files you're importing were created by pg_dump, they'll contain various "backslash" commands that are psql-specific, so you *have* to re-import them via psql. HTH, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Mugs 'n stuff
Hi all, Is it still possible to get PostgreSQL merchandise? A friend of mine is looking for some, but I can't seem to find where its available. I have a PostgreSQL mug I bought some years ago, which sparked this search (I know, I need a life...) :-) Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Mugs 'n stuff
On 04/07/2007 14:20, Geoffrey wrote: A. Kretschmer wrote: We, the german postgresql user group, have some stuff like blue plush elephants, shirts and coffee-cups, see also http://ads.wars-nicht.de/blog/ (scroll a little bit down), this will be available in Prato. I've always had luck finding such items at http://www.cafepress.com/ Here's the tinyurl to a search for postgresql, which found a few items as well as some semi-related: http://tinyurl.com/27onuq I found all kinds of clothing as well as coffee cups, license plate frames and clocks. Thanks for your replies! - I'll pass them on. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] blobs
On 04/07/2007 17:34, Cesar Alvarez wrote: is there a way to store pictures or executables in postgres?? http://www.postgresql.org/docs/8.2/static/datatype-binary.html --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] catalog location
On 07/07/2007 20:06, Carmen Martinez wrote: Please, I need to know where the catalog tables (pg_class, pg_attrdef...) are located in the postgresql rdbms. Because I can not see them in the pgAdminII interface, like other tables or objects. And I There is a separate list for pgAdmin questions - you're better off to direct pgAdmin questions there. What version of pgAdmin are you using? In versions up to the current one (1.6.3), you need to click View -> System Objects (or something like that). In the coming version to be released in the not-too-distant future, there is a separate "Catalogs" node in the treeview on the left. HTH, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Windows Vista not working
On 09/07/2007 16:49, Richard Eng wrote: First, every time I try ‘psql’ or ‘createdb’ in the command line, it says the password does not match user ‘richard’. I suppose this is because I’m logged in to Windows as ‘richard’. ‘richard’ is my Windows account (administrator) under which I installed postgreSQL. Yes, that's exactly right. You have to specify the PostgreSQL user, NOT the Windows user, as which to execute the command, since it involves a connection to the database. You do this with the -U switch to psql or createdb. - Read the documentation for full details. Does anyone know what the hell is going on??? Hasn’t anyone used postgreSQL under Vista? I you have a look through the (reasonably recent) archives of this list, you'll see that others have and do you might find something of help. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Windows Vista not working
On 09/07/2007 18:33, Raymond O'Donnell wrote: I you have a look through the (reasonably recent) archives of this list, Whoops - that should have been "If you" - sorry for any confusion. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgpass.conf
On 10/07/2007 08:47, Ashish Karalkar wrote: Still the batch asks for the password.!!! I am just not getting why its not reading password from pgpass file. Probably a silly question, but if you're using the createdb utility in the batch file, have you inadvertently included the -W option? - this forces a password prompt. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgpass.conf
On 10/07/2007 11:28, Ashish Karalkar wrote: I have set this succesfully on redhat linux but iam messed up in Windows XP prof. Is there any other thing to do? I'm not a guru, but maybe it's a permissions problem on the pgpass file? Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need help with bash script and postgresql
On 23/07/2007 11:04, Andy Dale wrote: The posgres command in the loop should look like so (not sure about the password): As I understand it, you supply the password via a pgpass file - you can't include it on the command line. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need help with bash script and postgresql
On 23/07/2007 14:22, Perry Smith wrote: First, you can specify a password with -P (I think --password works also). psql --help for optins. -P doesn't specify the password - see below: C:\Documents and Settings\rod>psql --help This is psql 8.2.4, the PostgreSQL interactive terminal. Usage: psql [OPTIONS]... [DBNAME [USERNAME]] Output format options: -P VAR[=ARG]set printing option VAR to ARG (see \pset command) Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need help with bash script and postgresql
On 23/07/2007 14:51, Perry Smith wrote: Ah... I knew that. I always assume it does, then it doesn't work, then I look at the man page. Sorry. Been there, still wearing the t-shirt! :-) Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connection error
On 24/07/2007 15:19, Fst Procurement wrote: Thanks for all your help. It is working. I think.- not sure - I removed PgAdmin 1.7 from my computer and now everything is working. Got it somewhere on the net. Can't remember where. 1.7 was a development version of pgAdmin, so you must have got a snapshot from the pgAdmin site. The current stable version is 1.6, and 1.8 is in beta. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG Admin
On 31/07/2007 16:55, Bob Pawley wrote: Can anyone tell me why a table developed through the PG Admin interface isn't found by SQL when accessing it through the SQL interface?? Hi Bob, No reason that I can think ofcan you describe *IN DETAIL* the steps you followed and the result? Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux distro
I'm about to install a new Linux server, and I've followed this thread with interest, being a tinkerer rather than any sort of expert. I'm going to try out Debian, which I haven't used before - the server it's replacing is running an old RedHat - and would be interested in people's comments. This machine will be running PostgreSQL and nothing else, and I'll probably compile Postgres from source. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] date ranges
On 31/07/2007 17:26, jo wrote: I would like to know if there's a simple way to customize the range for dates, to avoid people insert dates before 1900 and after 2020, for example. How about a check constraint on the date column? Something like - create table the_table ( the_date date, etc... check (the_date >= '1900-01-01' and the_date <= '2020-12-31') ); Then you just need to handle in your application the error raised when someone enters an incorrect date. HTH, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Timestamp in pg_dump output, was Re: [GENERAL] How I can know a back up database is up to date
On 09/08/2007 23:40, [EMAIL PROTECTED] wrote: My database is restored from a dump file every day. How I know that this database is up to date (as it has no timestamp in any table). If I create a file, I can know when I created it by seeing its property. How I can do the same thing with a back up database. Actually, it *would* be really handy if pg_dump included a timestamp in the plain-text output. The version I use regularly (Windows) doesn't...it simply says "PostgreSQL database dump" which is only helpful to a point. :-) Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Timestamp in pg_dump output, was Re: [GENERAL] How I can know a back up database is up to date
On 10/08/2007 18:40, Richard Broersma Jr wrote: If you need to, you can append your own timestamp to the dump file if you need it. Heh heh, I just gave this same advice in reply to the post that prompted this idea. :-) Thanks, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How I can know a back up database is up to date
On 09/08/2007 23:40, [EMAIL PROTECTED] wrote: My database is restored from a dump file every day. How I know that this database is up to date (as it has no timestamp in any table). If I create a file, I can know when I created it by seeing its property. How I can do the same thing with a back up database. Do the backup from a shell script that names the output file with the current date/time. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] SQL question: checking all required items
Hi all, Given the following tables - create table people ( person_id text primary key, person_name text, [...etc...] ); create table items ( item_id text primary key, item_name text, is_required boolean, [...etc...] ); create table items_for_people ( person_id text, item_id text, primary key (person_id, item_id), foreign key person_id references people(person_id), foreign key item_id references items(item_id) ); - how can I find those people who don't have _all_ of the items which are marked "required"? In other words, how do I select those rows in "people" which don't have a corresponding row in "items_for_people" for *each* row in "items" which has is_required=true? Many thanks, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 21:29, Scott Marlowe wrote: select table1.id from table1 where table1.id is not in (select id from table2); Duh! I should have thought of that thanks for that, and apologies for the stupidity (blame it on the glass of wine I had with dinner!). Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Timestamp in pg_dump output, was Re: [GENERAL] How I can know a back up database is up to date
On 10/08/2007 19:10, Tom Lane wrote: Use the "verbose" option. [/me tries it out] That'll do nicely - thanks. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 22:03, Carlos Ortíz wrote: Select * from people where person_id in ( Select person_ID from Items_for_people group by Person_id Having Count(*) = ( Select count(*) from Items Where is_required = true)) That seems to work fine! I'd only change "having count(*) = ..." to "having count(*) >= ..." to allow for people having other items in addition to the required ones. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 21:42, Scott Marlowe wrote: Show us the query when you're done, I'm sure there are enough folks who'd like to see your solution. Here's what I came up with: select distinct ip.person_id from items_for_people ip where exists ( ( select item_id from items where is_required = true ) except ( select ip2.item_id from items_for_people ip2 inner join items i on (ip2.item_id = i.item_id) where ip2.person_id = ip.person_id and i.is_required = true ) ) This finds all those who don't have all the required items, whatever else they may have. Comments and improvements are welcome! Thanks for the help, Ray. ----------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] winxp failed installation problem
On 12/08/2007 18:37, Gilbert Albero wrote: I downloaded the postgresql-8.2.4-1.zip <http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.2.4%2Fwin32%2Fpostgresql-8.2.4-1.zip> and install it to win xp service pack 2 but i'm encountering this error: -- This installation package cannot be opened. Verify that the package exists and you can access it, or contat the application vendor to verify that this is a valid windows installer package. Have you extracted *both* of the .msi files from the zip archive to your hard disk? Just clicking on the installer package inside the zip won't work. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Repeat posts
Hi all, Is it just me? :-) from time to time I get repeat broadcasts from various PG mailing lists - posts that I've already received several days previously are sent again. It's not a major problem, nor even annoying in any wayI was just wondering if anyone else has noticed it. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Finding my database
On 17/08/2007 05:44, [EMAIL PROTECTED] wrote: needed to move it to the D Drive. After completely messing everything up, I am now unable to get it working. I have uninstalled everything and now have v8.2 installed on the D drive, and I put the data directory back within that I know it's not a lot of help to you at this point, but the recommended way to backup and restore a database is using pg_dump, and if you're moving from one major version to another it's the only way. Was the previous installation of Postgres also 8.2? If not - if it was an earlier version - I'd put the old version back, point it at the data directory, then use pg_dump to export the data if you want to upgrade at that point. Have a look at the following, and in particular the examples towards the bottom: http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html HTH, Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding my database
On 17/08/2007 13:32, Raymond O'Donnell wrote: Was the previous installation of Postgres also 8.2? If not - if it was an earlier version - I'd put the old version back, point it at the data directory, then use pg_dump to export the data if you want to upgrade at that point. I meant to add that you can get Win32 versions back as far as 8.0 on the PostgreSQL home page (http://www.postgresql.org/) - top right of the page - click on the "Binary" link beside the version number. To check the version number of your data files, look in the PG_VERSION file. Ray. ------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Repeat posts
On 17/08/2007 13:48, Martijn van Oosterhout wrote: AIUI, posts from non-subscribers can get held for moderation. Because they CC the other people the thread kept going. Later on the moderator approves the messages and they get sent out again. Ah - I see. As I said, it wasn't a problemjust curious. - Thanks for explaining. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Where are the Server error logs
On 17/08/2007 15:47, Rajaram J wrote: where can the server log files be found. if i need to set some parameter in which file do i do that. The server logging method and log files are set in postgresql.conf - this is well documented in the file itself, as well as the PostgreSQL docs. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding my database
On 18/08/2007 09:03, Adrian Pitt wrote: I have put v8.0 back on as you suggested, but so far it has made no difference. I am still unable to get the service restarted. When installing I changed the install to drive D, and also changed the data reference to the directory where I moved the data to. I unchecked the initialize database cluster and it seemed to install fine and did not create the usual data directory within the SQL folder. But for some perplexing reason, even though it all was working fine on drive C, I can't for the life of me work out what has gone wrong. I cannot export any data if it's not finding the What about permissions on the data directory? Apart from that, I'm afraid I'm out of suggestions. If you post the exact text of the error you're getting, maybe other more knowledgeable people can help. Ray. ----------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
