is there a way to reset the primary key of a postgres table to start at 1 again on a populated table? right now its generating numbers from 1000000 and up. I want it all to reset and start to 1, keeping all my existing data intact. Thanks. ...
is there some library for connecting to postgresql database and to have support for TransactionScope I tried Npgsql but it doesn't yet support transactionscope ...
How can write regular expressions to match names like 'José' in postgres.. In other words I need to setup a constraint to check that only valid names are entered, but want to allow unicode characters also. Regular expressions, unicode style have some reference on this. But, it seems I can't write it in postgres. If it is not possible ...
I have a method that gets the DateTime from the DB with the following code: NpgsqlCommand cmd = ActiveConnection.CreateCommand(); cmd.CommandText = "SELECT NOW()"; object obj = cmd.ExecuteScalar(); return (DateTime)obj; Sometimes an InvalidCastException is thrown when converting the result to DateTime. Sometimes the result (obj) is nu...
Let's say I have a table with with one column made up of 4 rows. Names name1 name2 name3 name4 How could I get all permutations of this column rows. ie name1 name2 name3 name4 name1 name2 name4 name3 ETC. ...
I'm passing some values into a postgres character field using psycopg2 in Python. Some of the string values contain periods, slashes, quotes etc. With MySQL I'd just escape the string with MySQLdb.escape_string(my_string) Is there an equivalent for psycopg2? ...
I'm using PostgreSQL 8.1.17, and I have a table with account numbers. The acceptable range for an account number is a number between 1 and 1,000,000 (a six digit number). The column "acctnum" contains the account number. Selecting all the numbers in use is easy (SELECT acctnum FROM tbl_acct_numbers ORDER BY acctnum). What I would like to...
I have a plpgsql function which updates table data. I want to call this function within a loop passing the looping variable as an argument, and commit after each function call. I tried to make another plpgsql function, but I figured out that i can't do commit in a function unlike oracle procedure. Is there a way to do this in a regula...
I have a table with hundreds of millions rows with schema like below. tabe AA { id integer primay key, prop0 boolean not null, prop1 boolean not null, prop2 smallint not null, ... } The each "property" field (prop0, prop1, ...) has a small number of distinct values. And I usually query to find "id" from the given conditions of p...
Does PostgreSQL support \b? I'm trying \bAB\b but it doesn't match anything, whereas (\W|^)AB(\W|$) does. These 2 expressions are essentially the same, aren't they? ...
I have a table like this to save the results of a medical checkup and the date of the report sent and the result. Actually the date sent is based on the clinic_visit date. A client can have one or more reports (date may varies) --------------------------------------- | client_id | date_sent | result | -------------------------------...
I want to check type of value in postgres like this: SELECT id, CASE WHEN val_is_integer THEN (SOME_QUERY) WHEN val_isnot_integer THEN (ANOTHER_QUERY) ELSE 0 END FROM test; How to do that? notes: the value is varchar type in table, and in that field there is value is numeric and varchar ....
I'm trying to work on postgresql locally with ubuntu 10.04. I get this error after I configure with ./configure --enable-cassert --enable-debug --prefix=$HOME/pgsql when I make && make install I get this error: make -C parser all make[3]: Entering directory `/home/james/school/db/pg-stuff/hw1/postgresql-8.4.2/src/backend/parser' E...
I have a pretty primitive table schema now ( in Postgres ): CREATE TABLE "public"."sandbox_demo" ( "id" int4 DEFAULT nextval('sandbox_demo_id_seq'::regclass) NOT NULL, "source" text DEFAULT NULL NOT NULL, "created" timestamptz(6) DEFAULT NULL NOT NULL, "modified" timestamptz(6) DEFAULT NULL NOT NULL ) However, this tab...
Here is what I'm trying: private int insertMaterial(AddMaterial add) { final String INSERT = "insert into material (name, keywords, started, finished, subject, description, c_method, c_time, financer, f_time, g_where, g_when, projectname, project_duration, projectleader, projectresearcher, projectfinancer, publicati...
Hello! I'm looking for .Net library to deal with PostgreSQL server that uses kerberos authentication. The environment is Debian linux, PostgreSQL 8.4 and mono 2.6.7. I was trying to do that with Npgsql, but it supports two types of authentication only: passwords-based and "integrated security". As i know, integrated security is windows...
I am trying to create a function to populate 2 tables, the second query containing the new id from the first insert. an example of some of my tables: CREATE TABLE message ( message_id bigserial NOT NULL, member_id bigint NOT NULL, message character varying(8192) NOT NULL, ... ) CREATE TABLE feed_message ( feed_id bigint NOT ...
Hi guys, I am running PostgreSQL 8.4.4 with Ubuntu 10.04. I am trying to generate uuid but can't find a way to do it. I do have the uuid-ossp.sql in /usr/share/postgresql/8.4/contrib/uuid-ossp.sql When I try this is what I get : postgres=# SELECT uuid_generate_v1(); ERROR: function uuid_generate_v1() does not exist LINE 1: SELECT ...
As I said in title, I've installed PostgreSQL usind MacPorts, but cannot access it. The installation process was $ sudo port install postgresql83-server $ sudo mkdir -p /opt/local/var/db/postgresql83/webcraft $ sudo chown postgres:postgres /opt/local/var/db/postgresql83/webcraft $ sudo su postgres -c '/opt/local/lib/postgresql83/bin/in...
I have question about this query, a little bit slower for me :( My problem : I'm searching the minimum ticks in a list which contains each maximum of each list of ticks by seed. => Equal to min( max(seed1), max(seed2), max(seed 3), etc. ) PS : Each seed contains ~ 10000 rows (1 row = 1 ticks) PS2 : I have one table with contains all ...