I have created a data type called id which consists of two text values: id(text, text) I now need to cast values to this data type before they are inserted into my table. How would I go about doing this? I created the type as follows: CREATE TYPE ID AS(id text, source text); ...
On development server I'd like to remove unused databases. To realize that I need to know if database is still used by someone or not. Is there a way to get last access or modification date of given database, schema or table? ...
Hello togehter, what are the performance aspects if you have something like this in your query: ... AND x.somfield IN ( 33620,262,394,450,673,674,675,2331,2370,2903,4191,4687,5153,6776,6898,6899,7127,7217,7225, 7227,7757,8830,8889,8999,9036,9284,9381,9382,9411,9412,9423,10088,10089,10304,10333,10515, 10527,10596,106...
I'm trying to get the following query to execute at a reasonable performance: UPDATE order_item_imprint SET item_new_id = oi.item_new_id FROM order_item oi INNER JOIN order_item_imprint oii ON oi.item_number = oii.item_id Currently, it doesn't complete within 8 days so we killed it. The query explaination is as f...
I have a table with user_ids that we've gathered from a streaming datasource of active accounts. Now I'm looking to go through and fill in the information about the user_ids that don't do much of anything. Is there a SQL (postgres if it matters) way to have a query return random numbers not present in the table? Eg something like this:...
My problem include the following, i want select from table my number But it is not select * from table1 where name = 1; i whont select in the whole table my number. postgresql (NPgsql.) ...
I have an upcoming project in which reports will play a large role. The reports will be predefined in nature, but may return significant amounts of data. For the database portion of the project we've settled on PostgreSQL, but we're still debating about what stack of software to use for the frontend. The reports are a significant en...
are there any Free tools to generate tables diagrams with postgresql? ...
I am writing my queries in 'standard' / 'generic' SQL so that it runs on any database (MySQL, Postgre etc). I'd like to use DATE_ADD() of MySQL. In Postgre, it would be done with something like '2001-01-01'::timestamp + '1 year'::interval;. Is there a more 'general' way of writing it so that it runs on both MySQL and Postgre (or other...
I am desperately trying to backup using pgAdmin III my database and I receive an error: geometry contains non-closed rings. How can I get around this?? ...
Hi, I'm executing a recursive query in Postgres to retrieve a list of emails and their threaded children as follows: WITH RECURSIVE cte (id, title, path, parent_id, depth) AS ( SELECT id, title, array[id] AS path, parent_id, 1 AS depth FROM emails WHERE parent_id IS NULL UNION A...
Hello I have table in postgressql with primare key (pk_user). If i insert wrong parametrs. postgres call exception:( ERROR: duplicate key value violates unique constraint "pk_user" ) this is ok, but i like catch this error and transform it to user interface ( this username is used) my php script: $sql="INSERT INTO user (....) VALUES...
I happen to have tables with timestamp without time zone column in my database. Now I need to compare them with respect to time zone. I can do it like: select my_timestamp::timestamp with time zone at time zone 'EST5EDT' from my_table where my_timestamp >= '2010-07-01'::timestamp at time zone 'EST5EDT' and my_timestamp < '20...
If I needed to do a left join between 2 tables (in order to run some kind of analysis between them), but both datasets are too large for this to be executed in a single query, what's the best practice to accomplish this? I saw FETCH in the documentation but wasn't sure if this is conventionally used to loop over entire datasets. Since I...
Hello, Is there any way to convert a 6-digit julian date to a normal timestamp in Postgres? Date is created using python's datetime.toordinal(). For example, >>> date = datetime.date(2010, 7, 20) >>> datetime.toordinal(date) 733973 What I need is to convert '733973' back to '2010-07-20' in Postgres. Thanks! ...
Is it possible to see the last entry into a table that was triggered on? For instance, I have a table called "users" and a table called "log". I want to write to "log" the userid that was last written to "users" so each time a user is created, it writes the userid to a log. I create a trigger on users with a function like the one belo...
Let's say I have a table with two columns, id and hash: id | hash ------------------ 1 | bb 2 | aa 3 | aa 4 | bb I need to order them by id (descending), yet group all the rows that have the same value. An algorithm that did this would be for example: Gather into disjunct subsets the table so that the column...
I have to grab i18n text from a database. The default language is English, it has text for everything. But the non-English languages doesn't necessarily have all the desired translations. If a non-English translation for a certain entity/key isn't available in the DB, then I'd like to have it to return the English text instead. So, Engli...
We are using Spring 2.0, Hibernate 3.2, and PostgreSQL 8.4.2 with pgBouncer (we've also tried pgPool-II). We are occasionally getting "IDLE in transaction" queries which sit around and hang up our database ... they usually show up sometime in between 2:00am and 6:45am. I've been running into a lot of conflicting information online as t...
(I've read a lot about 64-bit versus 32-bit OS/Apps, but this question is specifically in regards to databases.) I'm trying to understand the pros and cons of 32-bit versus 64-bit databases, and namely, under what conditions that it starts to make sense to use 64-bit installations. The database systems that I am interested in are: SQL ...