I have a script which generates queries in the following fashion (based on user input): SELECT * FROM articles WHERE (articles.skeywords_auto ilike '%pm2%') AND spubid IN ( SELECT people.spubid FROM people WHERE (people.slast ilike 'chow') GROUP BY people.spubid) LIMIT 1; The resulting data set: Array ( [0] => Ar...
Which one of the following ways would you use in declaring Primary Keys by Postgres? #1 CREATE TABLE user( user_id PRIMARY KEY, ... ) #2 CREATE TABLE user( user_id NOT NULL, ... CONSTRAINT user_pk PRIMARY KEY(user_id); ) ...
Can I define a primary key according to three attributes? I am using Visual Paradigm and Postgres. CREATE TABLE answers ( time SERIAL NOT NULL, "{Users}{userID}user_id" int4 NOT NULL, "{Users}{userID}question_id" int4 NOT NULL, reply varchar(255), PRIMARY KEY (time, "{Use...
How do you read the following line of code? The code is from SO by John Saunders. (Please add a link to it if you find it) SET search_path TO so,"$user", public; Context START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE; CREATE SCHEMA SO SET search_path TO so,"$user", public; /// here ... I read the line as "set...
I am working on a business app (asp.net). Right now I am using sql server. But I plan to support at least mysql and postgresql down the road. What are the issues that I should consider to avoid future headaches? Especially about datatypes (column types). E.g. I think BIT column is not supported on some dbs so I use tinyint? I mostly u...
When I try to initiate a new Slony cluster in the PgAdmin interface, the status-bar says "Slony-I creation scripts not available; only joining possible" .. OS : windows PostgreSQL version : 8.4 I fallowed some solutions like Xxid.sql, slony1_funcs.sql, slony1_base.sql file copy and rename as a Xxid.v84.sql, slony1_funcs.v84.sql, s...
I have a problem with using Bitwise operators in Postgres I get the following error message ERROR: argument of WHERE must be type boolean, not type integer My query looks as below SELECT DISTINCT number,name,contact,special FROM clients WHERE special & 2048; Any help will be appreciated ...
I am trying to do full text searching in PostgreSQL 8.3. It worked splendidly, so I added in synonym matching (e.g. 'bob' == 'robert') using a synonym dictionary. That works great too. But I've noticed that it apparently only allows a word to have one synonym. That is, 'al' cannot be 'albert' and 'allen'. Is this correct? Is there any ...
What are some Oracle gotchas for someone new to the platform, but not new to relational databases (MySQL, MS SQL Server, Postgres, etc.) in general. Two examples of the kind of things I'm looking for Many relational database products handle creating an auto_increment key for you. Oracle does not, you must manually create the sequence...
What does the INDEX-expression do? An example at the end: CREATE TABLE tags ( tag_id varchar(255) NOT NULL, "{Users}{userID}question_id" int4 NOT NULL, tag varchar(20), CONSTRAINT tag PRIMARY KEY (tag_id)); CREATE INDEX tags_tag ON tags (tag); ...
Hi. Can someone tell me how can I get MS Entity Framework working with PostgreSQL. How does Entity Framewok work with Mono? Could you please suggest other similar ORM tools which would run on Mono and what is your opinion about them? ...
Hi, I have python threaded application + Postgres. I am using Django's ORM to save to Postgres.. I have concurrent save calls. Occasionally 2 threads save with the same primary key which leads to an issue. Postgres log: ERROR: duplicate key value violates unique constraint "store_pkey" STATEMENT: INSERT INTO "store" ("store_id", "ad...
Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function? ...
should I call lo_unlink ? A delete didn't remove the object from pg_largeobject. ...
Hi, I am using postgresql-8.3-603.jdbc4.jar with jdk 1.6 in my application to do the db operations. I am getting the below exceptions at sometimes and doing restart helps to avoid this exceptions temporarily. org.postgresql.util.PSQLException: The column name sender_id was not found in this ResultSet. at org.postgresql.jdbc2.A...
I have a C++ application which is making use of PostgreSQL 8.3 on Windows. We use the libpq interface. We have a multi-threaded app where each thread opens a connection and keeps using without PQFinish it. We notice that for each query (especially the SELECT statements) postgres.exe memory consumption would go up. It goes up as high as...
Sometimes I run a Postgres query it takes 30 seconds. Then, I immediately run the same query and it takes 2 seconds. It appears that Postgres has some sort of caching. Can I somehow see what that cache is holding? Can I force all caches to be cleared for tuning purposes? Note: I'm basically looking for a postgres version of the fol...
Hi, (PostreSQL 8.2, running on WindowsXP) I have these lots of complexes queries that takes several seconds to run each. They are not "views" actually, but can be treated as so. I decided to persist the result records from those "views" into tables, that I call "aux" (auxiliary) tables. I can guarantee that there is no data change a...
Im fiddling with psycopg2 , and while there's a .commit() and .rollback() there's no .begin() or similar to start a transaction , or so it seems ? I'd expect to be able to do db.begin() # possible even set the isolation level here curs = db.cursor() cursor.execute('select etc... for update') ... cursor.execute('update ... etc.') db.com...
Is there a way to copy the structure of a table into a new table, without data, including all keys and constraints? ...