Hi there, please have a look at the following table: name | x | y ---------+-----+------ foo | 3 | 5 bar | 45 | 99 foobar | 88 | barfoo | 0 | 45 I want to add a constraint CHECK ( y > x ), but this obviously will fail due it is violated by the row 'foobar'. How do I create a constraint that says: check (...
I have an typical CUSTOMER/ORDERS set of tables and I want to display the total percentage of sales a particular customer is responsible for. I can get the total number of orders in the system like so: SELECT COUNT(order_id) FROM orders And I can get the the total number of orders made by the customer like so: SELECT COUNT(order_id) ...
I'm not really looking for a basic SQL guide but just something specific to PostgreSQL. And I do run my own servers so getting the latest version ( 8.2 I believe? ) is no issue. ...
PostgreSQL's dblink module allows for SQL statements to be written that execute other SQL statements against a remote PostgreSQL database. Is there an equivalent to PostgreSQL's dblink for MySQL? ...
The documentation for set local states: "Note that SET LOCAL will appear to have no effect if it is executed outside a BEGIN block, since the transaction will end immediately." If I'm using SET LOCAL in the context of read only transactions do I need to indicate the end of the transaction with a COMMIT statement? Is there any differen...
I need to set up the following env variables such that I can a database program which use PostgreSQL export PGDATA="/home/masi/postgres/var" export PGPORT="12428" I know that the problem may be solved by adding the files to .zshrc. However, I am not sure whether it is the right way to go. How can you add env variables? ...
My Django site recently started throwing errors from my caching code and I can't figure out why... I call: from django.core.cache import cache cache.set('blogentry', some_value) And the error thrown by Django is: TransactionManagementError: This code isn't under transaction management But looking at the PostgreSQL database logs, i...
Hi guys, i create my prepared statement as: pg_prepare('stm_name', 'SELECT ...'); Today, i had a problem (calling twice a function for mistake) when declaring a prepared statement with the same name twice: Warning: pg_prepare() [function.pg-prepare]: Query failed: ERROR: prepared statement "insert_av" already exists in xxx on line 2...
Hello, I have the following table: tickername | tickerbbname | tickertype ------------+---------------+------------ USDZAR | USDZAR Curncy | C EURCZK | EURCZK Curncy | C EURPLN | EURPLN Curncy | C USDBRL | USDBRL Curncy | C USDTRY | USDTRY Curncy | C EURHUF | EURHUF Curncy | C USDRUB | USDRUB Curncy...
Hi, I'm struggling with creating a SQL query involving aggregates using PostgreSQL. Consider the following tables: CREATE TABLE thing ( id INT NOT NULL PRIMARY KEY, price NUMERIC(10,2) NOT NULL, description VARCHAR(255) NOT NULL, url VARCHAR(255) NOT NULL, location_id INT NOT NULL REFERENCES location(id) ) CREATE TABLE locat...
The problem: we have a very complex search query. If its result yields too few rows we expand the result by UNIONing the query with a less strict version of the same query. We are discussing wether a different approach would be faster and/or better in quality. Instead of UNIONing we would create a custom sql function which would return ...
In an existing application, I have a table which has no primary key, which (rarely) has duplicate rows in it. For integration with another system, I need to add a column to the table that makes these duplicate rows unique. The table is essentially: +------+---------+--------+ | txn# | detail# | amount | +------+---------+--------+ I ...
Improvements done nvarchar(5000) -> nvarchar(4000) BUT no nvarchar in PostgreSQL => TEXT memory limits to some variables the syntax slightly changed to more readable dashes to underscores Magnus' improvements I am following my plan for my first database project. I would like to know any weaknesses in the queries and in the relationa...
How does the work_mem option in Postgres work? Here's the description from http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html: Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value defaults to one megabyte (1MB). Note that for a com...
Hi guys, i have this simple plpgsql function: CREATE FUNCTION "update_times" () RETURNS trigger AS ' BEGIN NEW.update_time = NOW(); RETURN NEW; END;' LANGUAGE "plpgsql"; --The trigger: CREATE TRIGGER test_update_time BEFORE UPDATE ON contact FOR EACH ROW EXECUTE PROCEDURE update_times(); and this works well, b...
I want to provide the table name for a query as command parameters, like so: public class Foo { private const String myTableName = "mytable"; public void Bar() { NpgsqlCommand command = new NpgsqlCommand("SELECT * from :tableName", connection); command.Parameters.Add(new NpgsqlParameter("tableName", DbType.S...
John uses CHARACTER VARYING in the places where I use VARCHAR. I am a beginner, while he is an expert. This suggests me that there is something which I do not know. What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL? ...
I have a product table that contains thousands of products. Some products are available in different colors. But when somebody searches for example 'mp3 player' i dont want to show him every color, instead just the player with the best selling color. Her is the table layout (simplified): ID | PRODUCT_NAME | COLOR | SALE_COUNT ====...
I am doing my first database project. I would like to know how you can have false as the default value for the following SQL -query ... MODERATOR_REMOVAL boolean NOT NULL ... Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_ID) PRIMARY KEY ...
I read that there are datatypes which do encryption so passwords are secured in your database. I use at the moment varchar to store passwords. I have had the idea that I should somehow apply a SHA-512 function to the password and put that data somewhere such that the plain text password is removed. However, the datatype in Perl suggest...