Thread: Read-only attributes
I have a table where one of the attributes is the time a row was inserted: 'updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP'. Is there a way to define this attribute so that a user cannot override the default value? I have tried this constraint, but it fails for any INSERT: 'updated ... CHECK (updated = CURRENT_TIMESTAMP)'. ___________________________________ Michael Klatt University of Oklahoma Environmental Verification and Analysis Center 710 Asp Avenue, Suite 8 Norman, OK 73069 405.447.8412 405.447.8455 FAX http://www.evac.ou.edu/ "I'm a great motivator. Everyone says they have to work twice as hard when I'm around."
Michael Klatt <mdklatt@ou.edu> writes: > I have a table where one of the attributes is the time a row was inserted: > 'updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP'. > Is there a way to define this attribute so that a user cannot override the > default value? I have tried this constraint, but it fails for any INSERT: > 'updated ... CHECK (updated = CURRENT_TIMESTAMP)'. No, but if you used a trigger rather than a default value, you could force the field value to be always the time of insert. You should, however, consider carefully whether that is *really* what you want: there's no way to bypass a trigger. Among other things, dumping and reloading such a table would cause all the rows to acquire the time of load. A probably cleaner solution is not to give the untrusted users direct write access on the table at all. Give them write access on a view, and let the INSERT rewrite rule for the view enforce the desired behavior. regards, tom lane
hi all, i'm pretty new to postgres, and thus the post to novice. i tried to perform an upgrade to 7.2 today and have run into some problems at the very end of the process. after making 2 pg_dumpalls (one for globals and one for the data), stopping postgresql, installing the appropriate 7.2 rpms, and then starting postgresql again, i tried to put the data dumps back in and had an error thrown back at me. here's what i did pre install: sudo -u postgres pg_dumpall -g -h myDB > /u/tchatter/globals_dbdump.txt sudo -u postgres pg_dumpall -o -h myDB > /u/tchatter/all_dbdump.txt post install: sudo -u postgres /usr/bin/psql -d template1 -f /u/tchatter/globals_dbdump.txt sudo -u postgres /usr/bin/psql -d template1 -f /u/tchatter/all_dbdump.txt at which point i got: psql:/u/tchatter/all_dbdump.txt:323: \connect: FATAL 1: IDENT authentication failed for user "karl" so i figured that something was wrong with the pg_hba.conf file but i'm not quite sure what i'm looking for in order to correct it. i'd appreciate any pointers for where to look next. thanks! tuna
Tuna, > so i figured that something was wrong with the pg_hba.conf file > but i'm not quite sure what i'm looking for in order to correct it. > > i'd appreciate any pointers for where to look next. > thanks! Check to make sure that the authentication for localhost is set to "TRUST". Anything else will interfere with restoring a pg_dumpall. You can re-secure your authentication after you are done restoring. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
thanks a lot josh! after changing the authentication for localhost i forgot that i had to restart postgres... but after much hairpulling i remembered to restart and it works beautifully:o) take care, tuna On Wed, 2002-02-20 at 17:18, Josh Berkus wrote: > Tuna, > > > so i figured that something was wrong with the pg_hba.conf file > > but i'm not quite sure what i'm looking for in order to correct it. > > > > i'd appreciate any pointers for where to look next. > > thanks! > > Check to make sure that the authentication for localhost is set to > "TRUST". Anything else will interfere with restoring a pg_dumpall. > You can re-secure your authentication after you are done restoring. > > -Josh Berkus > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco