postgresql

PostgreSql: different query plans with identical columns

I have a table with 2 foreign keys, lets call them fk1 and fk2. Both have identical types, and identical indices. But when I "explain" a simple select query, I get completely different query plans. For fk1: explain select * from mytable where fk1 = 1; Result Index Scan using fk1_idx on mytable (cost=0.00..9.32 rows=2 width=4) Inde...

How to declare local variables in postgresql?

There is an almost identical, but not really answered question here. I am migrating an application from MS SQL Server to PostgreSQL. In many places in code I use local variables so I would like to go for the change that requires less work, so could you please tell me which is the best way to translate the following code? -- MS SQL Synt...

How to Synchronize Postgres Database table

Hi Could any one of you please let me know how to synchronize table of any function in postgres Database? Thanks in Advance. ...

How do you handle charitable contributions?

Basically, I'm wondering if any of you have put together a for-profit e-commerce site that has had a charity donation option. We are trying to keep it a separate entity from our inventory and the rest of our books. We are looking for a tool to easily integrate a $1 or custom amount donation into the checkout. We run a single-page AJ...

Statistics on Query Time (PostgreSQL)

I have a table with a billion rows and I would like to determine the average time and standard deviation of time for several queries of the form: select * from mytable where col1 = '36e2ae77-43fa-4efa-aece-cd7b8b669043'; select * from mytable where col1 = '4b58c002-bea4-42c9-8f31-06a499cabc51'; select * from mytable where col1 = 'b97242...

Rails SQL query with % Wildcards works in SQLite but not PostgreSQL?

I have a query I'm using for a search with :conditions like this: :conditions => ['family_name LIKE ? OR given_name LIKE ?', "%#{params[:search]}%", "%#{params[:search]}%"] The query works fine locally on SQLite, but when I push to Heroku on PostgreSQL, only the first % works for both family_name and given_name. In other words, it wil...

How to use database functions in Kohana 3 ORM select query

I'm using Postgres with Kohana 3's ORM module and would like to run a SELECT using a postgres function to convert values already in the database to lower case before doing the comparison. In SQL I would write: select * from accounts where lower(email) = '[email protected]'; In Kohana I would like to write something like this: $user = O...

Algorithm improvement on a simple looking postgresql query.

High-level: Can I do this order by, group by based on sum any faster? (PG 8.4, fwiw., on a non-tiny table .... think O(millions of rows) ) Suppose I had a table like this: Table "public.summary" Column | Type | Modifiers -------------+-------------------+-----...

How to interpret this execution plan (IN vs EXISTS)?

It looks like from the execution plan the IN version is faster than EXISTS version I'm thinking that EXISTS query is faster, for it eagerly checks the conditions. The IN query though it looks intuitive, I feel that it seems it resolves the final conditions' result very late; that is, from inside out, I perceived that IN are slower beca...

Large Objects may not be used in auto-commit mode

Hello, I have a Spring application which uses Hibernate on a PostgreSQL database. I'm trying to store files in a table of the database. It seems it stores the row with the file (I just use persist method on EntityManager), but when the object is loaded from the database I get the following exception: org.postgresql.util.PSQLException: ...

Postgres Editable Union View

I have a table which stores 'links' between 2 people. In order prevent further complications down the road on an application I am building, I want to create an editable view, that shows the link records and an inverse copy of the link records. Meaning if Joe is linked to Sally, then the view should show Joe linked to Sally and Sally li...

postgresql equivalent of SQL Server sysprocesses loginname and program_name

I am migrating to Postgresql, but I am facing a problem. A "trick" I am using with SQL Server is to login always using a single user (tipically sa) and I write the program_name in the database connection to check the number of currently logged users in the application. Everytime I do a db connection for UserX I set the program_name in t...

Django ORM misreading PostgreSQL sequences?

Background: Running a PostgreSQL database for a Django app (Django 1.1.1, Python2.4, psycopg2 and Postgres 8.1) I've restored the database from a SQL dump several times. Each time I do that and then try to add a new row, either shell, admin, or site front end, I get this error: IntegrityError: duplicate key violates unique constraint "a...

Link libpq.a(Postgres lib) with Xcode project, dependency errors

Hi! I am trying to link libpq.a to a c++ library being build on Xcode, to use Postgres functions's so I can connect and retrieve data. My problem is that when I build the project, it complains about references from the libpq.a, like in the image below: few minutes ago, it was much more errors... it was complaning about openssl lib ...

PostGIS intersection troubleshooting

I just started using PostGIS & Postgresql and everything is running smoothly for the most part. When I try to find which MULTIPOLYGON s a POINT lies in I'm getting stuck. I have two separate points that I am certain lie inside one and only one shape that is of MULTIPOLYGON data type in my database. They are not the same points and the...

sqlalchemy's create_all doesn't create sequences automatically

I am using SQLAlchemy 0.4.8 with Postgres in order to manage my datastore. Until now, it's been fairly easy to automatically deploy my database: I was using metadata.create_all(bind=engine) and everything worked just fine. But now I am trying to create a sequence that it's not being used by any table, so create_all() doesn't create it, e...

Schemas and Indexes & Primary Keys : Differences in lookup performance ?

Hi. I have a database (running on postgres, precisely) , with the following structure : user1 (schema) | - cars (table) - airplanes (table, again) ... user2 | - cars - airplanes ... It's clearly not structurized the way classic relational databes should be, but it "just works" as it is now. As you can see, schemas are like prim...

Why the most natural query(i.e. using INNER JOIN (instead of LEFT JOIN)) is very slow.

This query takes too long... explain analyze select c.company_rec_id, c.the_company_code , c.company from tlist t -- it is questionable why this query become fast when using left join, the most natural query is inner join... join mlist m using(mlist_rec_id) join parcel_application ord_app using(parcel_application_rec_id) join par...

how create function with argumet as subselect?

Hello I like create function for select and changed me data CREATE OR REPLACE FUNCTION PublicatedTask( argument ) RETURNS SETOF task AS $$DECLARE f task%ROWTYPE; BEGIN FOR f IN SELECT * FROM Task where layer IN $1 and publicationin<>0 ORDER BY id LOOP if (f.publicationIN = 1) then f.description=''; end if; ...

alter table to create columns for other table

Hi, can anyone help me with creating column for table A that are from table B ? let's say i have table B: column name:WORDS row 1:ONE row 2:TWO row 3:THREE and want to have table A: column name: ONE | TWO | THREE I need this to be created and not some VIEW thanks ...