postgresql

Disabling the PostgreSQL 8.4 tsvector parser's `file` token type

I have some documents that contain sequences such as radio/tested that I would like to return hits in queries like select * from doc where to_tsvector('english',body) @@ to_tsvector('english','radio') Unfortunately, the default parser takes radio/tested as a file token (despite being in a Windows environment), so it doesn't match the ...

Freely available example datasets of hierarchical information, and realistic names

I'm about to write some example applications and accompanying documents comparing ways of accessing information stored in relational databases. To demonstrate real-life requirements, I need to include a realistic dataset of hundreds of thousands of facts. Is anyone aware of publicly available, free datasets of that magnitude, of dataset...

How to get the key fields for a table in plpgsql function?

I need to make a function that would be triggered after every UPDATE and INSERT operation and would check the key fields of the table that the operation is performed on vs some conditions. The function (and the trigger) needs to be an universal one, it shouldn't have the table name / fields names hardcoded. I got stuck on the part whe...

sql split string by space into table in postgresql

I looking for a function like regexp_split_to_table, but our db is version 8.2.9, so it doesn't have it. I'm really only splitting on a space, so a string like how now brown cow would return +------+ |Column| +------+ |how | |now | |brown | |cow | +------+ is there a simple function that can handle this, or something I ...

Difference between ALTER ROLE WITH CREATEDB and GRANT CREATE ON TABLESPACE

Coming from MySQL and not knowing about ROLEs I absentmindedly tried this GRANT CREATE ON TABLESPACE pg_default TO username; It didn't have the desired effect. The command that I was looking for was: ALTER ROLE username WITH CREATEDB; But what's the difference precisely? Does giving someone the CREATEDB role implicitly give them CR...

Can ASP.NET MVCF be used with a Postgresql database?

I was wondering if ASP.NET MVCF be used with a Postgresql database? ...

PostgreSQL specific SQL keywords

What are specific features from Postgres that are not available in MySQL? Are there some queries that you wouldn't be able to do as easily? Or are the differences mostly in how you store your data? ...

PostgreSQL: Selecting N result in group by query

Hi, I have following SQL query that selects some results from my table: select avg(c3), count(c3), std from ssims where obraz = 'lena' group by std order by std But I have various number of tests performed for different values of std, so it returns me something like that: 0.906176136363636;44;5 0.881669302325582;43;10 0.855873409...

Manually connect to PostgreSQL with Ruby

Connecting to postgres with rails was no big deal. rails -d postgresql app_name, setup the database.yml and voila. I cannot, however, use postgres with just a little test script I've tried installing the gems postgres, dbi & dbd-pg, pg, and ruby-pg, but with pg/ruby-pg and postgres it fails at the require for require 'postgres' or req...

Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql

I have a number of tables that use the Postgres "Partitioning" feature. I want to define a common BEFORE INSERT OF ROW trigger on each table that will 1) dynamically create the partition should the insert occur against the parent table and 2) re-execute the insert against the partition. Something like: CREATE OR REPLACE FUNCTION parti...

Insert Data Into Tables Linked by Foreign Key

Hello all, I am using PostgreSQL. Customer ================== Customer_ID | Name Order ============================== Order_ID | Customer_ID | Price To insert an order, here is what I need to do usually, For example, "John" place "1.34" priced order. (1) Get Customer_ID from Customer table, where name is "John" (2) If there are no ...

Invoke PostgreSQL Stored Procedure using C

I am referring to http://www.postgresql.org/docs/8.1/static/libpq.html I try to find an example of C/C++ to call PostgreSQL stored procedure. However, I cannot find one. Can anyone point me the right direction? ...

Postgres/PHP - What is the standard way to retrieve the ID of an inserted row?

I have searched on Google and read a couple of articles on how different people approach this problem, but I was wondering what the standard way of solving it is and also, which one will work best for my situation. I have an AJAX page that creates a new question and I need to know how to retrieve the ID from the insert query within the ...

PostgreSQL notice processing

Hi, I'm working on a C application that is suppose to talk to PostgreSQL. Right now I need to handle notices and warnings sent by the server but I'm at a loss on how to make it work. The (very unclear) documentation says we should use PQsetNoticeReceiver to set a method as the receiver of notifications, as the default receiver just for...

PostgreSQL - combining two tables

Hello! I have a PostgreSQL database with two tables named "user" and "group". They have different columns, but I want to join them. user: id, firstname, lastname, email, registrationdate group: id, name, desc, createdate I can do two separate queries: select * from "user" where ... order by "registrationdate" asc; select * from "grou...

Clone a row with a primary key in SQL?

Hello, I am attempting to make some cross-DB(SQL Server and PostgreSQL) compatible SQL. What I am needing to do is clone a row. It is preferable to do it locally on the SQL server without having to pull the entire row down to our client and reinsert it. Also, we prefer to not have to create a new SQL query from scratch putting in column ...

SELECT id_field WHERE max(date_field) < 'some date'

I'm sure this question is obvious, but I've been fiddling with an increasingly complex set of subqueries for the last hour or so and getting nowhere. I need to select a bunch of ID's from a table where the last date for that record is before a given date. I was trying: SELECT id_field WHERE max(date_field) < 'some date' But getting '...

Shall I use PostgreSQL Array Type in The Following Case

I am using PostgreSQL. I realize there is Array data type for PostgreSQL. http://www.postgresql.org/docs/8.1/interactive/arrays.html Currently, I need to use database to store measurement result of a semiconductor factory. They are producing semicondutor units. Every semicondutor units can have variable number of measurement paramete...

Postgresql - how to disallow use of spaces in some string fields

I want to disallow the use of spaces in some text/varchar fields. Even more, it would be best to have only a set of characters that are allowed to use there, like: [a-zA-Z0-9_\-] And I want to make it as a rule to all VARCHAR fields that are members of primary key in their tables. This should be done on the database level and could...

Best way to use a PostgreSQL database as a simple key value store.

I am being required to use a postgreSQL database and it will replace my current use of berkeleyDB. Although; I realize this is not an ideal situation, it is beyond my control. So the question is... If you were required to make postgreSQL into a key value store how would you go about doing this, while making it as efficient as possible?...