I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written to or read during the update. I want to know if there is an easy way in the psql console to make these types of operations...
I am thinking of working on a Rails application that uses PostgreSQL. I have some questions before I am comfortable using Rails: Is PostgreSQL support in Rails less superior than, say, MySQL. Would it feel any different if using PostgreSQL? Are there any cases where using PostgreSQL fail to work? Thanks. ...
Hello, I've got m2m relationship like this: #main table CREATE TABLE products_product ( id integer NOT NULL, company_id integer, user_id integer, type_id integer NOT NULL, name character varying(100) NOT NULL, description character varying(200) NOT NULL, tags character varying(255) NOT NULL, image charac...
Hi I'm trying to export a PostgreSQL table with headings to a CSV file via commandline, however I get it to export to CSV file but without headings. I need those headings as well. My code looks as follows COPY products_273 to '/tmp/products_199.csv' delimiters','; Any help would highly be appreciated ...
I have a postgres table like this: CREATE SEQUENCE seq; CREATE TABLE tbl (id INTEGER DEFAULT VALUE nextval('seq'), data VARCHAR); When I insert into the table: INSERT INTO tbl (data) VALUES ('something'); How can I get back the value of the id field for the record I just created? (Note, I may have got some of the SQL syntax a bit ...
Hi, I'm working on a application which uses spring and hibernate. We are using postgresql as the database. When I try to insert a record into a table which has a OID column it is throwing the following error. org.hibernate.exception.GenericJDBCException: could not insert: [com.greytip.cougar.model.misc.MailAttachment] at org.hibernate...
I need to take the first N rows for each group, ordered by custom column. Given the following table: db=# SELECT * FROM xxx; id | section_id | name ----+------------+------ 1 | 1 | A 2 | 1 | B 3 | 1 | C 4 | 1 | D 5 | 2 | E 6 | 2 | F 7 | 3 | G 8 | 2...
Consider following schema in postgres database. CREATE TABLE employee ( id_employee serial NOT NULL PrimarKey, tx_email_address text NOT NULL Unique, tx_passwd character varying(256) ) I have a java class which does following conn.setAutoComit(false); ResultSet rs = stmt.("select * from employee where tx_email_address = 'test1...
I'm trying to run a PHP script which has pg_connect($connection_string) and it just crashes my PHP script. I'm running it off of xampp on my computer, here are some facts: If I put exit("test"); immediately above the pg_connect statement, it successfully displays the word "test" and terminates the script, so I know that my installation...
I am using a trigger in PostgreSQL 8.2 to audit changes to a table: CREATE OR REPLACE FUNCTION update_issue_history() RETURNS trigger as $trig$ BEGIN INSERT INTO issue_history (username, issueid) VALUES ('fixed-username', OLD.issueid); RETURN NULL; END; $trig$ LANGUAGE plpgsql; CREATE TRIGGER update_issue...
I'm having a really strange and frustrating issue. On one page, an existing and often used one, I have this query: SELECT COUNT(*) AS count FROM uvusers WHERE vdate IS NULL It works exactly as expected and always has. On a new page I'm working on, I have this query: SELECT COUNT(*) AS count FROM uvusers WHERE vdate IS NULL This gen...
How do I best design a database where I have one table of players (with primary key player_id) which I want to pair into teams of two so that the database can enforce the constraint that each team consists of exactly two players and each player is in at most one team? I can think of two solutions, but both I'm not too happy about. One ...
I am building an app in which I need to delete a table row at a certain line number. I don't want to use or rely on an id, because if I delete a row, the following rows won't "shift down" -- line 8 today could be line 7 tomorrow, but line 8 will still have an id of 8. How can I write a postgres SQL that essentially does this: DELETE F...
Is there a unpivot equivalent function in PostgreSQL? ...
Hy guys, i have a postgresql 8.3 server with many database. Actually, im planning to backup those db with a script that will store all the backup in a folder with the same name of the db, for example: /mypath/backup/my_database1/ /mypath/backup/my_database2/ /mypath/backup/foo_database/ Every day i make 1 dump each 2 hours, overwriti...
I am new to Subsonic and would like to try Subsonic 3 with postgreSQL. Are there any templates available for postgreSQL? The download includes templates for mySQL and SQL Server only. ...
I have a query like: SELECT EXTRACT(WEEK FROM j.updated_at) as "week", count(j.id) FROM jobs WHERE EXTRACT(YEAR FROM j.updated_at)=2009 GROUP BY EXTRACT(WEEK FROM j.updated_at) ORDER BY week Which works fine, but I only want to show the last say 12 weeks, LIMIT 12 works, but only gives me the first twelve and I need th...
I installed a default installation of PostgreSQL 8.4 on Windows 2003 Server, using the one-click installer provided. Running psql -l for the first time, I noticed there are three databases installed by default: postgres, template0, and template1. Being security-minded, my initial reaction is to delete or change default configurations. H...
I'm using SELECT current_query FROM pg_stat_activity; to see the currently executing queries, but I noticed that the query is truncated. Is there any workaround or any other way to see the currently executing queries? ...
I have a query like this: select foo.*, count(bar.id) from foo inner join bar on foo.id = bar.foo_id group by foo.id This worked great with SQLite and MySQL. Postgres however, complains about me not including all columns of foo in the group by clause. Why is this? Isn't it enough that foo.id is unique? ...