Y ..
Am I missing something?
TIA
Erwin Brandstetter
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 27.04.2011 19:36, Tom Lane wrote:
Erwin Brandstetter writes:
Hi all!
This may seem unimportant, but I still would like to know.
I have columns for timestamps without fractional digits, so I could
define them as timestamp(0).
However, there is no way fractions could ever enter anyway
) without time zone NOT NULL DEFAULT
(now())::timestamp(0) without time zone
TIA
Erwin Brandstetter
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Dec 16, 10:47 pm, pavel.steh...@gmail.com (Pavel Stehule) wrote:
> hello
>
> look on orafce from pgfoundry. There modul utl_file
>
> http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE
Thanks Pavel, that should do the trick.
I assume then, there is no easier built-in way i
Hello,
I need a long text form from a file in my plpgsql variable.
Can anyone think of a more straightforward way to read the file than
the following:
CREATE FUNCTION test() RETURNS void AS
$BODY$
DECLARE
mytxt text;
BEGIN
CREATE TEMP TABLE x (x text);
COPY x from '/path/to/myfi
Thanks Scott!
(And thanks for all the other hints!)
Yes, the goal is to get the data into tables in a pg database. Having
a CSV file or having the data in pg-tables, both equally solve the
problem.
I like this approach as it does not involve additional tools. I will
have to upgrade to pg 8.4 firs
Hi!
How do you import data from an xml-file?
For instance, if I have a file like this:
Sonstiges
5
Buehne
2
Konzerte
1
Reggae
1
45
sonstige
5
44
... and
The last part got scrambled, should read like this:
(...)
Use it like this:
SELECT * FROM foo WHERE foo_id > myval();
Or, for the case at hand, an example in sql:
CREATE FUNCTION my_colors()
RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
LANGUAGE 'sql' IMMUTABLE;
Use it like thi
On Jul 30, 10:53 pm, [EMAIL PROTECTED] ("Richard Broersma")
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <[EMAIL PROTECTED]> wrote:
> > Is there any means like (#define or DECLARE ) where I can write SQL like
(...)
> CREATE VIEW primary_colors_foos AS
> SELECT * FROM foo
>
On Mar 5, 10:20 am, [EMAIL PROTECTED] (Richard Huxton) wrote:
> Erwin Brandstetter wrote:
> > Hi!
>
> > What I want to do:
> > Import a file from the file system into a bytea field of a table.
(...)
> Not that I know of. It's simple enough to do from the applic
Hi!
What I want to do:
Import a file from the file system into a bytea field of a table.
I know how to do it with large objects:
INSERT INTO mytable(oid_fld) VALUES (lo_import('/mypath/myfile'));
And export from there:
SELECT lo_export(oid_fld, '/mypath/myfile2') FROM mytable WHERE
;
Now,
On Jun 13, 3:13 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
(...)
> As I understand, only way to optimize the statement
>
> delete from firma1.rid where dokumnr not in (select dokumnr from
> firma1.dok);
>
> assuming that firma1.dok.dokumnr does not contain null values is to change
> it to
>
> CREATE
On Jun 7, 9:49 pm, [EMAIL PROTECTED] (Jerry Sievers) wrote:
>
> No sense in writing your own func for this; the feature is already
> provided.
>
> select array_to_string(array(select * from generate_series(1,5)), ',');
Tell me about redundant efforts! :)
Regards
Erwin
--
Hi Andrus!
On Jun 12, 6:38 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
> 1 second if for repeated runs from pgAdmin.
> I my script same CREATE TEMP TABLE command takes appox 11 minutes for same
> data (see log below).
I cannot make much sense of this information. I can see no reason why
your script s
On Jun 11, 2:23 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
> I tried
>
> CREATE TEMP TABLE mydel AS
> SELECT r.dokumnr
> FROM rid r
> LEFT JOIN dok d USING (dokumnr)
> WHERE d.dokumnr IS NULL;
> DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr;
> drop table mydel;
>
> and this runs 1 s
On Jun 11, 2:01 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
(...)
> > This index makes no sense at all:
> > CREATE INDEX dok_dokumnr_idx ON firma1.dok USING btree (dokumnr);
>
> I listed table structure and constraints partially.
> Theis is also primary key constraint in dok table:
>
> CONSTRAINT do
On Jun 9, 12:15 am, Erwin Brandstetter <[EMAIL PROTECTED]> wrote:
> 3.) Write results of the subquery in a temp table, then DELETE:
>
> CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
> DELETE FROM firma1.dok USING mydel WHERE firma1.rid.doumnr =
>
Hi Andrus!
On Jun 8, 10:29 am, "Andrus" <[EMAIL PROTECTED]> wrote:
> How to speed up the query
>
> delete from firma1.rid where dokumnr not in (select dokumnr from firma1.dok)
> CREATE TABLE firma1.dok
> (
> doktyyp character(1) NOT NULL,
> dokumnr integer NOT NULL DEFAULT nextval('dok_dokumn
On Jun 5, 10:14 pm, Erwin Brandstetter <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
(...)
> LANGUAGE 'plpgsql' STABLE IMMUTABLE;
There's a typo. Should be:
LANGUAGE 'plpgsql' IMMUTABLE;
Regards
Erwin
---
On Jun 5, 7:39 pm, [EMAIL PROTECTED] (Ragnar) wrote:
>
> create an aggregate function and use that in your
> select.http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html
Of course you could do that. And it would look like that:
CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
Oh my, it took me a ton of text to finally come up with a better idea.
5.) The Sun King solution
"L'etat c'est moi!". The model is as simple as can be:
CREATE TABLE nation
(
nation_id SERIAL PRIMARY KEY
);
CREATE TABLE man
(
man_id SERIAL PRIMARY KEY,
nation_id INTEGER NOT NULL REFERENCE
On Jun 5, 8:35 am, [EMAIL PROTECTED] (Gregory Stark) wrote:
> "Erwin Brandstetter" <[EMAIL PROTECTED]> writes:
> > I postulate further that a king only be king of his own people (rules out
> > multiple kingships, too).
>
> That's not how it's worked i
On Jun 5, 5:10 am, Lew <[EMAIL PROTECTED]> wrote:
> Erwin Brandstetter wrote:
> > CREATE TABLE king
> > (
> >king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE
> > CASCADE ON DELETE CASCADE,
> >nation_id INTEGER UNIQUE,
> >F
Hi Lew!
Thank you for your comments. I have elaborated on them.
On Jun 3, 7:22 pm, Lew <[EMAIL PROTECTED]> wrote:
(...)
> The trouble with this is that it models "kingship" as an attribute of every
> man. (What, no female rulers allowed?)
Yeah, saddening, isn't it? Actually, for simplicity's sa
Or even, slightly shorter:
EXECUTE
'SELECT '
|| array_to_string(ARRAY(
SELECT a.attname
FROM pg_class c, pg_namespace n, pg_attribute a
WHERE n.oid = c.relnamespace
AND a.attrelid = c.oid
AND a.attnum >= 1
AND n.nspname = 'myschema'
AND c.relname = 'mytbl'
On May 30, 7:42 am, [EMAIL PROTECTED] (PFC) wrote:
> Python example :
I found a decent solution for the existing plpgsql function (as
posted). Thanks a lot for the insight into the Python way, though!
Regards
Erwin
---(end of broadcast)--
On Jun 2, 2:43 am, [EMAIL PROTECTED] (Erwin Brandstetter) wrote:
> raise warning '%', kings;
And remove this line of debug code.
/Erwin
---(end of broadcast)---
TIP 6: explain analyze is your friend
RETURN was missing in the AFTER triggers. here is the corrected version:
- begin of code
CREATE TABLE mankind
(
man_id integer primary key,
people_id integer NOT NULL, -- references table people ..,
but that's irrelevant here ..
king boolean NOT NULL DEFAULT false
);
On
Hi group!
In the course of trying to create a cleanly formated posting that would
make my problem understandable I have eventually solved it myself. :)
I now post the solution instead, maybe it is of interest to someone. :)
Here is a showcase how to avoid to the "highlander-problem".
Imagine a
On May 30, 6:48 am, Rodrigo De León <[EMAIL PROTECTED]> wrote:
> You might want to add:
>
> AND a.attnum >=1
>
> to remove "tableoid" and friends from the output.
Now I know why I did not get tableoid & friends: because I am querying
a view which does not yield these fields. But to be on the
On May 30, 6:48 am, Rodrigo De León <[EMAIL PROTECTED]> wrote:
> On May 29, 11:35 pm, Erwin Brandstetter <[EMAIL PROTECTED]> wrote:
>
> > EXECUTE
> > 'SELECT '
> > || (SELECT array_to_string(ARRAY(
> > SELECT a.attname
> > FROM pg_cla
To conclude (to the best of my current knowledge), here is a plpgsql
code sample based on what was said here:
EXECUTE
'SELECT '
|| (SELECT array_to_string( ARRAY(
SELECT column_name::text
FROM information_schema.columns
WHERE table_schema = 'my_schema'
AND table_name = 'my_relation'
On May 30, 2:11 am, Rodrigo De León <[EMAIL PROTECTED]> wrote:
(... useful code example snipped)
> Now see:
>
> http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures
Thanks for your hints, Rodrigo!
I am aware I can consult pg_catalog / information_schema to retrieve
the informa
Created a new 7.4 database.
# create database foo with encoding = UNICODE;
Then tried to restore my dump from pg 7.2 which was SQL-ASCII or Latin1
encoded (cant tell which of the two, only got the dump of the old database
left after upgrading postgresql.)
Succeeded creating the objects, but no da
OK. I understand that you prefer a different approach. For my part, I
like to combine different approaches. I understand also that you
consider it impossible to put all the stuff into one database. I think
it is possible after all, but that is not the point here.
My request remains. I am lookin
some hours now, but did not succeed. Maybe I am
just being blind.
Thanx for any hints!
Regards, Erwin Brandstetter
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unreg
36 matches
Mail list logo
