Hi;
I'm looking for a way to do this:
# \d tbl
Table "public.tbl"
Column | Type | Modifiers
+-+---
idn| integer |
code | text|
# SELECT * FROM tbl;
idn | code
-+--
1 | A
2 | B
2 | C
3 | A
3 | C
3 | E
(6 rows)
# select idn,
I have a database running 8.0.1
One of the tables uses a plpgsql function as a
check constraint. There is data in the table that
passed the constraint.
The problem comes when trying to restore the
database using a file created by pg_dump.
Some of the data required by the check function
is being
Some of the data required by the check function
is being restored after the data being checked
by the function and so it all fails the constraint.
Are you saying that the check function perform queries against other
data? That might not be a good idea -- consider what happens if
the data change
I have not been able to work out how to do this is Postgres 8
(pseudo-code)
if exists table foo
drop table foo;
end
create table foo;
If I go with
drop table foo;
create table foo;
then it barfs on an empty db.
The assumption here is that the SQL is coming in on a script via the
select
coalesce(a.n,0) as a,
coalesce(b.n,0) as b,
coalesce(c.n,0) as c,
coalesce(a.s,b.s,c.s) as s
from
( select 1 as n, 0 as s) a full outer join
( select 1 as n, 1 as s) b full outer join
( select 2 as n, 2 as s) c
on
a.s = b.s and
b.s = c.s
Let's say I am writing a file for creating a database. Say ...
-- schematest
CREATE SEQUENCE foo_id_seq;
CREATE TABLE foo(
id integer
PRIMARY KEY
DEFAULT nextval('foo_id_seq'),
xx text
);
I want to be able to ...
lee=# drop schema beta cascade;
DROP SCHEMA
lee=# create sch
I must have a fundamental misunderstanding about using schema.
Before using schema, I usually have a file that has my database
definition, and I can play that file back in to a new database to
create a testing area or to create my production setup.
I think I want to use schema the same way.
My pr
Every once in a while my automated script fails because
some other system which should have disconnected
failed to unhook. The old data and old connection are
not important to me. The new run needs to go through.
You can look for people connected and kick them out with kill -2 on
their pid. The tr
So that means somehow I need to kick off another
script with elevated priveleges to take care of it ...
I would rather not have to do that, but I do not see another way.
If you are creating a database from scratch why not create a brand new
database and drop the old one at some later point in time
I am following along with the pl/pgsql docs here:
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
In section 37.4.3. Row Types I have altered the function slightly
(I finished the where ... clause) :
CREATE or REPLACE FUNCTION use_two_tables(tablename) RETURNS text AS '
DECL
Unless your function parameter is an integer you must quote it... eq:
select use_two_tables('tablename');
Hrm... That does not work either:
# select use_two_tables('tablename');
ERROR: function use_two_tables("unknown") does not exist
HINT: No function matches the given name and argument types
>Unless your function parameter is an integer you must quote it... eq:
>
>select use_two_tables('tablename');
>
Hrm... That does not work either:
# select use_two_tables('tablename');
ERROR: function use_two_tables("unknown") does not exist
Why not just use a text type in your definition?
CREATE
I am following along with the pl/pgsql docs here:
http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
Now, how do I call the function?
I believe you want
select use_two_tables(tablename.*) from tablename;
"foo.*" is the locution for referring to the whole-row value coming fro
I have a database where I remove the schema public. When I
try to use the createlang script, it fails like this ...
createdb foo
CREATE DATABASE
psql foo -c "select version()"
version
-
PostgreSQL 7.4.
How do i convert a result to upper/lowercase?
I'ld like to do the following:
SELECT UPPER(lang) from languages;
and get for example "uk" as "UK", "dk" AS "DK" and so on?
That looks right to me. Are you getting an error?
lee=# select version();
version
--
is it possible to use a parameter of a plpgsql-function to order a
selection
inside the function?
You need to use the FOR-IN-EXECUTE style of query. That way
you can use any string you want (including text passed in as
a parameter) to build the query inside the function ...
http://www.postgresql.
Hi;
I have been writing a ton of PL/pgSQL lately. It seems like there is
probably a lot of code out there that would be of general use to
other people. I looked around for a "postgres cookbook" but only
found a few dead links.
There is one on
http://techdocs.postgresql.org/oresources.php
(http://ww
Have a look at this simpler non looping version of week_start()
That is a nice idea. I had to modify it a bit in order to get
the same answers as my other function ...
CREATE or REPLACE FUNCTION week_start2(integer, integer) RETURNS date AS '
DECLARE
pyear ALIAS FOR $1;
pweek ALIA
My very simple table creation test (based on their "first.py"
example is failing ... Here's what I'm trying. Non-pythonated
syntax works in pgsql:
no go:
curs.execute("""CREATE TABLE key_test (
key_col CHAR(9) PRIMARY KEY,
nother_col CHAR(256))""")
Works fine for me:
d=psycopg.
On Mon, 2 Jul 2001 15:04:41 + (UTC), Richard Huxton <[EMAIL PROTECTED]>:
> There is a brief description of PG's MVCC in Linux Gazette that people might
> find of interest. Written by Joseph Mitchell of Great Bridge
>
> http://www.linuxgazette.com/issue68/mitchell.html
>
Thank you. That is
I am running PostgreSQL 7.1.2 on FreeBSD 4.3
This is an old 486 with only 12MB memory.
I am able to create databases and insert and retrieve data,
but when I try to query on the structure of the database
I am getting this error:
signin=# \d person_personid_seq
pqReadData() -- backend closed the
> (Is there any good reason for this, btw.? The major vendors [Oracle, SQL
Server, Sybase] support having logfiles (txn, redo, archive) on separate file
systems/devices for reliability and performance.)
>
My understanding is that you _can_ do this, by shutting down the server
moving the file
On Mon, 6 Aug 2001 11:20:34 +0300, Timo Lehtinen <[EMAIL PROTECTED]> wrote:
> Hello!
>
> I'am very new to linux and postgresql. I just intalled Redhat7.1. and
> downloaded rpms and installed rpms. Now I'am stuck with startin the server
> itself. What do I have to do after I have installed rpms?
On Mon, 13 Aug 2001 17:30:50 GMT, Jova <[EMAIL PROTECTED]> wrote:
> how do spool my results to a file.
> I know there is a command spool on and off. How do I use it?
>
\o
then
\o
to shut it off
looks like
\g
does something similar for single queries (I did not know that).
remember \? too
> My question:
> is there anyway of retreiving the sequence_name corresponding to the
> respective column,
> knowing just the tablename and columnname?
>
> The reason I need to do this, is because the application I write
> dynamicly creates new tables, and I have no way of knowing the sequence
>
On Sun, 19 Aug 2001 20:59:35 + (UTC), omid omoomi <[EMAIL PROTECTED]>:
> hi all,
> I have pg 7.0.3 installed. I can use ascii() to get the ascii code of a
> character but I can not use chr() to reverse the function... is it a matter
> of the pg version I have? If so, is there any other way t
On Mon, 20 Aug 2001 18:19:49 + (UTC), Killian May <[EMAIL PROTECTED]>:
> Hi,
>
> I'm a little new to postgres, and I'm having trouble droping a sequence.
> I get the following error:
>
> ERROR: mdopen: couldn't open scotty_scottyid_seq: No such file or
> directory
>
> when I try to recre
On Mon, 20 Aug 2001 22:08:22 + (UTC), <[EMAIL PROTECTED]>:
> orf is a primary key. It is a unique string that links the tables.
>
> Essentially, the setup is this: I am presented with data called
> transcription profiles. These contain two columns: orf, which is a unique
> representation of
On 26 Aug 2001 13:50:16 -0700, Cody <[EMAIL PROTECTED]> wrote:
> I just finished reading Bruce M's book, so this thread confuses me,
> esp. Jan's posts. I take full heed of the need for application level
> user/thread management, but I was interested in using a parallel
> set-up in PG (however re
On Fri, 24 Aug 2001 21:53:00 GMT, uncleleo <[EMAIL PROTECTED]> wrote:
> I am attemping to create multiple databases with Postgresql ver. 7.0.3
> running on Mandrake 8.0 rpm. The tool that I am using is Pgadmin ver 7.1.0.
>
> Can someone tell me how I can connect to different databases in a singl
On Fri, 7 Sep 2001 14:22:22 +0800, Corn <[EMAIL PROTECTED]> wrote:
> Does pgsql supports full text keyword index?
> The one like M$SQL / Oracle text index.
>
I do not use this, but I think you will find some interesting
things in the contrib directory of the postgres sources.
-
On Sun, 23 Sep 2001 03:56:50 GMT, Pat M <[EMAIL PROTECTED]> wrote:
> I'm just a hobbyist so this is probably atroceous, but I'm trying to do
> something like the following. Sorry its not in real SQL format. I know how
> to auto-update by referencing to a primary key of another table, but what if
>
> * area - arbitrary name for a collection of sites (optional)
>
> * site name for a collection of buildings (may only be one building)
> optional
>
> * building - a single building not optional name for a collection of rooms
>
> * zone - sub area of a building not optional name for a collectio
On Sat, 12 May 2001 13:00:35 -0300, David Stevenson <[EMAIL PROTECTED]>
wrote:
> I am a new user of PostgreSQL and Linux. I have my Linux up and running and
> my PostgreSQl semi-configured. I can get into PostgreSQL using super user
> on root and then super user into postgres. The problem is w
On Thu, 07 Jun 2001 19:11:21 -0400, Grant <[EMAIL PROTECTED]> wrote:
> ok. i've found a weird bug. I have five records in a table. in one
> column i'm doing a select based on, two values for the column are NULL.
> i do a 'SELECT testcolumn FROM testtable WHERE testcolumn!=1'. This
> query for
35 matches
Mail list logo
