postgresql

Mono ORM that works with PostgreSQL?

I've been trying to find a decent ORM that works with both Mono and PosgreSQL. Does anyone know of such an ORM? ...

PostgreSQL aggregate views

Are views in PostgreSQL completely recalculated every time one of underneath tables are changed? More precisely, if I have aggregate view over table votes, will complete recalculation of a view be needed, when any changes are made in votes table? If I expect often changes of votes table, what are alternatives to aggregate view? I need ...

Write raw SQL for PostGIS

I'm trying to input some data into a PostgreSQL 8.4 database with a PostGIS template. I'm unable to UPDATE polygons: > UPDATE my_table SET coords = POINT(1, 1) UPDATE 0 1 > UPDATE my_table SET box = POLYGON(((1, 1), (2, 3), (3, 3), (1, 1))) ERROR: function polygon(record) does not exist > UPDATE my_table SET box = POLYGON((1, 1), (2,...

DbProviderFactory with Npgsql?

I have a project which I'm trying to port from SQL Server to PostgreSQL. I've got almost everything done I believe except for I can not get DbProviderFactory to work with Npgsql. Factory = DbProviderFactories.GetFactory("Npgsql"); yields Unhandled Exception: System.Configuration.ConfigurationErrorsException: Failed to find or...

How to split compound column like ROW, ARRAY?

I have same problem with this stopped thread. http://www.mail-archive.com/[email protected]/msg28070.html QUERY-1 SELECT r.id, ( SELECT rl.reminder_header, rl.reminder_footer FROM reminder_levels AS rl WHERE rl.lookup = ( SELECT MAX(reminder_level_lookup) ...

Unions of polygons (ST_UNION for Geography type)

Hi everyone, I am looking for a function that will return the intersection of 2 or more polygons (geography type). I am aware of ST_UNION, ST_COLLECT but it works only for geometry type. Any tip will be really appreciated ...

Postgres disable super user account

Hi all, How does one go about disabling a super user account within postgres without deleting it. I looked at the Alter Role documentation and I am drawing blanks. In addition this is the only super user account and the subordinate accounts own the tables that they are responsible for ...

DB Design: best practices to hierarchical structure.

I'll try to expose as clear as possible ;) Well, i need to store some data that can be linket to themselves as the parent > child relationship, with no-limit deep. My first try was: entry_id | parent_id | value 1 | NULL | Foo //foo is the grand parent 2 | 1 | Bar //bar is child of Foo 3 | 1 ...

Error Installing ruby gem for PostgreSQL

I am trying to configure a rails server to use PostgreSQL, but getting stuck when installing the postgres gem. I have installed PostgreSQL successfully on the machine (and can use it for other applications) but am getting an error when attempting to install the ruby gem for it. I do: sudo gem install postgres And get the following outp...

can we get the postgres db dump using SQLAlchemy?

Hi, Is it possible to have the postgres database dump(pg_dump) using SQLAlchemy? i can get the dump using pg_dump but I am doing all other db operations using SQLALchemy and thus want to know if this dump operation is also opssible using SQLAlchemy. Any suggestion, link would be of great help. Thanks, Tara Singh ...

PostgreSQL import problem - all tables but one import.

I have a reasonably sized postresql dump (around 270 mb) that I'm importing into a new database. All the tables import without issue except for one - the users table - and I'm neither sure what the problem is, nor what the best way to figure it out is. The tables after it import without error, so it's not as if it barf's on something and...

Production Postgres query returning different values than local sqlite query

I have a query based on the answer to this question: http://stackoverflow.com/questions/2939061/handling-ties-when-ranking-from-the-highest-to-the-lowest/2939079#2939079 Given this dataset: Name | Score Mike | 5 John | 3 Mary | 3 Matt | 0 The following query returns a user array containing the correct values. User.find_by_sql("SELECT...

how to import exsisting *.sql files in postgresql 8.4?

i am using postgresql 8.4 and i have some *.sql files to import into database how can i do so ? thanx in advance! ...

postgresql creating rules on columns?

Hi guys, I need to format my SELECT queries on a text column. How may I do it without expliciting inserting it together with the query? Do i use a rule in this case? I have tried creating a rule on the tables' column but apparently it won't work. create or replace rule t_format AS ON SELECT TO site_ss_last_entry2 DO INSTEAD ...

How to get timestamp as timestamp from postgres?

Hi, I want to get data from potgree SQL. There is a column in type timestamp without timezone. Simple SELECT returns me formated date but I want timestamp in miliseconds simply. How I can do it in SQL? Thx for answers. Kamilos ...

Report Writer for Linux (XML, PGSQL, PHP, LINUX)

I'm a developer on an open source ERP project, it's web based and as you'd expect we have a requirement to print various reports. We currently use ezPDF but it means having to set a ridiculously high memory limit / execution time, and that is still limiting us to how many reports we can print. We liked the look of rlib however complicat...

How to show row numbers in PostgreSQL query?

I'd like to show the observation number for each record returned by a PostgreSQL query. I think in 8.4 windowing functions can perform this capability. Edit: I think I just found the answer, but since this hasn't been asked before, I'll keep the question open. I also welcome non-windowing function possibilities. Edit 2: So a little t...

Is PostgreSQL IN() statement still fast with up to 1000 arguments?

I'm querying to return all rows from a table except those that are in some list of values that is constant at query time. E.g. SELECT * FROM table WHERE id IN (%), and % is guaranteed to be a list of values, not be a subquery. However, this list of values may be up to 1000 elements long in some cases. Should I limit this to a smaller ...

Downloading and using a database from an untrusted source?

If I was to obtain a database (in this case for postgresql) from an untrusted source, is there any risk in activating it and querying it? ...

Are PostgreSQL VIEWS created newly each time they are queried against?

I am creating a web app which has some complex underlying associations. In order to solve several issues I was having I created a UNION View. There are probably a lot of other ways this could be solved. But I am now considering the efficiency of my design, and I wanted to know if a VIEW is newly created each time it is queried, or is it...