How do I connect C# with Postgres?
How do I Connect C# with Postgres? I download the npgsql data provider but couldn't make it work. Are there more examples than the ones in the Postgres site? Cheers, ...
postgresql
Iterate through "linked list" in one SQL query?
I have a table that looks basically like this: id | redirectid | data where the redirectid is an id to another row. Basically if a row is selected, and it has a redirectid, then the redirectid data should be used in it's place. There may be multiple redirects until redirectid is NULL. Essentially these redirects form a linked list ...
To use prepared statements by PHP for Postgres
This is a similar question to this "When not to use Prepared statements?", but with the "how-to" -part and for PostgreSQL. I know that I need prepared statements because I make more than one call to my database during one script. I would like to get concrete examples about the following sentence Look at typecasting, validating and ...
Is MySQL appropriate for a read-heavy database with 3.5m+ rows? If so, which engine?
My experience with databases is with fairly small web applications, but now I'm working with a dataset of voter information for an entire state. There are approximately 3.5m voters and I will need to do quite a bit of reporting on them based on their address, voting history, age, etc. The web application itself will be written with Djang...
Dataset Designer and Postgresql?
Hi, I have used Dataset Designer in built in Visual Studio 2008 for my asp.net application to interact with my Sql Server database. Now i want to port my application to postgresql database. Can i use Dataset Designer similarly with Postgres? Or otherwise i am looking for an alternative tool that works similarly like Dataset Design...
To select the newest 3 questions from PostgreSQL by PHP
How can you select the titles of the newest 3 questions from PostgreSQL database by PHP? My attempt in PHP $result = pg_prepare($dbconn, "query1", "SELECT title FROM questions ORDER BY was_sent_at_time WHERE question_id = $1;"); $result = pg_execute($dbconn, "query1", array(7, 1, 9); // Problem HERE, since I do not kn...
To generate a question for a given user in PostgreSQL by PHP
I have a list of URLs in my homepage which is like SO in the following form <a href="?questions&questions=777">link1</a> <a href="?questions&questions=666">link2</a> The following PHP script has a problem in the parameter of $_GET. $dbconn = pg_connect("host=localhost port=5432 dbname=masi user=masi password=123"); // A QUESTION SE...
To sanitize all user's input in PostgreSQL by PHP
This question is based on this thread. Do you need the explicit sanitizing when you use pg_prepare? I feel that pg_prepare sanitizes the user's input automatically such that we do not need this $question_id = filter_input(INPUT_GET, 'questions', FILTER_SANITIZE_NUMBER_INT); Context where I use Postgres $result = pg_prepare($dbco...
unable to run postgres as windows service
I had this in my windows services: C:/Program Files/PostgreSQL/8.4/bin/pg_ctl.exe runservice -N "postgresql-8.4" -D "D:/PostgreSQL/8.4/data" -w It never finishes executing. But if i did this on the dos shell: C:/Program Files/PostgreSQL/8.4/bin/pg_ctl.exe start -N "postgresql-8.4" -D "D:/PostgreSQL/8.4/data" -w notice that i only cha...
How to do an update based on a count - SQL (postgres)
I have a table, let's call it 'entries' that looks like this (simplified): id [pk] user_id [fk] created [date] processed [boolean, default false] and I want to create an UPDATE query which will set the processed flag to true on all entries except for the latest 3 for each user (latest in terms of the created column). So, for the follo...
Learning PostgreSQL
What's the best place to start learning PostgreSQL administration and programming in a Linux environment? What websites are the most recommended, concise and have easy to learn tutorials? I am already familiar with ANSI SQL from playing with SQL Server and MySQL; however, I've never touched PostgreSQL before. Any direction you can give...
How to make psql completion with Windows?
There is a completion function by the psql command of Cygwin or UNIX There is not the function in psql for a Windows native. How to make psql completion with Windows? ...
Postgres csv import duplicate key error?
Hi, I am importing a CSV file to postgres. copy product from '/tmp/a.csv' DELIMITERS ',' CSV; ERROR: duplicate key value violates unique constraint "product_pkey" CONTEXT: COPY product, line 13: "1,abcd,100 pack" What is the best way to avoid this error.. Would I have to write a python script to handle this error.. ...
How can I resolve the "Resource ID #8" error message in PostgreSQL/PHP?
How can you solve the Resource ID #8 -error message in the following code? The error apparently means that I have a bug in my SQL statement. However, I cannot see it. $result = pg_prepare($dbconn, "query1", "SELECT user_id FROM users WHERE email = $1;"); $result = pg_execute($dbconn, "query1", array("[email protected]")); // to r...
Postgres encoding "UTF8" error whilst inserting images via Java
Hi I am inserting jpeg images into my UTF-8 encoded Postgres database into bytea column/s. I'm using a prepared sql statement to insert the images. In the statement I create a file object, within Java, of the jpeg image and then pass it as a FileInputStream into the setBinaryStream method. However every now and again my Java app will th...
Django ORM in a python threaded application .. Does not exit
I have a standalone threaded python application in which I use Django's ORM (Postgres). After I join all my threads and wait for the application to exit(sys.exit(0)), there is still an extra thread running, for which the sys.exit waits: All the threads I started exited. Does Django spawn threads to handle database connectivity? This ...
pgsql concat() functionality(almost)
Part of the MySQL query that I'm trying to convert to pgSQL : LEFT JOIN {$_TABLES['comments']} c ON c.sid = concat('fileid_' ,a.lid ) This got messy since it's concatenating a string with a column(a.lid), which isn't supported by the SQL 92 || operator(important!). Any idea's how to redo this part of the query for pgSQL? ...
Understanding PHP's database connection variable
How can you have only one declaration of the database connection variable, $dbconn? My login script is based on the following procedure If the user is not authenticated, he is thrown back to the login page If the user is authenticated, he gets an $_SESSION["logged_in"] = true; Then when the user is browsing the main page or other page...
Secure Database Backend for Windows Application that users can't hack into easily.
I'm writing a database driven windows application and both the executable and database need to be installed on the customers machine. Is there a database that I can use as a backend to my application that the user can't get into even though the user is using the same machine that the database is stored on. As far as I can tell, Postgre...
Row-wise IN BETWEEN comparison in a single SQL statement
Given the following tables: CREATE TABLE tree ( id serial NOT NULL, name character varying NOT NULL, type integer, lft integer NOT NULL, rgt integer NOT NULL ) CREATE TABLE item ( id serial NOT NULL, name character varying NOT NULL, tree_id integer CONSTRAINT fk_tree FOREIGN KEY (tree_id) REFERENCES ...