I've a field that is INTEGER NOT NULL DEFAULT 0 and I need to change that to bool. This is what I am using: ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN 0 THEN FALSE ELSE TRUE END; But I am getting: ERROR: argument of CASE/WHEN must be type boolean, not type integer ********** Error ***...
Hi, I think this is a pretty common problem. I've got a table user(id INT ...) and a table photo(id BIGINT, owner INT). owner is a reference on user(id). I'd like to add a constraint to the table photo that would prevent more than let's say 10 photos to enter the database for each users. What's the best way of writing this? Thx! ...
I'm trying to find open source applications using PostgreSQL that are written in C/C++ so I can study them. A few open source projects using PostgreSQL are Evergreen ILS, SpamAssassin, and pgpool. However, Evergreen and SpamAssassin are written in Perl, and pgpool (written in C) is a replication tool, not a typical application. Moreov...
On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 gigs in size. The challenge is that the data for our business entities are scattered across many tables. We want to create some sort of fi...
Hi, I have a problem where I have to add a unique constraint to an existing table. This is fine except that the table has millions of rows already, and many of the rows violate the unique constraint I need to add. What is the fastest approach to removing the offending rows? I have an sql statement which finds the duplicates and deletes...
I'm having trouble building an Entity Framework LINQ query whose select clause contains method calls to non-EF objects. The code below is part of an app used to transform data from one DBMS into a different schema on another DBMS. In the code below, Role is my custom class unrelated to the DBMS, and the other classes are all generated ...
How do I load data from an Excel sheet into my Django application? I'm using database PosgreSQL as the database. I want to do this programmatically. A client wants to load two different lists onto the website weekly and they don't want to do it in the admin section, they just want the lists loaded from an Excel sheet. Please help becaus...
I have a (potential) client who has had several a several Access applications previously developed which they want to upgrade to use a database back end. Unfortunately because of costs they don't want to use SQL Server, and SQL Express is likely inappropriate due to licensing restrictions. They have mentioned using MySQL and an option ...
I'm using Django (specifically django-admin) to be the admin panel for a site that uses PHP for client-facing features. I've been messing around making the admin look exactly the way I want and so far so good. However, I've run into a few problems that I need solving. Here's the models that I'm working with: class Permissions(models.Mo...
I have a table called items. It contains a column tz with timezone identifiers (like America/New_York, Europe/London, etc). I want to select all the items where the current time is 8AM +/- 5 minutes. So if I run the query at 8AM EST it will return rows where tz = 'America/New_York'. If I run the query at 9AM EST it will return rows whe...
I am using AR's sum method for a query and seeing this error when using PostgreSQL: PGError: ERROR: function sum(character varying) does not exist LINE 1: SELECT sum("assets".asset_file_size) AS sum_asset_file_size ... HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT sum("as...
How can i run multiple sql files from one main sql files in postgres. For example in oracle Inside Main.sql i can specify n number of @Child.sql , and then i can run Main.sql to run all child.sql 's . How can i do this in the postgres. Thanks! Pratik ...
I'm trying to get my first ever trigger and function to work, but how I throw exceptions and return data right way? PostgreSQL 8.4.1 CREATE TABLE "SHIFTS" ( id integer NOT NULL, -- SERIAL added timestamp without time zone DEFAULT now() NOT NULL, starts timestamp without time zone NOT NULL, ends timestamp without time zo...
Thank you for Denis who solves the first bug! How can you have two Postgres queries in one Python method? Example where the 2nd query is not run def comp_func(pgmasi): pgmasi.query("""CREATE TABLE courses ( course_id SERIAL PRIMARY KEY)""") pgmasi.query("""CREATE TABLE files ( # not executed for some u...
In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL? ...
I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by: ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function Could someone could tell me what I'm doing wrong? MySQL which works 100%...
Dear all, I have several statements which access very large Postgresql tables i.e. with: SELECT a.id FROM a WHERE a.id IN ( SELECT b.id FROM b ); SELECT a.id FROM a WHERE a.id NOT IN ( SELECT b.id FROM b ); Some of them even access even more tables in that way. What is the best approach to increase the performence, should I switch i....
I'm using iBatis/Java and Postgres 8.3. When I do an insert in ibatis i need the id returned. I use the following table for describing my question: CREATE TABLE sometable ( id serial NOT NULL, somefield VARCHAR(10) ); The Sequence sometable_id_seq gets autogenerated by running the create statement. At the moment i use the following sq...
I'm using iBatis/Java and Postgres 8.3. When I do an insert in ibatis i need the id returned. I use the following table for describing my question: CREATE TABLE sometable ( id serial NOT NULL, somefield VARCHAR(10) ); The Sequence sometable_id_seq gets autogenerated by running the create statement. At the moment i use the following sq...
Thank you for bobince in solving the first bugs! How can you use *pg.escape_bytea* or *pg.escape_string* in the following? #1 With both pg.escape_string and pg.escape_bytea con1.query( "INSERT INTO files (file, file_name) VALUES ('%s', '%s')" % (pg.escape_bytea(pg.espace_string(f.read())), pg.espace_string(...