Hi I have written a pgsql function along the lines of what's shown below. How can I get rid of the $1, $2, etc. and replace them with the real argument names to make the function code more readable? Regards Peter CREATE OR REPLACE FUNCTION InsertUser ( UserID UUID, FirstName CHAR(10), Surname VARCHAR(75), Email VARCHA...
I have a table that describes which software versions were installed on a machine at various times: machine_id::integer, version::text, datefrom::timestamp, dateto::timestamp I'd like to do a constraint to ensure that no date ranges overlap, i.e. it is not possible to have multiple software versions installed on a machine at the same ...
I am trying to create a pivot table type view in postgresql and am nearly there! Here is the basic query: select acc2tax_node.acc, tax_node.name, tax_node.rank from tax_node, acc2tax_node where tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531'; And the data: acc | name | rank --...
Hi can someone explain me why it works like this? => select client_id from clients_to_delete; ERROR: column "client_id" does not exist at character 8 but, when putting this inside an IN()... => select * from orders where client_id in(select client_id from clients_to_delete); it works! and select all rows in the orders table. Same...
I'm trying to better understand the concept of 'autocommit' when working with a Postgres (psycopg) connection. Let's say I have a fresh connection, set its isolation level to ISOLATION_LEVEL_AUTOCOMMIT, then run this SQL directly, without using the cursor begin/rollback methods (as an exercise; not saying I actually want to do this): I...
I have a primary table for Articles that is linked by a join table Info to a table Tags that has only a small number of entries. I want to split the Articles table, by either deleting rows or creating a new table with only the entries I want, based on the absence of a link to a certain tag. There are a few million articles. How can I do ...
There is a table: doc_id(integer)-value(integer) Approximate 100.000 doc_id and 27.000.000 rows. Majority query on this table - searching documents similar to current document: select 10 documents with maximum of (count common to current document value)/(count ov values in document). Nowadays we use PostgreSQL. Table weight ...
Hello I have two tables in database: CREATE TABLE items( id SERIAL PRIMARy KEY, ... some other fields ); This table contains come data row with unique ID. CREATE TABLE some_choosen_data_in_order( id SERIAL PRIMARy KEY, id_items INTEGER[], ); This table contains array type field. Each row contains values of IDs from table "ite...
Hello, First time posting here, but an avid reader. I am experiancing slow query times on my database (all tested locally thus far) and not sure how to go about it. The database itself has 44 tables and some of them tables have over 1 Million records (mainly the movies, actresses and actors tables). The table is made via JMDB using the ...
Ok I have a PostgreSQL server with a Database titled brittains_db that I only have PuTTY access to. I can also upload via FTP to the web server which has access to PostgreSQL and the Database somehow... I have made a SQL file named logins.sql CREATE TABLE logins( userName VARCHAR(25) NOT NULL PRIMARY KEY, password VARCHAR(25) N...
I am finding myself with the issue of needing to execute the postgres createuser.exe from a batch script and cannot get it to stop prompting me with the following: Enter name of role to add: my batch script looks like this: echo calling createuser! createuser username %super_user% -s -U Super_Postgres s -q Where %super_user% is a com...
I am trying to create a record containing the path to a file. The insertion is done into a Postgres database where UTF8 is enabled, using the NpqSQL driver. My table definition: CREATE TABLE images ( id serial, file_location character varying NOT NULL ) My SQL statement including the code that executes it (boiled down to a mi...
I have a table containing some names and their associated ID, along with a snapshot: snapshot, systemid, name[, some, other, columns] I need to identify all the unique names that a systemid has had across all snapshots, but only where there has been at least once change. For example, with the data: 'DR1', 0, 'MOUSE_SPEED' 'DR1', 1, ...
My table is like this on Postgres, note that all days start by 01, there is only 1 entry a month+year SELECT * FROM "fis_historico_receita" +----+------------+---------------+ | id | data | receita_bruta | +----+------------+---------------+ | 1 | 2010-02-01 | 100000.0 | | 2 | 2010-01-01 | 100000.0 | | 3 | 2009-12-01...
I have a postgresql db and a C# application to access it. I'm having a strange error with values I return from a NpgsqlDataAdapter.Fill command into a DataSet. I've got this code: NpgsqlCommand n = new NpgsqlCommand(); n.Connection = connector; // a class member NpgsqlConnection DataSet ds = new DataSet(); DataTable dt = new DataTabl...
I'm designing a fulltext search function in postgresql for my current project. It works ok with ispell/myspell dictionaries so far. Now I need to add support for chinese, japanese and arabic search. Where do I start? There are no templates or dictionaries available for those languages as far as I can see. Will it work with pg_catalog.sim...
Hi folks, I've been looking into popularity algorithms used on sites such as Reddit, Digg and even Stackoverflow. Reddit algorithm: t = (time of entry post) - (Dec 8, 2005) x = upvotes - downvotes y = {1 if x > 0, 0 if x = 0, -1 if x < 0) z = {1 if x < 0, otherwise x} log(z) + (y * t)/45000 I have always performed simple orderin...
Hey I'm really new to PHP and PostgreSQP or any database in that matter. So I'm at a loss how to do this. I need an if statement that says. If(the username user just typed in is already in database) { my code here } the variable the username that the user just typed in is $userNameSignup how would I do that with PHP for PostgreSQL...
I have a list of timestamped logs and I'd like a query to return 12 booleans say whether a certain month contains any logs, for each month of the year (starting from January), i.e.: (True, False, False, True, False ..., True) I currently have the following query, which will show me all months containing data: SELECT DISTINCT(EXTRACT(...
I inherited some code that is using the Apache commons-dbcp Connection pools in an OSGi bundle. This code works fine with Eclipse/Equinox OSGi version 3.4.3 (R34x_v20081215), commons-dbcp 1.2.2 and the postgres jdbc3 8.3.603 bundles from springsource.org. I wanted to modernize, maybe this was my first mistake! When I use the new versio...