SELECT trim(to_char(123,'9999999999D99')); 123.00 (1 row) SELECT trim(to_char(00,'9999999999D99')); .00 (1 row) I wrote the above query it giving the result as .00 if the amount is 0 (Zeor)? I need the output formate as 0.00. how to do this. PART -1 Format double precision in PostgreSQL ...
Hi, How would i rewrite this rails query to run on Heroku (Portegres) @students = Student.find(:all, :conditions =>["(concat(first_name, ' ', last_name) LIKE ?) OR (concat(first_name, ' ', middle_names, ' ', last_name) LIKE ?)", "%#{params[:search]}%"...
I am getting this error: DataError: (DataError) invalid input syntax for integer: "1.50" CONTEXT: PL/pgSQL function "sp_aggregate_cart" line 82 at FOR over EXECUTE statement 'SELECT total_items, subtotal, is_shipping_required, discount_other, is_shipping_discount FROM sp_aggregate_cart(8135)' {} When running my application code. When...
We need to make extensive use of the 'xml_is_well_formed' function provided by the XML2 module. Yet the documentation says that the xml2 module will be deprecated since "XML syntax checking and XPath queries" is covered by the XML-related functionality based on the SQL/XML standard in the core server from PostgreSQL 8.3 onwards. Howeve...
There are many disputes about what should use developers in sql queries. I guess I ask old question :) Buy can somebody completely explain what is big difference in these two methods? is there maybe some misunderstanding in database theory of programmers? Can somebody give good article about this question or just say - what is a differen...
With PostgreSQL, one of my tables has an 'interval' column, values of which I would like to extract as something I can manipulate (datetime.timedelta?); however I am using PyGreSQL which seems to be returning intervals as strings, which is less than helpful. Where should I be looking to either parse the interval or make PyGreSQL return ...
I'm trying to change the application schema of a database in Postgres...so I need to copy data from one table to another. In the original table, co-ordinates are specified as numeric values in two separate columns, one for the x value and one for the y value. In the new table, the co-ordinates need to be stored as one value of the point ...
I have a UML diagram of what I need my database schema to look like. This diagram includes roles and cardinality. How can I enforce cardinality in Postgres? ...
I write MS Access / VB / SQL Applications, customized. One of my clients has a truly massive Access application I wrote them, and their number of active users have just expanded to over 15. Running into more data corruption issues, I am recommending they port to an SQL backend for stability. (Of course). They would prefer to invest in a...
I am trying to count a substring, while matching different values from another column. The following statement gives me a syntax error on the where clause. Is the following even possible, and what is the correct syntax? select address, datacenter, ifdesc, count(substring(ifdesc, 'Ethernet0/*') where ifadminstatus ...
I have two Postgres databases. In one I have two tables, each with about 8,000,000 rows, and a count on either of them takes about a second. In another database, also Postgres, there are tables that are 1,000,000 rows, and a count takes 10s, and one table thats about 6,000,000 rows, and count takes 3min to run. What factors determine ...
Are there are any tools available for use with Postgres so that I can generate UML for the database that I have already created. ...
I'm taking over development of a set of User Defined Functions and User Defined Aggregates written for PostgreSQL, and I'm having difficulty diagnosing what's causing the code to constantly seg fault. Does any one know if/how it's possible to use GDB with a UDF written in C++? A couple of google searches didn't turn up anything. I've ...
I have a table with a few million tuples. I perform updates in most of them. The first update takes about a minute. The second, takes two minutes. The third update takes four minutes. After that, I execute a VACUUM FULL. Then, I execute the update again, which takes two minutes. If I dump the database and recreate it, the first upda...
If I set up an AFTER trigger in PostgreSQL to fire after an insert/update, will the calling software have to wait for the trigger to finish before returning control to the calling software? Or will the trigger run on its own behind the scenes? ...
I am making a function (PHP). In this function it basically uses the dbms specified and connects, submits the appropriate query, <insert my problem here>, and disconnects. DBMSs include MySQL, MSSQL, Postgres, and Oracle. I am assuming that outside of this function, the user would not have access to specified DMBS, so whatever results I...
The title says it all. I want to set the start value for auto increment on a column to 1000000 (to avoid conflicts with a legacy application), but can't find any documentation that tells me how. Pretty standard table, here's what the relevant bit looks like: User: attributes: export: tables columns: id: type: inte...
I am attempting to create a View in PhpPgAdmin (PostGreSQL db) which has the following SQL statement: DELETE FROM myTable WHERE myTable.error IS NULL; PhpPgAdmin gives me the following error: ERROR: syntax error at or near "DELETE" at character 59 In statement: CREATE OR REPLACE VIEW "Schema1"."Delete empty errors" AS DELETE ...
CREATE TYPE mpaa_rating AS ENUM ( 'G', 'PG', 'PG-13' ); CREATE TABLE film ( film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL, rating mpaa_rating DEFAULT 'G'::mpaa_rating ); I've tried the following: pg_insert($dbconn, "film", new array("rating" => "PG")); pg_insert($dbconn, "film", new array...
Hi, I want to assing the values of an object to an array. ex: object is of type samp table with columns col,col2,col3. array is also of type samp table. So array[i] := object; Does this work. ...