Hello, If I create a table mytable with column data as varchar(2) and then insert something like '123' into the column, postgres will give me an error for Value too long for type. How can I have Postgres ignore this and truncate the value if necessary? Also, I do not (when creating the query) know the actual size of the data column in...
I have web pages that take 10 - 20 database queries in order to get all the required data. Normally after a query is sent out, the Django thread/process is blocked waiting for the results to come back, then it'd resume execution until it reaches the next query. Is there's any way to issue all queries asynchronously so that they can be ...
I am facing problem in retrieving long value from PostgreSQL I use the following SQL command : SELECT *, (extract(epoch FROM start_timestamp) * 1000) FROM lot WHERE EXTRACT(EPOCH FROM lot.start_timestamp) * 1000 >=1265299200000 AND EXTRACT(EPOCH FROM lot.start_timestamp) * 1000 <=1265990399999 ORDER BY start_timestamp DESC limit 9 o...
I have a mobile application sync process. The transaction does a lot of modification on the database. Since this is done on mobile I need to issue a VACUUM to compact the database. I am wondering when should I issue a VACUUM in the transaction, as final statement or after the transaction? I am currently looking for SQLite, but if i...
Hi, I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The documentation states that; On successful completion, a DELETE command returns a command tag of the form DELETE count The count is the number of rows deleted. If count is 0, no rows matched the condition (this is no...
in our database schema, we like to use delete flags. When a record is deleted, we then update that field, rather than run a delete statement. The rest of our queries then check for the delete flag when returning data. Here is the problem: The delete flag is a date, with a default value of NULL. This is convenient because when a record ...
To automatically add a column in a second table to tie it to the first table via a unique index, I have a rule such as follows: CREATE OR REPLACE RULE auto_insert AS ON INSERT TO user DO ALSO INSERT INTO lastlogin (id) VALUES (NEW.userid); This works fine if user.userid is an integer. However, if it is a sequence (e.g., type serial or...
With Python's DB API spec you can pass an argument of parameters to the execute() method. Part of my statement is a WHERE IN clause and I've been using a tuple to populate the IN. For example: params = ((3, 2, 1), ) stmt = "SELECT * FROM table WHERE id IN %s" db.execute(stmt, params) But when I run into a situation where the parameter...
I'm trying to use the similarity function in Postgres to do some fuzzy text matching, however whenever I try to use it I get the error: function similarity(character varying, unknown) does not exist If I add explicit casts to text I get the error: function similarity(text, text) does not exist My query is: SELECT (similarity("tabl...
Using Greenplum database version 3.2.3 on Solaris. Step 1. Create a table. CREATE TABLE ivdb.OPTION_PRICE ( SecurityID integer NOT NULL, Date timestamp NOT NULL, Root char(5) NOT NULL, Suffix char(2) NOT NULL, Strike integer NOT NULL, Expiration timestamp NOT NULL, CallPut char(1), BestBid real NOT NULL...
This question has a little history http://stackoverflow.com/questions/2247892/is-there-a-way-to-make-a-query-respect-the-order-of-the-inputted-parameters I'm new to building "specialized" queries, so I assumed that if I supply an IN clause as part of a SELECT query, it'll return results in the same order. Unfortunately that's not the c...
Hi I try to select all records of a table (Postgres DB) with the following sql: SELECT * FORM 'tablename' WHERE 'myTimestampRow' >= now() There's allways an error message, telling me that there's an 'invalid input syntax for type timestamp with time zone: "myTimestampRow"'. What's wrong with the above query? ...
I have a project which I have used Active Record and which I'd like to add some new features using MongoDB. Rather than re-invent the wheel and re-write my entire site, how can I integrate 2 models together, one which use MongoMapper and the other ActiveRecord (postgres). I've found that others have done it successfully, but no example...
I have to keep track of revisions of records in a table. What I've done is create a second table that inherits from the first and adds a revision counter. CREATE TABLE A ( id SERIAL, foo TEXT, PRIMARY KEY (id)); CREATE TABLE B ( revision INTEGER NOT NULL) INHERITS (A); Then I created a trigger that would update table B everytime A i...
Hi, I have a posgreSQL database and I am using pgAdmin III to work with it.I created a sequence called primaryKeySequence. Now I want to use this sequence as the default value for a primary key field in a table. I tried to insert nextval('primaryKeySequence'); into the default value textfield in pgAdmin. When I click the 'OK'-butt...
Hi, I am using PostgreSQL 8.4. I really like the new unnest() and array_agg() features; it is about time they realize the dynamic processing potential of their Arrays! Anyway, I am working on web server back ends that uses long Arrays a lot. Their will be two successive processes which will each occur on a different physical machine. E...
I have an Attache 7 GL system, which runs its own proprietary ODBC connector to its database. I want to connect to these tables in Postgres via odbc and get all the data into the Postgres database. How can I connect to an ODBC database direct from Postgres and move the tables over into Postgres? Thanks in Advance. ...
(Postgres 8.3) I'm working with a DB table X 100+ columns wide (which I can't change sadly) many of which get updated constantly and very frequently by normal business process. I have a requirement to update table Y based on updates to a particular column foo in X updated by unusual business process. However, because of the very high n...
I have this SQL statement: SELECT ABX.ABX_APO_NUMBER, COUNT(A1.PROCESS_MODE) AS NUM_PLANNING, COUNT(A2.PROCESS_MODE) AS NUM_SETUP, COUNT(A3.PROCESS_MODE) AS NUM_OUTPUT FROM ABX, USER_INSTANCE U, ACTIVE_PROCESS A1, ACTIVE_PROCESS A2, ACTIVE_PROCESS A3 WHERE U.ABX_APO_NUMBER (+) = ABX.ABX_APO_NUMBER AND A...
I have two select statements that I am trying to port from Oracle to Postgres: 1) (Note: this is a subselect part of a bigger select) SELECT 'Y' FROM CRAFT MA, CONFIG MAC, ARMS SM WHERE MCI.MS_INVENTORY_NUMBER = SM.MS_INVENTORY_NUMBER (+) AND MCI.AB_BASE_ID = MA.AB_BASE_ID_LAUNCH AND SM.ACT_AC_TYPE = MAC.ACT_AC_TYPE AND SM.MAC_ID = M...