I have a table which stores a category mix for customers. The number of categories may change, but the breakdown must always be less than or equal to 100% for each customer. custom type_ pct ------- ------- ----- Cust1 Type A .33 Cust1 Type B .17 Cust1 Type C .50 Cust2 Type A ....
8-byte integers are now the default for Postgres 8.4, so it allows microsecond values to be stored. I don't care too much about real microsecond precision (probably depends on OS capabilities?!) - But does Postgres guarantee, that the timestamp values (inserted by current_timestamp) are always different between any two transactions? ...
Is there something simiar? I have this definition create table xyz ( fecha_alta timestamp NOT NULL DEFAULT localtimestamp, .... ) I tried to execute the same in Derby, but it complains with: Error: Syntax error: Encountered "localtimestamp" at line 3, column 41. What's the equivalent? ...
I'm trying to alter the table named company, but it will display the error syntax error at or near "(" LINE 2: ADD( company_access_level short NOT NULL, My syntax is ALTER TABLE company ADD company_access_level short NOT NULL, workgroup_level short NOT NULL, Company Logon URL character varying NOT NULL, Company Logoff URL character...
I am trying to setup PostgreSQL and allow only certain Windows users to access the data from the database. Setting up Windows Authentication is Quite easy with MS SQL, but I can't figure out how to set it up in PostgreSQL. I have gone through the documentation at http://www.postgresql.org/docs/current/static/auth-methods.html and edite...
I am trying to create entity classes form database with Netbeans wizard "Entiry classes from database". But if the field type in database table is an a custom domain the type of field is set to "Serializable", but it should be String or Integer. Because domains are just wrappers around "normal" Postgresql datatypes. And if my table cont...
I have a table I'd like to sort with a "priority" column. This column needs to be reordered when the priority of a record is changed or records are removed. Think of it as an array. The values will be modified in a UI so I want them to remain whole numbers and represent the true position within the larger recordset. The priority column w...
I am building an app with ASP.NET MVC2, Fluent NHibernate, StructureMap, and PostgreSQL. I am a total newbie when it comes to Fluent NHibernate. I got a setup going from a couple different sources but when I build and run my app it doesnt create the database tables for the database in my connection string. I have code in a few different ...
At work, we do distributed testing, where rsync out to everyone elses machine and distribute out test files to speed up the testing process. One issue we are running into is that if someone has an instance of pgadmin open, it won't let us reset the test database due to connections being open. Is there any way we can programmatically kil...
I'm trying to work around Postgresql 8.4's lack of MATCH PARTIAL. I have the following schema: [vehicles] lot_id | vin | source | year | make | model ... primary key ( lot_id, vin, source ) [pictures] picture_id | lot_id | vin | url | sha1 ... primary key ( picture_id ) Now, what I want is a compound FOREIGN KEY that REFERENCES t...
The documentation for Postgresql window functions seems to imply you can use the same named window in multiple places in your query. However, I can't figure out how do I create a named window? SELECT first_value(vin) OVER( PARTITION BY vin ) AS w, first_value(make) OVER w FROM inventory.vehicles WHERE lot_Id = 9999 AND make is not null;...
Hi all, I am faced with the task of building a new component to be integrated into a large existing C codebase. The component is essentially a kind of compiler, and will be complicated enough that I would like to write it in OCaml (for reasons along the lines of those given here). I know that OCaml-C interaction is possible (as per th...
Hi, consider a SQL table, which stores hierarchical data using MPTT (Modified Preorder Tree Traversal) method. CREATE TABLE node ( id SERIAL NOT NULL, -- primary key -- Nested mptt tree model. lft INT NOT NULL, rgt INT NOT NULL, -- Some legacy a...
Suppose we have a standard has and belongs to many association between products and categories product_categories table consists of product_id and category_id pairs. We have list of category_id's and need to select all products that belongs to all these categories. The best approach that came to my mind is to have multiple EXISTS() in ...
I have a working WAL shipping setup with a warm standby slave server applying the WAL files. When I create the pg_standby trigger file, it detects this at once, but it takes about 10-15 minutes to actually be ready for accepting connections. Most of the time is spent waiting for .history files. The trigger file is empty, so a "smart" f...
I have a field that has a max_length set. When I save a model instance, and the field's value is greater than max_length, Django enforces that max_length at the database level. (See Django docs on models: http://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.CharField.max_length) However, since I am using Postgres, I ...
I know that when I UPDATE a row in Pg, that row gets rewritten and the old row gets deactivated when the new row gets activated. I know this is due to how the MVCC layer is implemented. What the advantages then of UPDATE over DELETE ... INSERT? Is there anything else to be said about the relationship of UPDATE and DELETE in Postgresql? ...
When I subtract timestamps, the interval is in form DD:HH:MM:SS. How can I convert it all to minutes without extracting days and hours and multiplication/addition? I'm looking for a single function which I can substitute for date_part in this query so that it returns 65: select date_part('minutes', '65 minutes'::interval); Context: I ...
Hi there, I have a table in postgresql. The following table "animals" will do to explain my problem: name ------ tiger cat dog Now I am using the following query: SELECT array_to_string(array_agg("name"), ', ') FROM animals; The result is: "tiger, cat, dog". But I would like to sort the aggregate, before it is converted in...
The story goes like this: I have Zend server CE PHP5.3 with all the modules available enabled running on Debian Etch. I decided to upgrade Debian Etch to Lenny, then also upgraded PostgreSQL 8.1 to 8.3 Restored the database mydatabase --> $ psql mydatabase < schema_and_data.sql Checked the web site that connects to a postgreSQL databas...