I just discovered that a run of pg_dump that gets aborted empties any
pre-existing backup file of the same name. It happens whether the run was
deliberately canceled by the user or just failed because of a bad password
(as in the example below).
~/dba$ pg_dump --host=localhost -U ken --format=c
this weirdness, see
http://stackoverflow.com/questions/35809963/cant-see-postgresql-public-schema-in-oracle-sql-developer.
Any advice from the PG world would be very welcome.
~ Thanks, Ken
On Wed, Feb 24, 2016 at 10:52 PM, Ken Winter wrote:
> The best affordable (in this case, free) data modeli
The best affordable (in this case, free) data modeling tool that I have
found is the "Oracle SQL Developer Data Modeler" (
http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html).
The best DBMS (free or otherwise) that I have found is PostgreSQL.
So of course it would
According to the PG 9.1 doc (
http://www.postgresql.org/docs/current/static/sql-altertable.html), this is
a valid flavor of ALTER TABLE:
ALTER TABLE [ IF EXISTS ] name
RENAME TO new_name
But when I try to execute such a command, I get:
ERROR: syntax error at or near "EXISTS"
LINE 1: ALTER T
I swear this used to work, but in PostgreSQL 9.1 it doesn't work any more...
CASE 1: If I write it like this:
FOR func IN (
SELECT * FROM information_schema.routines
WHERE routine_schema = 'tests'
) LOOP
q := 'SELECT tests.' || fun
t
> Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run-
> time parameter
>
>
> On Jun 5, 2010, at 9:46 AM, Ken Winter wrote:
> > In case anybody else needs this functionality, let me offer a family of
> > functions that do comparisons that treat NULL as a
; From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Saturday, June 05, 2010 9:41 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run-
> time parameter
>
> "Ken Winter" writes:
>
When the run-time parameter transform_null_equals is on, shouldn't two
variables with NULL values evaluate as equal? They don't seem to.
At the bottom of this message is a little test function. It tries all
comparisons of NULL-valued variables and NULL constants, both before and
after turni
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Craig Ringer
> Sent: Sunday, May 23, 2010 8:48 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] ROLLBACK in a funct
How can I write a PL/PgSQL function that rolls back every database change it
has done?
I'm about to write a set of database test functions. Each function needs to
do some INSERT, UPDATE, or DELETE actions, test whether they had their
intended effect, and then roll back the test changes to rest
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Tom Lane
...
>
> "Ken Winter" writes:
> > The documentation at
> > http://www.postgresql.org/docs/8.3/static/datatype-date
The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to
say that the special value 'infinity' ("later than all other time stamps")
should work for an date-time column, and the type "date" is listed as among
the date-time data types.
But I can't get 'infini
Mark this one solved. I finally stumbled across an old, forgotten e-mail
thread from 2006 where Tom Lane solved exactly this problem. See
http://archives.postgresql.org/pgsql-general/2006-02/msg01039.php.
~ Thanks again, Tom!
~ Ken
>
> -Original Message-
> From: Ken Winter
I'm trying to implement a history-keeping scheme using PostgreSQL views and
update rules. My problem is that one of the commands in one of my crucial
update rules apparently never executes.
Briefly, the history-keeping scheme involves:
* Two tables: an "h table" that contains the columns for wh
Thanks, Osvaldo and Fernando - your solution works!
> -Original Message-
> From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com]
> Sent: Saturday, February 14, 2009 8:24 PM
> To: Ken Winter
> Subject: Re: [GENERAL] Array in nested query
>
> 2009/2/14 Ken Winte
I'm trying to look up the columns in a constraint in pg_catalog (of
PostgreSQL 8.0.x). I can't figure out how to "join" the elements of the
array that lists the 'attnum's of the columns in the table to the 'conkey'
array in the constraint definition (see
http://www.postgresql.org/docs/8.0/static/c
The following query against my PostgreSQL 8.0.x 'public' schema:
SELECT
fkc.table_name as fk_table_name,
fkc.constraint_name AS fk_constraint_name
FROM information_schema.table_constraints fkc
WHERE fkc.constraint_schema = 'public'
AND fkc.constraint_type = 'FOREIGN KEY'
AND (
Right you are, Tom!
In case anyone else is facing the same migration, pasted in below is a
pl/pgsql function that does the conversion.
~ Thanks to all
~ Ken
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Saturday,
Thanks Adrian ~ See comments at end.
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Adrian Klaver
> Sent: Friday, June 06, 2008 11:49 AM
> To: pgsql-general@postgresql.org
> Cc: Ken Winter
> Subject: Re: [GEN
amount AS text) -> "cannot cast type money to text"
CAST(amount AS varchar) -> "cannot cast type money to character varying"
to_char(money) -> "function to_char(money) does not exist"
~ Ken
> -Original Message-
> From: Joshua D. Drake [mailto:[EM
I understand from
http://www.postgresql.org/docs/8.0/static/datatype-money.html that the
"money" data type is deprecated.
So I want to convert the data from my existing "money" columns into new
un-deprecated columns, e.g. with type "decimal(10,2)". But every SQL
command I try tells me I can'
Applications accessing my PostgreSQL 8.0 database like to submit no-value
date column values as empty strings rather than as Nulls. This, of course,
causes this PG error:
SQL State: 22007
ERROR: invalid input syntax for type date: ""
I'm looking for a way to trap this bad input at the database l
Based on Tom Lane's response, here is version 2 of my attempt to document
what pg_restore does to a target database that already contains objects.
Version 2 has been limited to the case where pg_dump was run with the
--column-inserts option and pg_restore is run with the --clean option.
Also, w
I need to understand, in as much detail as possible, the results that will
occur when pg_restore restores from an archive file into a target database
that already contains some database objects. I can't find any reference
that spells this out. (The PG manual isn't specific enough.)
Instead of ju
nal Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Friday, January 04, 2008 3:35 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] Problem with pg_dump?
>
> "Ken Winter" <[E
When I do a pg_dump from an 8.1 database (with options schema-only,
no-owner, and plain format), the dump file includes the following:
"
--
-- TOC entry 1623 (class 1259 OID 17618)
-- Dependencies: 5 1624
-- Name: transaction_transaction_id_seq; Type: SEQUENCE; Schema: public;
-- Owner: -
--
CRE
While installing PostgreSQL (8.1) recently, I was offered the option to
install the geometry functions. "Why not?" I said to myself, and to the
installer I said yes.
Well, the answer to "Why not?" is that I have a couple hundred functions
that I'll probably never use junking up my installatio
I want to convert a column named "amount", currently of type money, to type
numeric(10,2).
When I try to do this using:
ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2);
I get:
PostgreSQL Error Code: (1)
ERROR: column "amount" cannot be cast to type "pg_catalog.numeric"
So t
Cc: PostgreSQL pg-general List
Subject: Re: [GENERAL] Disabling and enabling constraints and triggers
to make pg_restore work
"Ken Winter" <[EMAIL PROTECTED]> writes:
> I'm trying to do a data-only pg_restore. I'm running into a roadblock
> whe
users only.
(Perhaps I could write a program that drops all my FKs and triggers, and a
second program that recreates them after the data restore is complete. But
that seems a rather brutal and scary way to patch up a gap in the PostgreSQL
utilities.)
Any suggestions?
~ TIA
~ Ken W
] Confused about a function returning SETOF
>
> "Ken Winter" <[EMAIL PROTECTED]> writes:
> > select lov_personinorganization_role_status('Student','Applicant');
> > ERROR: set-valued function called in context that cannot accept a set
>
&
I have the following function:
CREATE or REPLACE FUNCTION public.lov_personinorganization_role_status(
varchar,
varchar)
RETURNS SETOF person_status_in_organization AS
$BODY$
DECLARE
rec person_status_in_organization%ROWTYPE;
role ALIAS FOR $1;
from_status ALIAS FOR
List
> Subject: Re: [GENERAL] Another perplexity with PG rules
>
> "Ken Winter" <[EMAIL PROTECTED]> writes:
> > After trying about a million things, I'm wondering about the meaning of
> > "OLD." as the actions in a rule are successively execu
I'm stumped on the following problem.
Everything between the "---" rows should be executable.
Please advise.
~ TIA
~ Ken
---
-- Here's a table:
CREATE TABLE public.person_h
(
person_id bigint DEFAULT nextval('pop_seq'::text),
effective_date_and_time timestamptz DEFAULT ('now
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 15, 2006 5:39 PM
> To: [EMAIL PROTECTED]
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Does PG really lack a time zone for India?
>
> "Ken Wint
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 15, 2006 10:25 AM
> To: [EMAIL PROTECTED]
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Does PG really lack a time zone for India?
>
> "
s
me that I'm stuck with 7.4 until a production version of the psycopg2
connector comes out.
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 15, 2006 12:34 AM
> To: [EMAIL PROTECTED]
> Cc: 'PostgreSQL pg-general List
The documentation (http://www.postgresql.org/docs/7.4/static/datetime-keywords.html)
doesn’t have an entry for Indian Standard Time, nor for any other time
zone with a GMT+5:30 offset.
Is this just an omission from the documentation? If
so, what are the name and codes of the GMT+5:30
rule to be named "_RETURN"? Or is there any other way to
accomplish what I'm trying to do?
~ Thanks again
~ Ken
> -Original Message-
> From: Stephan Szabo [mailto:[EMAIL PROTECTED]
> Sent: Sunday, February 12, 2006 11:39 PM
> To: Ken Winter
> Cc: 'Tom
> -Original Message-
> From: Stephan Szabo [mailto:[EMAIL PROTECTED]
> Sent: Sunday, February 12, 2006 8:47 PM
> To: Ken Winter
> Cc: 'Tom Lane'; 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named
>
Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Sunday, February 12, 2006 6:43 PM
> To: [EMAIL PROTECTED]
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named
> "_RETURN"?
>
> "
I'm trying to build something that behaves like an updatable view but that
PostgreSQL (version 7.4) regards and presents to the world as a table.
The reason I want to do this odd thing is that my front-end tools
(phpPgAdmin and PostgreSQL Lightning Admin) have handy pre-made data entry
and viewi
If I submit the same "INSERT INTO person_h..." query directly, rather than
as part of the rule, it works fine.
Help?
~ TIA
~ Ken
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 05, 2006 7:49 PM
> To: Ken Winter
> Cc: Pos
Ken
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Saturday, January 28, 2006 1:26 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
>
> "Ken Winter" <[EMAIL PROTE
Is a better PL/pgSQL editor / debugger than pgAdmin III or
phpPgAdmin available anywhere?
I ask because I was stuck for two days on the following
error message:
ERROR: syntax error at or near "LOOP"
CONTEXT: compile of PL/pgSQL function "gen_history"
near line 126
…and neithe
1. What is the value of the NEW variable for a column that is not mentioned
in an UPDATE statement? Is it NULL? If not NULL, what?
For example, given this table:
my_tbl (id integer, att1 varchar, att2 varchar)
and a row-wise ON UPDATE OR INSERT trigger function containing this
condition
~ Thanks!
~ Ken
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Friday, December 30, 2005 12:10 PM
> To: Ken Winter
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] In processing DDL, when does pg_catalog get
> updated
Tom ~
Good idea. The grisly details are as follows.
Here is the DDL script (generated from PowerDesigner 10.1.0.1134):
/*==*/
/* DBMS name: PostgreSQL 7.3 */
/* Created o
I'm running a DDL script that does the following (in this order):
1. Creates a table containing a BIGSERIAL primary key column declaration,
which apparently automatically creates a sequence to populate this column.
2. Runs a "gen_sequences" function that I wrote, which executes CREATE
SEQUENCE st
FWIW: At http://www.postgresql.org/docs/7.4/static/infoschema-triggers.html,
it says there is a column in the information_schema ”triggers”
table named “event_object_name”. The name actually is “event_object_table”.
~ Ken
That works. Thanks! (Thanks also to Joshua Drake, who contributed the same
answer.)
~ Ken
> -Original Message-
> From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 30, 2005 11:19 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subje
How can I cause a PL/pgSQL function to be executed from a
SQL script?
I know how to invoke a function using a trigger, but I don’t
know any other way.
I’m looking at the SQL Command Reference at http://www.postgresql.org/docs/7.4/static/sql-commands.html,
but the only command that l
52 matches
Mail list logo
