Re: [SQL] Converting integer to binary

Once upon a time in PostgreSQL there was a function : bitfromint4 ... Any idea where it has disappeared to? You can do # select B'10101101'::int4; int4 -- 173 (1 row) but you want to go # select 173::varbit; which is what bitfromint4 used to do. CG --- Bruno Wolff III <[EMAIL PROTECT

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

--- Tom Lane <[EMAIL PROTECTED]> wrote: > In practice I'm not sure that this is really a situation that we need to > fret about, because using a datatype that isn't in your search path has > got notational problems that are orders of magnitude worse than this > one. The functions and operators tha

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Chris Gamache <[EMAIL PROTECTED]> writes: > > I'm having a heck of a time, and it seems like in my thrashing about > > to find a solution to this problem I have ruined the uniqueidentifier > > datatype in the sch

[SQL] Schema + User-Defined Data Type Indexing problems...

PostgreSQL 7.4.2 -- All vacuumed and analyzed. I inserted the uniqueidentifier datatype into a new schema that I'm working on by changing the search_path to "my_schema" in the contrib SQL. It effectively created the datatype within the schema, all of its functions, operators, and operator classes.

[SQL] Schemata & User-Defined-Type casting issues

PostgreSQL 7.4.2 ... Background: I'm attempting to migrate tables which were created in the pre-schema days to a sensible schema setup. I'm using the "uniqueidentifier" column in some of these tables. When I created the new schema, I created an instance of "uniqueidentifier" and its supporting fun

[SQL] SCHEMA's the easy way?

I'm planning on dipping my toes into the world of schemata. I have tables, created in the Public schema, that I'd like to move to the new schema: SELECT * INTO new.tablename FROM public.tablename; CREATE SEQUENCE ...; CREATE INDEX ...; ALTER TABLE ...; BLAH ...; BLAH ...; BLAH ...; DROP public.tab

[SQL] tsearch2 trigger alternative

Tsearch2 comes with its own tsearch2 trigger function. You pass column names to it, and it puts a vanilla tsvector into the column names in TG_ARGV[0] (zero based, yes?). Not only can you pass column names to it, but you can pass simple functions to it as well. This is magical to me. :) I'm trying

[SQL] Historic Query using a view/function ?

...Postgresql 7.2... I'm building the history of a table using rules. I've been trying to figure out a way to select on a table as it would have appeared at a point-in-time. I can't seem to wrap my brain around the problem, tho. Given some tables CREATE TABLE list ( num int4 NOT NULL, name

[SQL] Historic Query using a view/function ?

...Postgresql 7.2... I'm building the history of a table using rules. I've been trying to figure out a way to select on a table as it would have appeared in a point in time. I can't seem to wrap my brain around the problem, tho. Given some tables CREATE TABLE list ( num int4 NOT NULL, name

Re: [SQL] Query planner: current_* vs. explicit date

Thanks Tom (and others!) Right-on-the-money, as always... By giving it a definitive range I was able to coax query planner to use the index: SELECT id FROM trans_table WHERE trans_date >= (SELECT current_date::timestamptz) AND trans_date < (SELECT current_timestamp); gave me from midnight to th

[SQL] Query planner: current_* vs. explicit date

PsotgreSQL 7.2.4: Query planner is behaving strangely. It operates differently for explicit dates and derived dates... any ideas on why? ( and why I might not have noticed this before... ) CREATE TABLE trans_table ( id serial, user_name varchar(50), trans_type varchar(50), trans_data v

[SQL] undefine currval()

I'm using sequences and currval() to retrieve the last inserted row in a table. If currval() is undefined, as it is when a connection is made, then I know no rows were inserted in that table and can take a different action. This is problematic when using a connection pooling library, as the value

[SQL] Elegant SQL solution:

There are so many (bad) ways to skin this cat... I'm looking for a more elegant solution. If I SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP BY month; It might only return month | rows ---+-- 1 | 234 3 | 998 4 | 403 5 | 252 10|

[SQL] Splitting text into rows with SQL

Using Postgresql 7.2.3 ... In order to search using indexes I need to split a composite field into its components and return it as rows... If this would only work: create table table_with_composite_fields ( data1 serial, data2 varchar(100), composite_field text ); insert into table_with_compos

[SQL] Race condition w/ FIFO Queue reappears!

nfig option would control something so basic. I can't find any reference to it in the 7.3 docs, and my tired eyes did not pick any fixes remotely pertaining to this type of locking problem in the HISTORY file. I'm (sadly) switching back to 7.2 until we can figure this out. CG >Chris Gamache

[SQL] A Costly function + LIMIT

PostgreSQL 7.2.3 I have a function that is quite costly to run on 1000's of records... Let's call it "widget". I have a query (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM eastern_usa ORDER BY state, city, zip, name LIMIT 5000) UNION ALL (SELECT name

Re: [SQL] Using VIEW to simplify code...

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 19 Nov 2002, Chris Gamache wrote: > >> Understood. PostgreSQL 7.2.3. > > > 7.3 will be better for this. There were questions about the safety > > of p

Re: [SQL] Using VIEW to simplify code...

> If you want help, you must provide details. The PG version number is > relevant also. Understood. PostgreSQL 7.2.3. Here's the generated client side sql: select case when (hasflag(ut.flags,1) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data

[SQL] Using VIEW to simplify code...

Right now I dynamicly generate the SQL for an incredibly ugly 4 table join based on user information client-side. I got the bright idea to create a view and then run a MUUUCH simpler client-side query on that view. The problem is that PostgreSQL apparantly runs the view FIRST and then applies the c

[SQL] FIFO Queue Problems

I'm having a race condition with a FIFO queue program that I've created... CREATE TABLE fifo ( id serial, data varchar(100), status int4 DEFAULT 0 ); I have a program that claims a row for itself my $processid = $$; my $sql_update =