C# Postgresql Backup Restore Solution
Hey guys, I'm a newbie for C#. can you tell me how to backup and restore db from postgresql using C#. i'm using npgsql as data provider. Thanks ...
postgresql
How to outer-join two tables (main and many-to-one sub-table) to get only ONE item from second table?
I have two tables that are something like this: Main table: id (int), title (varchar), etc. Sub-table: main_table_id (foreign key into main table), tag (varchar), etc. There can be zero or more subtable rows for a given row in the main table. I want to do a query that will return every row of the main table, with the columns of the...
Application to reverse engineer MySQL & PostgreSQL DB?
Is there an application to reverse engineer an existing database in mysql and/or postgre? I'm interested in obtaining the DB diagram from an existing one, similar as it can be done in MSSQL Server ...
How do I change a Crystal Report's ODBC database connection at runtime?
I have a report made with Crystal Reports 2008 that I need to deploy a production system which means that I need to be able to change the database connection at runtime. The database is PostgreSQL 8.3.0 and the connection I use for creating the initial report is an ODBC connection. I have found various ways to change the database conne...
Database Update Order
I've run into a situation in our database where I need to shift the timestamps of a number of records by a day, however I have a unique constraint that requires the an id field and a timestamp field to be unique. Here is the table description. Table "public.eedata" Column | Type | ...
group-by question in postgresql
Say I have a table 'orders' created as: CREATE TABLE orders (id SERIAL, customerID INTEGER, timestamp BIGINT, PRIMARY KEY(id)); Timestamp being the UNIX timestamp. Now i want to select the ids of the LATEST orders for every customer. As a view would be nice. however the...
Does PostgreSQL implement Geospatial relationship queries?
My company is looking at different database providers, and how they implement Geospatial queries to get distances from a Lat/Long for a number of records. We are going to investigate PostgreSQL as a potential database platform. I've heard that PostGRE implements Geospatial queries. Is this correct? Is there a good reference somewhere...
It is possible to retrieve a value from a table without spanish characters?
It's possible to retrieve a value (stored with spanish characters like 'ñ' or 'á') from any given table without spanish character? i.e: If I have the value 'Japón' stored, retrive it like 'Japon'. ...
What's the Postgresql equivalent of string.Format("{0:s}", DateTime.Now) ?
What's the equivalent of sortable datetime/timestamp varchar in PostgreSQL? Console.WriteLine("{0:s}", DateTime.Now); sample format output: 2000-05-04T15:30:59 This works, SELECT now::varchar, output is '2009-03-25', but only for date type, wondering what's the equivalent for timestamp. Note, i know date is sortable in and of itse...
How to interpret this Rails error?
Hi does anyone know what this means? I only get this error when I app is deployed on a server and using PostgresQL. When I'm running locally and testing on SQLite, it is fine. I have a features_simulations join table, I think it is related to it somehow. Processing AdminController#confirmed (for 211.30.107.155 at 2009-03-25 09:06:21) [G...
SQL equality/inequality comparison with nullable values
first take, kludge solution, sentinel approach(it's imperative that your program should not allow inputting of sentinel value): select coalesce(a, -2147483648) = coalesce(b, -2147483648) as is_equal -- a little postgresism let's say you forgot to block the sentinel value on your program, the user inputted -2147483648 on the B field...
How can I perform an AND on an unknown number of booleans in postgresql?
I have a table with a foreign key and a boolean value (and a bunch of other columns that aren't relevant here), as such: CREATE TABLE myTable ( someKey integer, someBool boolean ); insert into myTable values (1, 't'),(1, 't'),(2, 'f'),(2, 't'); Each someKey could have 0 or more entries. For any given someKey, I need to know if a)...
Develop with Postgres Plus and Deploy Postgresql Open Source?
According to the answers in this comparison question, there's no technical difference between the core PostgreSQL engine and Postgres Plus server from EnterpriseDB. The developer license is only $95/seat. Their development tools look quite attractive and that cost might be worth it to get increased productivity even if you then deploy t...
Restoring Postgresql database
Do I always have to delete and then create a database to restore it from a pg_dump file? If I don't delete the database, the data being restore is added to the current data in the DB even if some register already are in the database (so the data is duplicated). ...
What applications do you use for data entry and retrieval via ODBC?
What apps or tools do you use for data entry into your database? I'm trying to improve our existing (cumbersome) system that uses a php web based system for entering data one ... item ... at ... a ... time. My current solution to this is to use a spreadsheet. It works well with text and numbers that are human readable, but not with fore...
How do I UPDATE a row in a table or INSERT it if it doesn't exist?
I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction...
How do you expose case sensitivity to users?
Okay, I get it. Data in PostgreSQL is case-sensitive. And I know I can use LOWER() to make my queries case-insensitive. I know there might even be a "citext" type in a future version of PostgreSQL. My question is, today, how do you deal with this when designing user interfaces? I'm thinking specifically of uniqueness constraints. Le...
postgresql sequence problems with lastinsertid and zend framework
Hi I have just started working on a project and I am using zend framework and postgresql (normally use MySQL) however I am hitting a problem when I am trying to get the last inserted id when using the Zend_Db insert command. When using the function $db->lastinsertid('users', 'userid'); I get the following error message: SQLSTATE[42P01...
Cannot simply use PostgreSQL table name ("relation does not exist")
I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could not connect: ' . pg_last_error()); $query = 'SELECT * FROM sf_ba...
What's the PostgreSQL equivalent of MSSQL's CONTEXT_INFO?
In relation to my other question "What’s the best way to audit log DELETEs?". What's the PostgreSQL equivalent of CONTEXT_INFO? [EDIT] I want to log deletes using trigger, but since i'm not using the database user as my app's logical user, I cannot log the CURRENT_USER from the trigger code as the user who deleted the record. But for...