Re: [GENERAL] How to intelligently work with views that depend on other views

Melvin Davidson wrote: The best solution, IMHO, is don't create views that depend on other views. ... Much better to just make each view a stand alone. Seconding Mr. Davidson's advice. But, given that you are in the situation, here is a workable alternative: Matthew Wilson On Thu, Aug 6,

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson wrote: 9. >1) What happens if someone mis-types the account-id? > To correct that, you also need to correct the FK field in the other dozen tables. >2) What happens when your company starts a new project (or buys a I would not consider the general use of natural primary

Re: [GENERAL] Using a VIEW as a temporary mechanism for renaming a table

Ben Buckman wrote: Hello, I would like to rename a table with ~35k rows (on pgsql 9.4), let's say from `oldthings` to `newthings`. Our application is actively reading from and writing to this table, and the code will break if the table name suddenly changes at runtime. So I can't simply run an `A

Re: [GENERAL] Running query without trigger?

haman...@t-online.de wrote: Hi, a table is associated with a trigger for normal use. An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update, and then re-enable it. This means, however, that normal user activity has to be locked out. There are two possible sce

Re: [GENERAL] MediaWiki + PostgreSQL is not ready for production?

John R Pierce wrote: On 7/18/2016 9:14 PM, Tatsuo Ishii wrote: I found following comment for using PostgreSQL with MediaWiki: https://www.mediawiki.org/wiki/Compatibility#Database "Anything other than MySQL or MariaDB is not recommended for production use at this point." This is a sad and dis

Re: [GENERAL] Invoice Table Design

rob wrote: Hi Rich, thanks for the response -- going from Mongo to Postgres does require the kind of approach you suggest. I suppose my question was a little bit more along the lines if anyone has experience with designing payment / invoicing systems and any caveats they may have encountered al

Re: [GENERAL] Means to emulate global temporary table

Karsten Hilbert wrote: On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote: I don't see where "call a setup function immediately after connecting" Sounds like a "login trigger", more generally an ON CONNECT event trigger, which we don't have at the moment as far as I know. One

[GENERAL] Deletion Challenge

/* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history grows large enough to adversely effect performance, and also becomes less relevant for retention. I have devised

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: If I understand correctly the value of "click" always advances and within a "click" the "cash_journal_id" always advances - not necessarily by single steps so within a fairian_id, ordering by "click" plus "cash_journal_id" would return the records in order from which you

Re: [GENERAL] Deletion Challenge

Adrian Klaver wrote: On 12/05/2015 08:08 AM, Berend Tober wrote: /* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history grows large enough to adversely effect

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: The two general solutions are the "keep the last one" proposed by Adrian "keep the last N" that I sent. But it might be worth stepping back a bit. You said you are having performance problems that you feel would be improved by removing only a million rows which doesn't soun

Re: [GENERAL] Deletion Challenge

Jim Nasby wrote: On 12/9/15 7:59 PM, Berend Tober wrote: This project is a game, btw, described at You might be interested in https://schemaverse.com/ Schemaverse looks somewhat interesting. Seems like it and Fairwinds share in common Postgresql as a foundation, but they are very

Re: [GENERAL] Code of Conduct

Geoff Winkless wrote: On 11 January 2016 at 20:13, Regina Obe wrote: While this is funny to some, I don't think it adds value to this conversation. I would consider it a derailment and not very helpful. If I had a Coc to point at, I would point at the section I feel you are violating. If

Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

Bill Moran wrote: On Tue, 12 Jan 2016 22:10:43 -0500 Tom Lane wrote: Kevin Grittner writes: I'm not the greatest word-smith, but I'll attempt to rework Josh's draft to something that seems more "natural" to me. Minor (or not?) comment: Whether or not it is a foregone conclusion that thi

Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

Joshua D. Drake wrote: On 01/13/2016 06:00 PM, Berend Tober wrote: Whether or not it is a foregone conclusion that this community will adopt a CoC, it seems like a mailing list is not the place to do revision control. Can you people start a github project or something to develope your ideas

Re: [GENERAL] A motion

Adrian Klaver wrote: Motion: The Coc discussion be moved to its own list where those who care can argue to their hearts content and leave the rest of us to deal with technical questions. Upon a decision on said list the result be posted to the Postgres web site for consideration. Been suggest

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

Don Parris wrote: I have several tables... and want db users to be able to add or update ... ... in one step, and get all the information into the correct tables. I think I am ok with setting the privileges on the tables and columns as appropriate to allow each group to select, insert and update

Re: [GENERAL] Logger into table and/or to cli

Thiemo Kellner, NHC Barhufpflege wrote: I would like to log from within pgsql into table and/or to command line, something like https://sourceforge.net/projects/plnsqllogger/ . Does anybody know of an open source solution available? I like "18.8.4. Using CSV-Format Log Output", although it d

Re: [GENERAL] what database schema version management system to use?

John R Pierce wrote: On 4/6/2016 3:55 AM, Alexey Bashtanov wrote: I am searching for a proper database schema version management system. At my $job we're old school. our schemas are versioned. there's a settings table with (setting TEXT, value TEXT) fields, a row in that is ('version', '1

Re: [GENERAL] "Keyed" sequence?

Adrian Klaver wrote: On 04/28/2016 11:48 AM, Israel Brewster wrote: On Apr 28, 2016, at 10:39 AM, Vik Fearing wrote: On 04/28/2016 08:30 PM, Israel Brewster wrote: This is probably crazy talk, but in Postgresql is there any way to have a "keyed" sequence? That is, I have a table with a list

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com mailto:drum.lu...@gmail.com>> wrote: * This is what I did... |-- Creating the table CREATE TABLE public.company_seqs (company_id BIGINTNOT NULL, last_seq BIGINTNOT NULL DEFAULT

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com ... I would expect a minimum of respect from the members of this list, but seems you got none. If someone would need my help, I'd never insult him/her like you guys are doing. If my questions are too "

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: I'm just having some problem when doing: INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) VALUES (66,'tes...@test.com ','password','0','2016-05-03 00:01:01','2016-05-03 00:

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: ​Berend already identified the problem for you. Thank you. -- 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] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: It's working now... Final code: ALTER TABLE public.companies ADD COLUMN client_code_increment integer; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAU

Re: [GENERAL] How do I aggregate data from multiple rows into a delimited list?

D. Dante Lorenso wrote: I want to select several rows of data and have them returned in a single record with the rows joined by a delimiter. Review the User Comments at "http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html"; for some ideas. ---(e

[GENERAL] How to individually list the DDL for all individual data base objects

Is there a good way to individually list the DDL for all individual data base objects? Running a data base dump like: pg_dump mydatabase > mydatabase-database.sql produces one big file with all the DDL and DML to restore the data base, which is very convenient for most cases. Using that I

Re: [GENERAL] How to individually list the DDL for all individual data base objects

François Beausoleil wrote: Le 2014-11-24 à 10:14, Berend Tober a écrit : Is there a good way to individually list the DDL for all individual data base objects? Were you aware that pg_restore can restore to STDOUT, and output DDL for only a single named object from a custom dump file

Re: [GENERAL] How to individually list the DDL for all individual data base objects

Adrian Klaver wrote: On 11/24/2014 08:12 AM, Berend Tober wrote: François Beausoleil wrote: Le 2014-11-24 à 10:14, Berend Tober a écrit : Is there a good way to individually list the DDL for all individual data base objects? Were you aware that pg_restore can restore to STDOUT, and

Re: [GENERAL] How to individually list the DDL for all individual data base objects

Melvin Davidson wrote: You should probably look at the pg_extractor utility. https://github.com/omniti-labs/pg_extractor With it, you can dump individual or selected objects to separate directories. That looks like what I'm looking for. (Note: I did Google searching, but apparently did not

Re: [GENERAL] How to insert into 2 tables from a view?

Chris Hoover wrote:> Correct sql: > BEGIN; > > CREATE TABLE table1 ( >table1_id SERIAL PRIMARY KEY, >table1_field1 TEXT > ); > > CREATE TABLE table2 ( >table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) > ON DELETE CASCADE, >table2_field1 TEXT > ); > > CREATE VIEW

Re: [GENERAL] Simple Atomic Relationship Insert

John McKown wrote: On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco mailto:robert.difa...@gmail.com>>wrote: Let's say I have two tables like this (I'm leaving stuff out for simplicity): CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1; CREATE TABLE hometowns ( id INTE

[GENERAL] Can pg_restore produce create or replace commands

I often work with the output of pg_restore from a custom format dump file. For example a file produced by running pg_restore -s -1 -L listfile dumpfile where listfile has been edited to comment out most of the rows to leave only the data base objects I'm currently interested in. Most often,

Re: [GENERAL] Can pg_restore produce create or replace commands

Adrian Klaver wrote: On 01/17/2015 10:05 AM, Berend Tober wrote: I often work with the output of pg_restore from a custom format dump file... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace functio

Re: [GENERAL] Can pg_restore produce create or replace commands

Adrian Klaver wrote: On 01/17/2015 10:05 AM, Berend Tober wrote: I often work with the output of pg_restore from a custom format dump file. ... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace function

Re: [GENERAL] Can pg_restore produce create or replace commands

Thomas Kellerer wrote: Berend Tober wrote on 17.01.2015 19:05: I often work with the output of pg_restore from a custom format dump file. ... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a "create or replace functio

Re: [GENERAL] How to hide stored procedure's bodies from specific user

Saimon Lim wrote: Thanks for your help I want to restrict some postgres users as much as possible and allow them to execute a few my own stored procedures only. Create the function that you want restrict access to in a separate 'private' schema to which usage is not granted. Create the func

Re: [GENERAL] How to hide stored procedure's bodies from specific user

Guillaume Lelarge wrote: 2015-02-14 14:07 GMT+01:00 Berend Tober mailto:bto...@broadstripe.net>>: Saimon Lim wrote: Thanks for your help I want to restrict some postgres users as much as possible and allow them to execute a few my own stored procedure

Re: [GENERAL] Server SSL key with passphrase

dhanuj hippie wrote: I have a postgres-9.6 server running with SSL enabled, and I have setup the certificates as per documentation. But currently the key file is not protected by passphrase. Does postgres provide a way to use passphrase protected keys ? If by "per documentation" you refer t

Re: [GENERAL] Question regarding pgsql-general mailing list.

FYI, Postgresql caps for sale on ebay at http://www.ebay.com/itm/162564660418 -- 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 drop column from interrelated views

Guyren Howe wrote: I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear to

Re: [GENERAL] How to drop column from interrelated views

Guyren Howe wrote: On Jul 8, 2017, at 16:11 , Berend Tober mailto:bto...@computer.org>> wrote: Guyren Howe wrote: I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the fiel

Re: [GENERAL] What is exactly a schema?

marcelo wrote: The question is not trivial. Could I maintain two or three separate/distinct "versions" of same database using one schema for every of them? Could some tables (in the public schema) be shared among all the schemas? Yes and yes. In the Postgresql world, the word "schema" is may

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

Alexander Farber wrote: Good evening, In a word game I store all player moves in the table: CREATE TYPE words_action AS ENUM ( 'play', 'skip', 'swap', 'resign', 'ban', 'expire' ); CREATE TABLE words_moves ( mid BIGSERIAL PRIMARY KEY,

Re: [GENERAL] Best way to allow column to initially be null?

Glen Huang wrote: > I’m trying to make a column have these properties: > > 1. When a row is inserted, this column is allowed to be null. 2. When the row is updated, no null > can be assigned to it this column. > > I initially thought I can drop the not null constraint before insertion and turn i

Re: [GENERAL] Client Authentication methods

chiru r wrote: Hi All, I am trying to understand the Authentication method in pg_hba.conf file (password & md5) in PostgreSQL database server. I am assuming that the user provides the connection string host/usser/password,then client will go and contact the DB server pg_hba.conf file in memo

Re: [GENERAL] Trigger to run @ connection time?

Alban Hertroys wrote: On Mar 11, 2008, at 10:28 PM, Tom Lane wrote: An ON CONNECT trigger enforced by the database seems a bit scary to me. If it's broken, how you gonna get into the DB to fix it? regards, tom lane If creating the trigger wouldn't be possible from within the data

Re: [GENERAL] Primary Key with serial

x asasaxax wrote: I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1

Re: [GENERAL] Call for design: PostgreSQL mugs

Peter Geoghegan wrote: On Thu, Sep 5, 2013 at 8:22 AM, Merlin Moncure wrote: I'm still partial to this guy: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg I dislike that image, and always have. ... I agree with Mr. Geoghegan. That image should be eradicated from the p

Re: [GENERAL] Whole record returned in stead of field

Johann Spies wrote: SELECT A.article_id, A publication_year You are missing a "." between in what you think is the second column (after the "A"). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/p

Re: [GENERAL] create a script which imports csv data

Robert Buckley wrote: I have to create a script which imports csv data into postgresql The csv data is automatically created from an external database so I have no influence over which columns etc are downloaded. How can I best create a table for the import? This is what I do: 1) I have a ta

Re: [GENERAL] Sequence moves forward when failover is triggerred

Craig Ringer wrote: On 07/11/2012 07:23 AM, Andy Chambers wrote: I think I made a poor decision by having our application generate checkbook numbers on demand using sequences. Sure did. Sequences are exempt from most transactional rules; that's why they're fast and lock-free. This may be a

[GENERAL] How to ;ist all table foreign key dependency relationships

I am interested in listing all the pairwise foreign key dependencies between tables by name, i.e., if I have table A with some primary key column A.id, and table B has a foreign key referencing A.id, then table B depends on A. I found some things by Google searching, but most of these were not

Re: [GENERAL] obtain the difference between successive rows

Thalis Kalfigkopoulos wrote: On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell wrote: On 20/10/2012 11:54, ochaussavoine wrote: I have a table 'tmvt' with a field 'created' in the row, and would like to compute the difference between successive rows. The solution I found is: I think you can

Re: [GENERAL] obtain the difference between successive rows

Thalis Kalfigkopoulos wrote: SELECT current_meter_reading - lag(current_meter_reading) OVER(ORDER BY current_reading_date) AS kWh_diff, extract('days' FROM current_reading_date - lag(current_reading_date) OVER(ORDER BY current_reading_date)) as num_service_days FROM mytable; How would you get t

Re: [GENERAL] obtain the difference between successive rows

Raymond O'Donnell wrote: On 20/10/2012 17:02, Berend Tober wrote: Thalis Kalfigkopoulos wrote: How would you get the previous reading (and perhaps the previous read date) to also appear ... Just include them in the SELECT: Well, that is surprisingly easy! How about this then: the

Re: [GENERAL] obtain the difference between successive rows

Berend Tober wrote: Raymond O'Donnell wrote: On 20/10/2012 17:02, Berend Tober wrote: Thalis Kalfigkopoulos wrote: How would you get the previous reading (and perhaps the previous read date) to also appear ... Just include them in the SELECT: Well, that is surprisingly easy! How

Re: [GENERAL] obtain the difference between successive rows

Виктор Егоров wrote: 2012/10/20 Berend Tober : Your suggestion almost worked as is for this, except that you have to note that reading for meter #2 and meter #3 overlap ... You can do … OVER(PARTITION BY electric_meter_pk ORDER BY reading_date) to split you data by meter. That looks like

Re: [GENERAL] obtain the difference between successive rows

Thalis Kalfigkopoulos wrote: On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell wrote: On 20/10/2012 11:54, ochaussavoine wrote: I have a table 'tmvt' with a field 'created' in the row, and would like to compute the difference between successive rows. The solution I found is: I think you can

Re: [GENERAL] New Zealand Postgis DBA job vacancy

pcr...@pcreso.com wrote: Mat be of interest to someone here... http://careers.eroad.co.nz/vacancies/showVacancy/11 Brent Wood I attended a technical conference in the US recently, and someone posted an Auckland job flyer (not this same organization, though). He included a statement regard

Re: [GENERAL] New Zealand Postgis DBA job vacancy

Gavin Flower wrote: On 27/12/12 07:54, Berend Tober wrote: ...regarding New Zealand expatriate work visas and ... that it was notably easier for foreigners under age 30 to get permission to work. Can anyone with direct experience comment on this government-sanctioned discrimination? I think

Re: [GENERAL] creating "a perfect sequence" column

Jack Brown wrote: Dear list, I need some tips and/or pointers to relevant documentation implementing (what I chose to call) "a perfect sequence" i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its

Re: [GENERAL] Advice on implementing counters in postgreSQL

Marco Bizzarri wrote: > Hi all. > > I need to keep a numer of counters in my application; my counters are > currently stored in a table: > > name | next_value | year > > > The counters must be progressive numbers with no holes in between > them, and they must restart from 1 every year. What I've

Re: [GENERAL] Schema Upgrade Howto

Thomas Guettler wrote: Hi, is there a schema upgrade howto? I could not find much with google. There is a running DB and a development DB. The development DB has some tables, columns and indexes added. What is the preferred way to upgrade? I see these solutions: - pg_dump production DB. Insta

Re: [GENERAL] serial data type usage

EXT-Rothermel, Peter M wrote: I have a table where I would like the primary key to be generated during the insert. Here is a simplified example: CREATE TABLE employee_type { tname varchar(10) PRIMARY KEY, id_prefix char(1) ; ... } tname | id_prefix --+--

Re: [GENERAL] Easy form of "insert if it isn't already there"?

Chris Angelico wrote: On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak wrote: e.g. You can use BEGIN... EXCEPTION END, good example of such approach is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE; I wonder why, in that exampl

Re: [GENERAL] manage changes to views having depencies

Eric Worden wrote: Can anyone recommend a reasonably efficient system for changing a view definition (say by adding a column) when it has a bunch of dependent functions? Right now I work with the output from pg_dump to recreate things after doing "DROP VIEW ... CASCADE". But the pg_dump schema

Re: [GENERAL] preserving data after updates

> I use a modified form of option 3 with an ON UPDATE RULE the update rule > copies the row to an inherited table... I just gotta say that THAT is one COOL use of PG inheritance! Do you find that it works well and is robust and all the good stuff it seems like would be the case? -- Berend

Re: [GENERAL] Postgres mystery

Michael Fuhr wrote: On Wed, Mar 30, 2005 at 09:11:09AM +0200, Shaun Clements wrote: Can anyone tell me what the problem is here: I am inserting into a table via a stored procedure, to a few columns within the table and postgres is throwing a CANNOT EXECUTE NULL QUERY. EXECUTE ''INSERT INTO tab

[GENERAL] Preserving data after updates

A few months ago, a question by Scott Frankel produced a suggestion from Greg Patnude which I found very exciting that had to do with using pg table inheritance to maintain an audit or row change history table. I've been testing Patnude's idea and ran into a problem, described below, and wanted

Re: [GENERAL] Postgres in government

Scott Marlowe wrote: On Wed, 2005-05-18 at 21:24, Mark Steckel wrote: ...We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. So is the implication that they think open sour

Re: [GENERAL] Preserving data after updates

Tom Lane wrote: Berend Tober <[EMAIL PROTECTED]> writes: But after doing a dump of the modified data base, the script created by pg dump wants to recreate the history table as ... CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL))), H

Re: [GENERAL] preserving data after updates

Greg Patnude wrote: Yeah… this is where the inheritance model gets a little funky… What do you have “SQL_INEHERITANCE” set to when you dump the database ? I’ve never tested this so I don’t know if it makes a difference being on or off when you dump a table…. You might try it and compare the two

Re: [GENERAL] Preserving data after updates

Tom Lane wrote: The case I tested seems to work in 7.3 as well: CREATE TABLE person (last_name varchar(24), first_name varchar(24), CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL; CREATE TABLE person_change_history( action VARCHAR(6), update_dat

Re: [GENERAL] Preserving data after updates

Tom Lane wrote: What do you get from select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person'::regclass; conname | consrc --

Re: [GENERAL] Preserving data after updates

Tom Lane wrote: What do you get from select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person'::regclass; conname | consrc -

Re: Inherited constraints and search paths (was Re: [GENERAL] Preserving

Tom Lane wrote: Berend Tober <[EMAIL PROTECTED]> writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. This creates a problem because reverse-listing of the constraints varies depending o

Re: [HACKERS] Inherited constraints and search paths (was Re: [GENERAL]

Simon Riggs wrote: On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: Berend Tober <[EMAIL PROTECTED]> writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. ... Comments

Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was

Tom Lane wrote: ... I just ran into another inheritance-related oddness. Well maybe it is not really an oddness -- you tell me. The problem stems from the fact that I did not originally plan on using inhertiance and so did not include the ONLY keyword in the FROM clause of queries coded into my

Re: [GENERAL] enebling regular user to create new users ?

Zlatko Matić wrote: I know that superusers are allowed to do everything on the database, but I consider this as dangerous. I want to have some user group with rights of creating new users and giving them some authorizations, but without such wide power as superusers have. So, I was thinking

Re: [GENERAL] Backing up multiple databases

; # CC: pgsql-general@postgresql.org # # Modified by Berend Tober 2005-06-17 to: # a) include tcp port as command line parameter. # b) include syntax help. # c) include Postgresql version information in global.sql output file. # d) append ".sql" file name suffix to dump o

Re: [GENERAL] chosing a database name

Alvaro Herrera wrote: On Wed, Jul 13, 2005 at 05:56:03PM +0200, Karsten Hilbert wrote: we are developing GNUmed, a medical practice management application running on PostgreSQL (you want your medical data to be hosted by something reliable, don't you ;-) We are putting out our first releas

Re: [GENERAL] How to create unique constraint on NULL columns

Andrus wrote: How to create constraint so that NULL values are treated equal and second insert is rejected ? Rethink your data design --- this behavior is required by the SQL standard. I have a table of users permissions by departments CREATE TABLE permission ( id serial, user_

Re: [GENERAL] How to create unique constraint on NULL columns

Andrus wrote: "if department _id is NULL, user has access to all departments data." This is your problem. You've assigned meaning to the "value" NULL. CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, UNIQUE (

Re: [GENERAL] help me learn

suresh ramasamy wrote: i'm new to postgreSQL as well as new to database concepts. please tell me how can i learn. i mean the easiest and fast way. Your help will be appreciated. Make an appropriate posting to pgsql-jobs? ---(end of broadcast)

Re: [GENERAL] Implementing a change log

Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My original intention was to keep two sets of tables. The first containing only the working set of current records. The second containing all prior versions. I haven't experimented with such a setup yet and I'm wonde

Re: [GENERAL] Implementing a change log

Berend Tober wrote: ...See "User Comments" at "http://www.postgresql.org/docs/8.0/interactive/tutorial-inheritance.html"; for something that should set you afire. And, commenting on my own post, try this cool function: /* The following is based on suggestion by Mik

Re: [GENERAL] Implementing a change log

Mike Rylander wrote: On 9/20/05, Berend Tober <[EMAIL PROTECTED]> wrote: /* The following is based on suggestion by Mike Rylander posted on Postgresql-General Sun, 18 Sep 2005 23:29:51 + Rylander's original suggestion employed a trigger and tracked only row updates. My imp

Re: [GENERAL] Securing Postgres

L van der Walt wrote: I would like to secure Postgres completly. Some issues that I don't know you to fix: 1. User postgres can use psql (...) to do anything. 2. User root can su to postgres and thus do anything. 3. Disable all tools like pg_dump How do I secure a database if I don't trust t

Re: [GENERAL] Securing Postgres

Tom Lane wrote: L van der Walt <[EMAIL PROTECTED]> writes: ...I can use encryption to protect the data. If you think encryption will protect you against someone with root privileges, you're sadly mistaken. ... All the same points hold for SQL Server of course --- the fact that you w

Re: [GENERAL] Dump all except some tables?

WireSpot wrote: Is it possible to dump an entire database but to skip one or two tables? Or, conversely, to restore an entire dump except for one or two tables? (Although I'd prefer the first version.) The only related option for both pg_dump and pg_restore is --table, which only takes 1 (on

Re: [GENERAL] getting around---division by zero on numeric

Richard Huxton wrote: Tim Nelson wrote: I am getting division by zero on a calculated field ( sum(sales) is 0 ) It's a two-stage process, so you'll want a sub-query. Something like: ... Thanks. That's a cool addition to my bag of tricks. ---(end of broadcast)-

Re: [GENERAL] Duplicate Row Removal

Dean Gibson (DB Administrator) wrote: CREATE TABLE new_name AS SELECT DISTINCT * FROM old_name; DROP TABLE old_name; ALTER TABLE new_name RENAME TO old_name; The problem with this technique is that it doesn't account for indexes, foreign key references, and other dependencies. Another ap

[GENERAL] Most significant digit number formatting

ow it to come to an end. Regards, Berend Tober begin:vcard fn:Berend Tober n:Tober;Berend org:Seaworthy Systems, Inc. adr:;;22 Main Street;Centerbrook;CT;06409;USA email;internet:[EMAIL PROTECTED] tel;work:860-767-9061 url:http://www.seaworthysys.com version:2.1 end:vcard -

Re: [GENERAL] Most significant digit number formatting

044 That is why I didn't realize ROUND was an option, but for me it still is not since the report is produced by a Delphi application. I suppose I can accomplish this formatting programmatically within the Delphi application, but I was hoping to have the data base do it directly. Thanks, B

[GENERAL] Multi-parameter aggregates.

I'm interested in defining a covariance aggregate function. (As a refresher, remember that covariance is a little bit like variance, but is between two variables: cov(X,Y)= - , where the angular brackets in this case denote taking the averag. Variance is a special case when X and Y are

Re: [GENERAL] Multi-parameter aggregates.

Hakan Kocaman wrote: have you considered using pl/r. http://www.joeconway.com/plr/ I think R got a covariance-function. http://www.r-project.org/ That would be, like, the easy way. Thanks! Berend begin:vcard fn:Berend Tober n:Tober;Berend org:Seaworthy Systems, Inc. adr:;;22 Main Stree

Re: [GENERAL] Multi-parameter aggregates.

Michael Fuhr wrote: On Mon, Nov 21, 2005 at 11:03:22AM -0500, Berend Tober wrote: I'm interested in defining a covariance aggregate function. I think aggregates must take a single value, so the above won't work as written. However, in PostgreSQL 8.0 or later you could

[GENERAL] Function name variable within a non-trigger function

I know that within a trigger function the functin name can be referenced by the special variable TG_NAME, so I could include raise an exception that identified its source with a line like: RAISE EXCEPTION ''ERROR IN %'', TG_NAME; Is there a similar set of special variables defined for "no

Re: [GENERAL] Can we convert from Postgres to Oracle !!???

Sandeep Kumar Jakkaraju wrote: Can we convert from Postgres to Oracle !!??? Umm, this would be the wrong forum for that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

  1   2   >