I have this query SELECT * FROM "functions" WHERE (models_mask & 1 > 0) and the I get the following error: PGError: ERROR: operator does not exist: character varying & integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. The models_mask is an integer in the databas...
Does anyone knows how can I set up an insert trigger so when a perform an insert from my application, the data gets inserted and postgres returns, even before the trigger finishes executing? ...
Our product ecosystem: Web/Enterprise Application using EJB in Glassfish V3 and PostgreSQL 8.4. How to detect which field was changed without using dirty flags or re-reading the same record(s) before updating it ? Why: For audit purpose/logging Any recipes or ideas ? Thanks Sven ...
UpDate: Yes, that was it! Like mloskot said, the whole tabel needs to be dumped. selecting fields with sql loses information about fuield widths. Thanks, that fixed it! Hi All, I have a problem with ogr2ogr. I'm trying to dump a PostgreSQL tabel to a Shapefile. ogr2ogr seems to change the size of data-types like integer and char. H...
I have a postgresql table in which I have declared a column 'inv_seq' which is declared as 'SERIAL'. I have a Hibernate bean class to map the table. All the other columns are read properly except this column. here is the declaration in the Hibernate bean class: .... .... @GeneratedValue(strategy=javax.persistence.Generati...
I have a software in PHP and postgres that I use for invoicing. I am running a stock management system that I created. i am trying to create a stock movement page where I can see when a part came in, where it was issued and when and also when it was credited (if it was). I am running 5 tables for the stock. My main one is part2vendor, pa...
I'm trying to think ahead a little and avoid myself some extra pain if possible. I've had this problem in past applications and have usually opted for the most verbose approach but would like the opinions of a few others. If you have a basic table such as below, is it wise, and/or more efficient to include a field which includes a calc...
I have a result set A which is 10 rows 1-10 {1,2,3,4,5,6,7,8,9,10}, and B which is 10 rows consisting of evens 1-20 {2,4,6,8,10,12,14,16,18,20}. I want to find of the elements that are in one set but not both. There are no other columns in the rows. I know a UNION will be A + B. I can find the ones in both A and B with A INTERSECT B. I ...
Hi, I am trying to work with postgresql 'nextval' in PHP. How can I fill in the parenthesis in the third line in order to replace TXN_ID with the value of nextval('schemadb.audit_txn_seq')? $DB->query("SELECT nextval('schemadb.audit_txn_seq')"); $DB->query('SET CONSTRAINTS ALL DEFERRED'); $DB->query('SELECT schemadb.undo_transaction(TX...
Hi I'd like to have a column in a Postgres table which will store the remote IP address of the user connecting to the database. I'm thinking of data type "inet" with some sort of default constraint. Any ideas? Thanks. ...
EDIT OP has acknowledged a mistake when profiling PostgreSQL in his answer below. I am updating this question to reflect the comparison between MyISAM & InnoDB. Hello, I ran a test against MySQL InnoDB, MyISAM, and PostgreSQL to see how well each of these engines performed doing full table scans to understand what the response ti...
how we can delete record of a table when record of another table deleted using function,trigger in postgresql -concepts ...
I'm looking for a more efficient way to run many columns updates on the same table like this: UPDATE TABLE table SET col = regexp_replace( col, 'foo', 'bar' ) WHERE regexp_match( col, 'foo' ); Such that foo, and bar, will be a combination of 40 different regex-replaces. I doubt even 25% of the dataset needs to be updated at all, but w...
I would like to get the columns that an index is on in PostgreSQL. In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column. mysql> show indexes from foos; +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Tabl...
Hello, I am creating to pg_dumps, DUMP1 and DUMP2. DUMP1 and DUMP2 are exactly the same, except DUMP2 was dumped in REVERSE order of DUMP1. Is there anyway that I can sort the two DUMPS so that the two DUMP files are exactly the same (when using a diff)? I am using PHP and linux. I tried using "sort" in linux, but that does not work....
I have this table NAME|VALUE| T | A T | NONE T | B S | NONE where the value NONE is a NULL value in real. I need to do a query who will return all lines BUT if th name have a value i should ONLY return the line with his value like this in result for the table above: NAME|VALUE| T | A T | B S | NONE Do you k...
Hi, Recently I moved a web app I'm developing from MySQL to PostgreSQL for performance reasons (I need functionality PostGIS provides). Now quite often encounter the following error: current transaction is aborted, commands ignored until end of transaction block The server application uses mod_python. The error occurs in the hailing f...
i am getting this error when doing database calls in a sub process using multiprocessing library. http://pastie.org/811424 InternalError: current transaction is aborted, commands ignored until end of transaction block this is to a postgres database, using psycopg2 driver in web.py. how ever if i use threading.Thread instead of multip...
When you get a new badge on stackoverflow.com, a bar appears at the top of the screen informing you of your achievement. It sticks around until the user closes it. I rather like that system of informing the user about new news related to the site or their account. It's fairly unintrusive, but still clearly communicates the information...
In MSSQL, I can do SELECT ISNULL(Field,'Empty') from Table But in PGSQL i get a syntax error. how do I emulate the ISNULL functionality ? ...