Thread: Converting char to varchar automatically

Converting char to varchar automatically

From
"Andrus"
Date:
Database contains about 300 tables.
Most of them contain columns of char(n) type.
 
How to convert all those columns to varchar automatically ?
 
Is it possible to run some update commands in system tables for this ?
Or is it possible to create pgsql script which creates dynamically alter table alter column commands and PERFORMs them ?
 
Any tables have primary keys with char(n) columns and foreign keys on them. Foreign keys are deferrable and initially  immediate.
Will foreign keys allow to perform such alter table alter column commands ?
Or is there better way.
 
Andrus.

Re: Converting char to varchar automatically

From
Andy Colson
Date:
On 10/6/2014 5:29 AM, Andrus wrote:
> Database contains about 300 tables.
> Most of them contain columns of char(n) type.
> How to convert all those columns to varchar automatically ?
> Is it possible to run some update commands in system tables for this ?
> Or is it possible to create pgsql script which creates dynamically alter
> table alter column commands and PERFORMs them ?
> Any tables have primary keys with char(n) columns and foreign keys on
> them. Foreign keys are deferrable and initially  immediate.
> Will foreign keys allow to perform such alter table alter column commands ?
> Or is there better way.
> Andrus.

I'd use a little perl.

Or if your editor has macros, you could use that.

change:
create table bob (
    id char(50),
..
)

to

alter table bob alter id type varchar(50);

You might be able to query them out if you wanted:

select table_name, column_name, character_maximum_length
from information_schema.columns
where data_type = 'character'

Then use that to generate the alter table commands.  Hum... this might
also work:

select 'alter table ' || table_name || ' alter ' || column_name .... etc

but that might try changing system tables which would be bad.

-Andy



Re: Converting char to varchar automatically

From
Melvin Davidson
Date:
This query might work for you, but double check all result statements first.

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||  quote_ident(c.relname)
       || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_attribute a ON a.attrelid = c.oid
  JOIN pg_type t ON t.oid = a.atttypid
 WHERE t.typname = 'char'
   AND n.nspname <> 'pg_catalog';


On Mon, Oct 6, 2014 at 6:29 AM, Andrus <kobruleht2@hot.ee> wrote:
Database contains about 300 tables.
Most of them contain columns of char(n) type.
 
How to convert all those columns to varchar automatically ?
 
Is it possible to run some update commands in system tables for this ?
Or is it possible to create pgsql script which creates dynamically alter table alter column commands and PERFORMs them ?
 
Any tables have primary keys with char(n) columns and foreign keys on them. Foreign keys are deferrable and initially  immediate.
Will foreign keys allow to perform such alter table alter column commands ?
Or is there better way.
 
Andrus.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Converting char to varchar automatically

From
"Andrus"
Date:
Hi!
>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||  quote_ident(c.relname)
>       || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
>  FROM pg_class c
>  JOIN pg_namespace n ON n.oid = c.relnamespace
>  JOIN pg_attribute a ON a.attrelid = c.oid
>  JOIN pg_type t ON t.oid = a.atttypid
> WHERE t.typname = 'char'
>   AND n.nspname <> 'pg_catalog';
It does not return any data.
 
Andrus.
 

Re: Converting char to varchar automatically

From
hari.fuchs@gmail.com
Date:
Melvin Davidson <melvin6925@gmail.com> writes:

> This query might work for you, but double check all result statements first.
>
> SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
> quote_ident(c.relname)
>        || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
>   FROM pg_class c
>   JOIN pg_namespace n ON n.oid = c.relnamespace
>   JOIN pg_attribute a ON a.attrelid = c.oid
>   JOIN pg_type t ON t.oid = a.atttypid
>  WHERE t.typname = 'char'
>    AND n.nspname <> 'pg_catalog';

Make that "t.typname = 'bpchar'".

Re: Converting char to varchar automatically

From
Jim Nasby
Date:
On 10/6/14, 12:41 PM, hari.fuchs@gmail.com wrote:
> Melvin Davidson <melvin6925@gmail.com> writes:
>
>> This query might work for you, but double check all result statements first.
>>
>> SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
>> quote_ident(c.relname)
>>         || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
>>    FROM pg_class c
>>    JOIN pg_namespace n ON n.oid = c.relnamespace
>>    JOIN pg_attribute a ON a.attrelid = c.oid
>>    JOIN pg_type t ON t.oid = a.atttypid
>>   WHERE t.typname = 'char'
>>     AND n.nspname <> 'pg_catalog';
> Make that "t.typname = 'bpchar'".
>
Just a heads-up: each of those ALTER's will rewrite the table, so unless your database is tiny this will be a slow
process.There's ways to work around that, but they're significantly more complicated. 

--
Jim Nasby, Data Architect, Blue Treble
Data in Trouble? Get it in Treble! http://BlueTreble.com



Re: Converting char to varchar automatically

From
Melvin Davidson
Date:
Also, don't forget to test for relkind = 'r'. My bad from before.
Revised query is below.

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
             || quote_ident(c.relname)
             || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
   FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_attribute a ON a.attrelid = c.oid
     JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'bpchar'
    AND c.relkind = 'r'
    AND n.nspname <> 'pg_catalog';

On Mon, Oct 6, 2014 at 6:18 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 10/6/14, 12:41 PM, hari.fuchs@gmail.com wrote:
Melvin Davidson <melvin6925@gmail.com> writes:

This query might work for you, but double check all result statements first.

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
        || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
   FROM pg_class c
   JOIN pg_namespace n ON n.oid = c.relnamespace
   JOIN pg_attribute a ON a.attrelid = c.oid
   JOIN pg_type t ON t.oid = a.atttypid
  WHERE t.typname = 'char'
    AND n.nspname <> 'pg_catalog';
Make that "t.typname = 'bpchar'".

Just a heads-up: each of those ALTER's will rewrite the table, so unless your database is tiny this will be a slow process. There's ways to work around that, but they're significantly more complicated.

--
Jim Nasby, Data Architect, Blue Treble
Data in Trouble? Get it in Treble! http://BlueTreble.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Converting char to varchar automatically

From
Tom Lane
Date:
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> Just a heads-up: each of those ALTER's will rewrite the table, so unless your database is tiny this will be a slow
process.There's ways to work around that, but they're significantly more complicated. 

I think he's trying to get rid of all the blank-padding he's got right
now, so table rewrites are unavoidable.

            regards, tom lane


Re: Converting char to varchar automatically

From
Tom Lane
Date:
Melvin Davidson <melvin6925@gmail.com> writes:
> Also, don't forget to test for relkind = 'r'. My bad from before.

In principle you need to ignore attisdropped columns as well.

Thinking about Jim's point about speed: it'd be wise to collapse any
updates for multiple columns in the same table into one ALTER command,
so that you only rewrite the table once, not once per column.

            regards, tom lane


Re: Converting char to varchar automatically

From
Sergey Konoplev
Date:
BTW, where can I find a list of type1->type2 pairs that doesn't
require full table lock for conversion?

ps. Sorry for top posting.

On Mon, Oct 6, 2014 at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Melvin Davidson <melvin6925@gmail.com> writes:
>> Also, don't forget to test for relkind = 'r'. My bad from before.
>
> In principle you need to ignore attisdropped columns as well.
>
> Thinking about Jim's point about speed: it'd be wise to collapse any
> updates for multiple columns in the same table into one ALTER command,
> so that you only rewrite the table once, not once per column.
>
>                         regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com


Re: Converting char to varchar automatically

From
Tom Lane
Date:
Sergey Konoplev <gray.ru@gmail.com> writes:
> BTW, where can I find a list of type1->type2 pairs that doesn't
> require full table lock for conversion?

There aren't any.  Sometimes you can skip a table rewrite, but that
doesn't mean that a lesser lock is possible.

            regards, tom lane


Re: Converting char to varchar automatically

From
Sergey Konoplev
Date:
On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sergey Konoplev <gray.ru@gmail.com> writes:
>> BTW, where can I find a list of type1->type2 pairs that doesn't
>> require full table lock for conversion?
>
> There aren't any.  Sometimes you can skip a table rewrite, but that
> doesn't mean that a lesser lock is possible.

Oh, sorry, it was a typo, I meant "that doesn't require a full table rewrite".

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com


Re: Converting char to varchar automatically

From
Tom Lane
Date:
Sergey Konoplev <gray.ru@gmail.com> writes:
> On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Sergey Konoplev <gray.ru@gmail.com> writes:
>>> BTW, where can I find a list of type1->type2 pairs that doesn't
>>> require full table lock for conversion?

>> There aren't any.  Sometimes you can skip a table rewrite, but that
>> doesn't mean that a lesser lock is possible.

> Oh, sorry, it was a typo, I meant "that doesn't require a full table rewrite".

Roughly speaking it's the pairs that have a binary (WITHOUT FUNCTION)
coercion according to pg_cast, although we have special logic for a few
cases such as varchar(M) -> varchar(N).

            regards, tom lane


Re: Converting char to varchar automatically

From
Sergey Konoplev
Date:
On Tue, Oct 7, 2014 at 11:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Roughly speaking it's the pairs that have a binary (WITHOUT FUNCTION)
> coercion according to pg_cast, although we have special logic for a few
> cases such as varchar(M) -> varchar(N).

That ones?

select t1.typname, t2.typname
from pg_cast, pg_type as t1, pg_type as t2
where
    t1.oid = castsource and t2.oid = casttarget and
    castmethod = 'b' order by 1, 2;

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com


Re: Converting char to varchar automatically

From
Jim Nasby
Date:
On 10/6/14, 6:16 PM, Tom Lane wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> Just a heads-up: each of those ALTER's will rewrite the table, so unless your database is tiny this will be a slow
process.There's ways to work around that, but they're significantly more complicated. 
>
> I think he's trying to get rid of all the blank-padding he's got right
> now, so table rewrites are unavoidable.

Right, but there's other ways this could be done without requiring an outage. Like creating the new column with
temporaryname, put trigger on table, etc, etc. 

Having dealt with an environment where downtime was thousands of dollars per minute I've gotten very creative at not
takingoutages. :) 
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


Re: Converting char to varchar automatically

From
"Andrus"
Date:
Hi!
 
also, it generates statement which tries to change all columns to one character length columns.
 
Andrus.
 
 
From: Andrus
Sent: Monday, October 06, 2014 8:11 PM
Subject: Re: [GENERAL] Converting char to varchar automatically
 
Hi!
>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||  quote_ident(c.relname)
>       || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
>  FROM pg_class c
>  JOIN pg_namespace n ON n.oid = c.relnamespace
>  JOIN pg_attribute a ON a.attrelid = c.oid
>  JOIN pg_type t ON t.oid = a.atttypid
> WHERE t.typname = 'char'
>   AND n.nspname <> 'pg_catalog';
It does not return any data.
 
Andrus.
 

Re: Converting char to varchar automatically

From
"Andrus"
Date:
Hi!

Using Toms recommendation I added  not attisdropped and now got the query

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
             || quote_ident(c.relname)
             || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar;'
   FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_attribute a ON a.attrelid = c.oid
     JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'bpchar'
    AND c.relkind = 'r'
    AND n.nspname <> 'pg_catalog' and not attisdropped;

Will this create commands which replace all user-defined char  things in
database  to varchar ?

"TYPE varchar" creates single character column so most alter table command
will fail.
How to change this so that original char column width is kept ?
I looked into tables used in this query but havent found column which holds
char column defined width.
How get it or is it better to re-write this query using informational_schema
?

How to change this query so that it creates single alter table command for
every table
(with multiple alter column clauses) to increase conversion speed ?

Andrus.



Re: Converting char to varchar automatically

From
Melvin Davidson
Date:
This revised query should give you what you need:

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname)
            || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length || ');'
  FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON t.oid = a.atttypid
    JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
WHERE t.typname = 'bpchar'
   AND c.relkind = 'r'
   AND n.nspname <> 'pg_catalog' and not attisdropped;


On Wed, Oct 8, 2014 at 3:34 PM, Andrus <kobruleht2@hot.ee> wrote:
Hi!

Using Toms recommendation I added  not attisdropped and now got the query

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname)
            || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar;'
  FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'bpchar'
   AND c.relkind = 'r'
   AND n.nspname <> 'pg_catalog' and not attisdropped;

Will this create commands which replace all user-defined char  things in
database  to varchar ?

"TYPE varchar" creates single character column so most alter table command
will fail.
How to change this so that original char column width is kept ?
I looked into tables used in this query but havent found column which holds
char column defined width.
How get it or is it better to re-write this query using informational_schema
?

How to change this query so that it creates single alter table command for
every table
(with multiple alter column clauses) to increase conversion speed ?

Andrus.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Converting char to varchar automatically

From
"Andrus"
Date:
Hi!
 
Thank you.
 
>This revised query should give you what you need:
>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
>            || quote_ident(c.relname)
>            || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length || ');'
>  FROM pg_class c
>    JOIN pg_namespace n ON n.oid = c.relnamespace
>    JOIN pg_attribute a ON a.attrelid = c.oid
>    JOIN pg_type t ON t.oid = a.atttypid
>    JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
>WHERE t.typname = 'bpchar'
>   AND c.relkind = 'r'
>   AND n.nspname <> 'pg_catalog' and not attisdropped;

How to create single alter table command for every table ?
Can we use string concat aggregate function or window functions or plpgsql or something other ?
 
Andrus.

Re: Converting char to varchar automatically

From
Melvin Davidson
Date:
There really is no easy way to make a single ALTER for each table unless you use a programming language. However, adding a
GROUP BY c.relname,
                  a.attname

would certainly simplify editing. Then you can combine all the
ALTER COLUMN's for each table.

On Wed, Oct 8, 2014 at 6:21 PM, Andrus <kobruleht2@hot.ee> wrote:
Hi!
 
Thank you.
 
>This revised query should give you what you need:
>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
>            || quote_ident(c.relname)
>            || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length || ');'
>  FROM pg_class c
>    JOIN pg_namespace n ON n.oid = c.relnamespace
>    JOIN pg_attribute a ON a.attrelid = c.oid
>    JOIN pg_type t ON t.oid = a.atttypid
>    JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
>WHERE t.typname = 'bpchar'
>   AND c.relkind = 'r'
>   AND n.nspname <> 'pg_catalog' and not attisdropped;

How to create single alter table command for every table ?
Can we use string concat aggregate function or window functions or plpgsql or something other ?
 
Andrus.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Converting char to varchar automatically

From
"Andrus"
Date:
Hi!
 
>There really is no easy way to make a single ALTER for each table unless you use a programming language.
 
I’snt SQL a programming language ?
 
>However, adding a  GROUP BY c.relname,                  a.attname
>would certainly simplify editing. Then you can combine all the
>ALTER COLUMN's for each table.
I wrote
 
with stem as (
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname) as prefix ,
  string_agg(
      ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length ||')',
      ',' ) as body
  FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON t.oid = a.atttypid
    JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
WHERE t.typname = 'bpchar'
   AND c.relkind = 'r'
   AND n.nspname <> 'pg_catalog' and not attisdropped
group by 1
)
 
select prefix || ' '|| body || ';' as statement
from stem
 
Is this prefect ?
 
Andrus.

Re: Converting char to varchar automatically

From
hari.fuchs@gmail.com
Date:
"Andrus" <kobruleht2@hot.ee> writes:

> Hi!
>
> Thank you.
>
>>This revised query should give you what you need:
>>SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
>>            || quote_ident(c.relname)
>>            || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length || ');'
>>  FROM pg_class c
>>    JOIN pg_namespace n ON n.oid = c.relnamespace
>>    JOIN pg_attribute a ON a.attrelid = c.oid
>>    JOIN pg_type t ON t.oid = a.atttypid
>>    JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
>>WHERE t.typname = 'bpchar'
>>   AND c.relkind = 'r'
>>   AND n.nspname <> 'pg_catalog' and not attisdropped;
>
> How to create single alter table command for every table ?
> Can we use string concat aggregate function or window functions or plpgsql or something other ?

string_agg should do it:

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) ||
       '.' || quote_ident(c.relname) || ' ' ||
       string_agg('ALTER COLUMN ' || quote_ident(a.attname) ||
       ' TYPE varchar(' || i.character_maximum_length || ')', ', ') || ';'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
JOIN information_schema.columns i ON
  i.table_name = c.relname AND i.column_name = a.attname
WHERE t.typname = 'bpchar'
  AND c.relkind = 'r'
  AND n.nspname <> 'pg_catalog' and not attisdropped
GROUP BY n.nspname, c.relname;

Re: Converting char to varchar automatically

From
Jim Nasby
Date:
On 10/9/14, 12:41 AM, Andrus wrote:
> Hi!
>  >There really is no easy way to make a single ALTER for each table unless you use a programming language.
> I’snt SQL a programming language ?
>  >However, adding a  GROUP BY c.relname,a.attname
>  >would certainly simplify editing. Then you can combine all the
>>ALTER COLUMN's for each table.
> I wrote
> with stem as (
> SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
>              || quote_ident(c.relname) as prefix ,
>    string_agg(
>        ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length ||')',
>        ',' ) as body
>    FROM pg_class c
>      JOIN pg_namespace n ON n.oid = c.relnamespace
>      JOIN pg_attribute a ON a.attrelid = c.oid
>      JOIN pg_type t ON t.oid = a.atttypid
>      JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
> WHERE t.typname = 'bpchar'
>     AND c.relkind = 'r'
>     AND n.nspname <> 'pg_catalog' and not attisdropped
> group by 1
> )
> select prefix || ' '|| body || ';' as statement
> from stem
> Is this prefect ?

That looks sane, though you didn't need the WITH.

In the future, you'll probably find it easier to go with information schema directly since then you don't have to worry
aboutthings like attisdropped. 

Also, you mentioned that type "varchar" restricts length to 1. That's not true. varchar with no specifier has
unlimited[1]length: 

decibel@decina.attlocal=# create table t(t varchar);
CREATE TABLE
decibel@decina.attlocal=# \d t
             Table "public.t"
  Column |       Type        | Modifiers
--------+-------------------+-----------
  t      | character varying |

decibel@decina.attlocal=# insert into t values( '123' );
INSERT 0 1
decibel@decina.attlocal=#

[1]: In reality you're limited to ~1GB of data
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


Re: Converting char to varchar automatically

From
"Andrus"
Date:
Hi!

> That looks sane, though you didn't need the WITH.

I changed select to code below.
If same table name appears in multiple schemas, it generates duplicate alter
column clauses which cause error.
How to fix it to generate proper sql ?

I added n.nspname='myschame' as shown in code below but problem persists.

Andrus.

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname) || ' ' ||
  string_agg(
      ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' ||
i.character_maximum_length ||')',
      ',' ) || ';' as statement
  FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON t.oid = a.atttypid
    JOIN information_schema.columns i ON (i.table_name = c.relname AND
i.column_name = a.attname)
WHERE t.typname = 'bpchar'
   AND c.relkind = 'r'
   AND n.nspname <> 'pg_catalog' and not attisdropped
and n.nspname='myschema'
group by n.nspname, c.relname



How to use record variable with non-null domain in plpgsql

From
"Andrus"
Date:
I'm looging for a way to use tebool type records in plpgsql method starting from Postgres 9.1
I tried code below but got error

domain tebool does not allow null values
Domain tebool default value is false so plpgsql must assing false to it and should not throw error.

How to fix this so that such record variable can created ?

Andrus.

CREATE DOMAIN tebool AS bool DEFAULT false NOT NULL;
create temp table test  ( test tebool ) on commit drop ;

CREATE OR REPLACE FUNCTION test()
       RETURNS numeric AS $$

    DECLARE
    r_test test;
    begin

    return 0;
    end;  $$ language plpgsql;

    select test();

Posted also in

http://stackoverflow.com/questions/32157166/how-to-fix-domain-tebool-does-not-allow-null-values-in-plpgsql-function


Re: How to use record variable with non-null domain in plpgsql

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> I'm looging for a way to use tebool type records in plpgsql method starting from Postgres 9.1
> I tried code below but got error

> domain tebool does not allow null values
> Domain tebool default value is false so plpgsql must assing false to it and should not throw error.

> How to fix this so that such record variable can created ?

TBH, the problem here is with the not-null constraint on the domain.
Get rid of that and you'll be much happier.  Data types that try to
insist on not being NULL are fundamentally incompatible with SQL
semantics --- to take one example, what do you think will happen
to a column of such a type when it's on the outside of a LEFT JOIN?

It's reasonable to assign NOT NULL requirements to individual table
columns, but not to think of it as being a property of a data type.

Or another way to put it: NULL in SQL is a type-independent concept.
Individual data types simply don't get to opt out of that without
creating more problems than they solve.

            regards, tom lane


Re: How to use record variable with non-null domain in plpgsql

From
"Andrus"
Date:
Hi!

>TBH, the problem here is with the not-null constraint on the domain.
>Get rid of that and you'll be much happier.

Is the only reasonable way is to change domain using

ALTER DOMAIN tebool DROP  NOT NULL   ?

bool types of columns are never used in database. Instead of them tebool type is alway used.

There are alrge number of tebool columns in database. In different installations there may be additional tebool columns

not know at design time.
Will everything work after such change ?

How to disable null values in tebool columns then ?
Should script created which loops over all tebool columns in all tables and adds not null constraint to them ?

Or is there some better solution ?

> Data types that try to
>insist on not being NULL are fundamentally incompatible with SQL
>semantics --- to take one example, what do you think will happen
>to a column of such a type when it's on the outside of a LEFT JOIN?

I tried:

create temp table test  ( test tebool, test2 numeric ) on commit drop ;
create temp table test1  ( test tebool, test2 numeric ) on commit drop ;
insert into test values (false,1);

select test1.test  from test left join test1 on false;

query returns null value.

I also tried to cast result to tebool

select test1.test::tebool  from test left join test1 on false;

and

select null::ebool

This returns also null. So Postgres allows null values in this type instance.
There are no problems.


Andrus.



How to speed up delete where not in

From
"Andrus"
Date:
Hi!


I'm looking for a way to delete records which do not have child rows on big
tables where lot of rows needs to be deleted. Both tables have lot of other
foreign key references.


Document headers are in omdok table:

    create table omdok ( dokumnr serial primary key, ... );

Document rows are in omrid table

    CREATE TABLE omrid
    (
      id serial NOT NULL,
      reanr serial NOT NULL,
      dokumnr integer NOT NULL,
      CONSTRAINT omrid_pkey PRIMARY KEY (id),
      CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
          REFERENCES omdok (dokumnr) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY
IMMEDIATE,
      ....
    );


I tried

    delete from omdok where dokumnr not in      (select dokumnr from omrid)

Query it is running currently 15 hours and is still running.
postgres.exe is using 50% CPU  all the time (this is 2 core CPU).

    explain delete from omdok where dokumnr not in      (select dokumnr from
omrid)

returns:

    "Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
    "  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815
width=6)"
    "        Filter: (NOT (SubPlan 1))"
    "        SubPlan 1"
    "          ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
    "                ->  Seq Scan on omrid  (cost=0.00..77858.61
rows=1897261 width=4)"

- How to delete parents without child rows fast?
- Will this command finish or is postgres hanging ?
- Currently it is running 15 hours. How many hours it takes to finish ?
How to speed up this query ?


Using

    PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit

    Windows 2003 x64 server with 4 GB RAM.


Posted also in

http://stackoverflow.com/questions/32794828/how-to-speed-up-deleting-documents-without-rows

Andrus.



Re: How to speed up delete where not in

From
David Rowley
Date:
On 26 September 2015 at 19:53, Andrus <kobruleht2@hot.ee> wrote:
Hi!


I'm looking for a way to delete records which do not have child rows on big tables where lot of rows needs to be deleted. Both tables have lot of other foreign key references.


Document headers are in omdok table:

   create table omdok ( dokumnr serial primary key, ... );

Document rows are in omrid table

   CREATE TABLE omrid
   (
     id serial NOT NULL,
     reanr serial NOT NULL,
     dokumnr integer NOT NULL,
     CONSTRAINT omrid_pkey PRIMARY KEY (id),
     CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
         REFERENCES omdok (dokumnr) MATCH SIMPLE
         ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
     ....
   );


I tried

   delete from omdok where dokumnr not in      (select dokumnr from omrid)

Query it is running currently 15 hours and is still running.
postgres.exe is using 50% CPU  all the time (this is 2 core CPU).

   explain delete from omdok where dokumnr not in      (select dokumnr from omrid)

returns:

   "Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
   "  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815 width=6)"
   "        Filter: (NOT (SubPlan 1))"
   "        SubPlan 1"
   "          ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
   "                ->  Seq Scan on omrid  (cost=0.00..77858.61 rows=1897261 width=4)"

- How to delete parents without child rows fast?

if you write the query as with a NOT EXISTS, rather than a NOT IN() it should complete much more quickly.

It's important to know that the semantics of NOT IN are likely not at all what you think:

For example, in the following query, would you expect it to return the row with a.a = 3 ?

select * from (values(1),(2),(3)) a(a) WHERE a NOT IN(SELECT b FROM (VALUES(NULL),(1),(2)) b(b));

The presence of NULL causes this to not behave the way you might think, yet it works exactly the way the SQL standard dictates.

You could think of this as equivalent to writing:

select * from (values(1),(2),(3)) a(a) WHERE a.a <> NULL AND a.a <> 1 AND a.a <> 2;

And since a.a <> NULL is 'unknown', this causes the entire WHERE clause to be false, therefore nothing matches.

For this reason PostgreSQL does not optimise NOT IN() the same way as it optimises NOT EXISTS().

If you write the query as:

delete from omdok where not exists(select 1 from omrid where omdok.dokumnr = omrid.dokumnr);

then you might see it execute in a few seconds. Perhaps you should consider cancelling the current query, perhaps perform a VACUUM on omdoc after cancelling, and then run the NOT EXISTS version instead.

I mocked up your tables locally, and populated them with the same number of records as your row estimates in the EXPLAIN you pasted and I got:

test=# delete from omdok where not exists (select 1 from omrid where omdok.dokumn = omrid.dokumnr);
DELETE 0
Time: 1698.233 ms

Whereas with the NOT IN() I cancelled it after 10 minutes.

Regards

David Rowley 

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: How to speed up delete where not in

From
Melvin Davidson
Date:
In addition to the previous recommendation, make sure you have an index on
dokumnr in table omrid.

EG:
CREATE INDEX omrid_dokumnr_fk ON omrid
  USING BTREE (dokumnr);



On Sat, Sep 26, 2015 at 7:33 AM, David Rowley <david.rowley@2ndquadrant.com> wrote:
On 26 September 2015 at 19:53, Andrus <kobruleht2@hot.ee> wrote:
Hi!


I'm looking for a way to delete records which do not have child rows on big tables where lot of rows needs to be deleted. Both tables have lot of other foreign key references.


Document headers are in omdok table:

   create table omdok ( dokumnr serial primary key, ... );

Document rows are in omrid table

   CREATE TABLE omrid
   (
     id serial NOT NULL,
     reanr serial NOT NULL,
     dokumnr integer NOT NULL,
     CONSTRAINT omrid_pkey PRIMARY KEY (id),
     CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
         REFERENCES omdok (dokumnr) MATCH SIMPLE
         ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
     ....
   );


I tried

   delete from omdok where dokumnr not in      (select dokumnr from omrid)

Query it is running currently 15 hours and is still running.
postgres.exe is using 50% CPU  all the time (this is 2 core CPU).

   explain delete from omdok where dokumnr not in      (select dokumnr from omrid)

returns:

   "Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
   "  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815 width=6)"
   "        Filter: (NOT (SubPlan 1))"
   "        SubPlan 1"
   "          ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
   "                ->  Seq Scan on omrid  (cost=0.00..77858.61 rows=1897261 width=4)"

- How to delete parents without child rows fast?

if you write the query as with a NOT EXISTS, rather than a NOT IN() it should complete much more quickly.

It's important to know that the semantics of NOT IN are likely not at all what you think:

For example, in the following query, would you expect it to return the row with a.a = 3 ?

select * from (values(1),(2),(3)) a(a) WHERE a NOT IN(SELECT b FROM (VALUES(NULL),(1),(2)) b(b));

The presence of NULL causes this to not behave the way you might think, yet it works exactly the way the SQL standard dictates.

You could think of this as equivalent to writing:

select * from (values(1),(2),(3)) a(a) WHERE a.a <> NULL AND a.a <> 1 AND a.a <> 2;

And since a.a <> NULL is 'unknown', this causes the entire WHERE clause to be false, therefore nothing matches.

For this reason PostgreSQL does not optimise NOT IN() the same way as it optimises NOT EXISTS().

If you write the query as:

delete from omdok where not exists(select 1 from omrid where omdok.dokumnr = omrid.dokumnr);

then you might see it execute in a few seconds. Perhaps you should consider cancelling the current query, perhaps perform a VACUUM on omdoc after cancelling, and then run the NOT EXISTS version instead.

I mocked up your tables locally, and populated them with the same number of records as your row estimates in the EXPLAIN you pasted and I got:

test=# delete from omdok where not exists (select 1 from omrid where omdok.dokumn = omrid.dokumnr);
DELETE 0
Time: 1698.233 ms

Whereas with the NOT IN() I cancelled it after 10 minutes.

Regards

David Rowley 

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services




--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

How to drop user if objects depend on it

From
"Andrus"
Date:
Hi!
 
Database idd owner is role idd_owner
Database has 2 data schemas: public and firma1.
User may have directly or indirectly assigned rights in this database and objects.
User is not owner of any object. It has only rights assigned to objects.
 
How to drop such  user ?
 
I tried
 
    revoke all on all tables in schema public,firma1 from "vantaa" cascade;
    revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
    revoke all on database idd from "vantaa" cascade;
    revoke all on all functions in schema public,firma1 from "vantaa" cascade;
    revoke all on schema public,firma1 from "vantaa" cascade;
    revoke idd_owner from "vantaa" cascade;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "vantaa";
    DROP ROLE if exists "vantaa"
 
but got error
   
    role "vantaa" cannot be dropped because some objects depend on it
    DETAIL:  privileges for schema public
 
in statement
 
    DROP ROLE if exists "vantaa"
 
How to fix this so that user can dropped ?
 
How to create sql or plpgsql method which takes user name as parameter and drops this user in all cases without dropping data ?
Or maybe there is some command or simpler commands in postgres ?
 
 
Using Postgres 9.1+

Ynt: How to drop user if objects depend on it

From
Neslisah Demirci
Date:


Hi ,


REASSIGN OWNED -- change the ownership of database objects owned by a database role.


REASSIGN OWNED BY old_role [, ...] TO new_role
You can create a new role then you just assign database objects depend on old role.
REASSIGN owned by old_role to new_role;

Then

DROP old_role;

Is this helpful?

Neslisah.


Gönderen: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> adına Andrus <kobruleht2@hot.ee>
Gönderildi: 07 Ekim 2015 Çarşamba 13:42
Kime: pgsql-general
Konu: [GENERAL] How to drop user if objects depend on it
 
Hi!
 
Database idd owner is role idd_owner
Database has 2 data schemas: public and firma1.
User may have directly or indirectly assigned rights in this database and objects.
User is not owner of any object. It has only rights assigned to objects.
 
How to drop such  user ?
 
I tried
 
    revoke all on all tables in schema public,firma1 from "vantaa" cascade;
    revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
    revoke all on database idd from "vantaa" cascade;
    revoke all on all functions in schema public,firma1 from "vantaa" cascade;
    revoke all on schema public,firma1 from "vantaa" cascade;
    revoke idd_owner from "vantaa" cascade;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "vantaa";
    DROP ROLE if exists "vantaa"
 
but got error
   
    role "vantaa" cannot be dropped because some objects depend on it
    DETAIL:  privileges for schema public
 
in statement
 
    DROP ROLE if exists "vantaa"
 
How to fix this so that user can dropped ?
 
How to create sql or plpgsql method which takes user name as parameter and drops this user in all cases without dropping data ?
Or maybe there is some command or simpler commands in postgres ?
 
 
Using Postgres 9.1+
Posted also in
 
sql - How to drop user in postgres if it has depending objects - Stack Overflow
Database idd owner is role idd_owner Database has 2 data schemas: public and firma1. User may have directly or indirectly assigned rights in this database and objects. User is not owner of any ob...

 
 
Andrus.

Re: How to drop user if objects depend on it

From
Thom Brown
Date:
On 7 October 2015 at 11:42, Andrus <kobruleht2@hot.ee> wrote:
> Hi!
>
> Database idd owner is role idd_owner
> Database has 2 data schemas: public and firma1.
> User may have directly or indirectly assigned rights in this database and
> objects.
> User is not owner of any object. It has only rights assigned to objects.
>
> How to drop such  user ?
>
> I tried
>
>     revoke all on all tables in schema public,firma1 from "vantaa" cascade;
>     revoke all on all sequences in schema public,firma1 from "vantaa"
> cascade;
>     revoke all on database idd from "vantaa" cascade;
>     revoke all on all functions in schema public,firma1 from "vantaa"
> cascade;
>     revoke all on schema public,firma1 from "vantaa" cascade;
>     revoke idd_owner from "vantaa" cascade;
>     ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES
> from "vantaa";
>     DROP ROLE if exists "vantaa"
>
> but got error
>
>     role "vantaa" cannot be dropped because some objects depend on it
>     DETAIL:  privileges for schema public
>
> in statement
>
>     DROP ROLE if exists "vantaa"
>
> How to fix this so that user can dropped ?
>
> How to create sql or plpgsql method which takes user name as parameter and
> drops this user in all cases without dropping data ?
> Or maybe there is some command or simpler commands in postgres ?

The objects can't be owned by nothing, so you will need to reassign ownership:

REASSIGN OWNED BY old_role TO new_role;

e.g.

REASSIGN OWNED BY vantaa TO postgres;

Then you can drop the role.

Regards

Thom


Re: How to drop user if objects depend on it

From
"Andrus"
Date:
Hi!

>The objects can't be owned by nothing, so you will need to reassign
>ownership:
>REASSIGN OWNED BY old_role TO new_role;
>e.g.
>REASSIGN OWNED BY vantaa TO postgres;
>Then you can drop the role.

User who deletes other users is not superuser. It is created using

CREATE ROLE admin LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
100;
GRANT idd_owner TO admin;


I tried

REASSIGN OWNED BY vantaa TO postgres;

and

REASSIGN OWNED BY vantaa TO idd_owner;

but got  error

permission denied to reassign objects .

How to fix ?

I can add some rights to user who invokes this command if this helps.

Andrus.



Re: How to drop user if objects depend on it

From
Adrian Klaver
Date:
On 10/07/2015 05:12 AM, Andrus wrote:
> Hi!
>
>> The objects can't be owned by nothing, so you will need to reassign
>> ownership:
>> REASSIGN OWNED BY old_role TO new_role;
>> e.g.
>> REASSIGN OWNED BY vantaa TO postgres;
>> Then you can drop the role.
>
> User who deletes other users is not superuser. It is created using
>
> CREATE ROLE admin LOGIN
>   NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
> 100;
> GRANT idd_owner TO admin;
>
>
> I tried

So to be clear admin is doing the below, correct?

>
> REASSIGN OWNED BY vantaa TO postgres;
>
> and
>
> REASSIGN OWNED BY vantaa TO idd_owner;
>
> but got  error
>
> permission denied to reassign objects .

Is the above a blanket error or does it mention specific objects?

>
> How to fix ?

What you are trying to do is reverse what you did to get the present
setup. Do you have a record/script that shows what you did to create the
role and assign it to the objects?

>
> I can add some rights to user who invokes this command if this helps.
>
> Andrus.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: How to drop user if objects depend on it

From
Melvin Davidson
Date:
Can you connect as user postgres? IE: psql -U postgres -d <yourdb>

If so, then you should have the ability to execute the commands without any problem.

On Wed, Oct 7, 2015 at 9:53 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/07/2015 05:12 AM, Andrus wrote:
Hi!

The objects can't be owned by nothing, so you will need to reassign
ownership:
REASSIGN OWNED BY old_role TO new_role;
e.g.
REASSIGN OWNED BY vantaa TO postgres;
Then you can drop the role.

User who deletes other users is not superuser. It is created using

CREATE ROLE admin LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION CONNECTION LIMIT
100;
GRANT idd_owner TO admin;


I tried

So to be clear admin is doing the below, correct?


REASSIGN OWNED BY vantaa TO postgres;

and

REASSIGN OWNED BY vantaa TO idd_owner;

but got  error

permission denied to reassign objects .

Is the above a blanket error or does it mention specific objects?


How to fix ?

What you are trying to do is reverse what you did to get the present setup. Do you have a record/script that shows what you did to create the role and assign it to the objects?


I can add some rights to user who invokes this command if this helps.

Andrus.




--
Adrian Klaver
adrian.klaver@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to drop user if objects depend on it

From
"Andrus"
Date:
Hi!

> I tried
>So to be clear admin is doing the below, correct?

Yes. I copied provided user definition which invokes delete command from
pgadmin
code window for this user .

> permission denied to reassign objects .
>Is the above a blanket error or does it mention specific objects?

postgres log file contains two lines:

ERROR:  permission denied to reassign objects
STATEMENT:  reassign owned by vantaa to postgres

No objects are mentioned.
I can change log level if this helps.

>What you are trying to do is reverse what you did to get the present setup.
>Do you have a record/script that shows what you did to create the role and
>assign it to the objects?

It was something like:

create script:

CREATE ROLE vantaa;
grant idd_owner to vantaa;

reset roles script:

revoke all on all tables in schema public,firma1 from vantaa cascade;
revoke all on all sequences in schema public,firma1 from vantaa cascade;
revoke all on database idd  from public,firma1 cascade;
revoke all on all functions in schema public,firma1 from vantaa cascade;
revoke all on schema public,firma1 from vantaa cascade;
revoke idd_owner  from vantaa cascade;
ALTER ROLE vantaa inherit NOCREATEROLE NOCREATEDB NOLOGIN;

grant all on all tables in schema public,firma1 to vantaa;
grant all on all sequences in schema public,firma1 to vantaa;
grant all on database idd  to vantaa;
grant all on schema public,firma1 to vantaa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;
-- Restrict some tables:
revoke all on  kasutaja,kaspriv,logifail from vantaa cascade;
grant select on kaspriv,kasutaja to vantaa;
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup)
on kasutaja to vantaa;
grant insert on logifail to vantaa;

Andrus.



Re: How to drop user if objects depend on it

From
"Andrus"
Date:
Hi!

>Can you connect as user postgres? IE: psql -U postgres -d <yourdb>

Applicaton has admin users which should be able to delete other users.
Those users dont have superuser rights.

I can connect as user postgres for testing only.
I'm looking for a way to delete users without superuser right.

>If so, then you should have the ability to execute the commands without any
>problem.

I tried in database ktp :

reassign owned by farukkugay to postgres;
drop user farukkugay ;

This causes error

ERROR:  role "farukkugay" cannot be dropped because some objects depend on
it
DETAIL:  privileges for schema public

So even superuser cannot delete.

Andrus.



Re: How to drop user if objects depend on it

From
Melvin Davidson
Date:
No. You need to be a superuser to reassign objects unless you own the object.
You must also be a superuser to drop roles.

So.
1. first connect as user postgres
2. REASSIGN all the tables owned by the missing user first.
3. Then you can drop the missing user AFTER you have reassigned all the objects they own.

On Wed, Oct 7, 2015 at 10:48 AM, Andrus <kobruleht2@hot.ee> wrote:
Hi!

Can you connect as user postgres? IE: psql -U postgres -d <yourdb>

Applicaton has admin users which should be able to delete other users.
Those users dont have superuser rights.

I can connect as user postgres for testing only.
I'm looking for a way to delete users without superuser right.

If so, then you should have the ability to execute the commands without any problem.

I tried in database ktp :

reassign owned by farukkugay to postgres;
drop user farukkugay ;

This causes error

ERROR:  role "farukkugay" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public

So even superuser cannot delete.

Andrus.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to drop user if objects depend on it

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
> vantaa;

I am not sure that REASSIGN OWNED will get rid of default-privilege
specifiers --- you might have to reverse this step separately.

In general, REASSIGN OWNED has to be done by a role that has privileges
of (is a member of) both the source and target roles.  Superusers are
considered members of all roles, so that's how come it works for them.

            regards, tom lane


Re: How to drop user if objects depend on it

From
"Andrus"
Date:
Hi!

>No. You need to be a superuser to reassign objects unless you own the
>object.
>1. first connect as user postgres
>2. REASSIGN all the tables owned by the missing user first.
>3. Then you can drop the missing user AFTER you have reassigned all the
>objects they own.

Script

reassign owned by farukkugay to postgres;
drop user farukkugay ;

is running by superuser but it still causes the error.


>You must also be a superuser to drop roles.

Non-superuser creates roles, assigns rights and drop users using scripts
which I provided.
Those scripts work OK on most cases.

For some users, vantaa and farukkugan  delete script causes error which I
described. For farukkugan it occurs also if running under superuser.

So it looks like it should be possible for non-superusers also.

Andrus.



Re: How to drop user if objects depend on it

From
Melvin Davidson
Date:
Andrus,

>is running by superuser but it still causes the error.

That does not sound right. Please verify you are running as user postgres with:

SELECT current_user;

Then make sure postgres is still a superuser with:

SELECT rolname as user,
       CASE WHEN rolcanlogin THEN 'user'
            ELSE 'group'
       END,
       CASE WHEN rolsuper THEN 'SUPERUSER'
            ELSE 'normal'
        END AS super
  FROM pg_authid
WHERE rolname = 'postgres';

If you still get errors, then please show the exact error to us.


On Wed, Oct 7, 2015 at 11:11 AM, Andrus <kobruleht2@hot.ee> wrote:
Hi!

No. You need to be a superuser to reassign objects unless you own the object.
1. first connect as user postgres
2. REASSIGN all the tables owned by the missing user first.
3. Then you can drop the missing user AFTER you have reassigned all the objects they own.

Script

reassign owned by farukkugay to postgres;
drop user farukkugay ;

is running by superuser but it still causes the error.


You must also be a superuser to drop roles.

Non-superuser creates roles, assigns rights and drop users using scripts which I provided.
Those scripts work OK on most cases.

For some users, vantaa and farukkugan  delete script causes error which I described. For farukkugan it occurs also if running under superuser.

So it looks like it should be possible for non-superusers also.

Andrus.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: How to drop user if objects depend on it

From
Tom Lane
Date:
I wrote:
> "Andrus" <kobruleht2@hot.ee> writes:
>> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
>> vantaa;

> I am not sure that REASSIGN OWNED will get rid of default-privilege
> specifiers --- you might have to reverse this step separately.

A little further review shows that DROP OWNED is the way to get rid of
leftover privileges.  So in general you need to do REASSIGN OWNED to move
the ownership of objects, then DROP OWNED to get rid of privileges granted
on non-owned objects, before you can drop a role.

This is documented, but only in passing in the REASSIGN OWNED man page.
I think it needs to be explained more prominently.  Will see about making
that happen.

            regards, tom lane


Re: Ynt: How to drop user if objects depend on it

From
Jerry Sievers
Date:
Neslisah Demirci <neslisah.demirci@markafoni.com> writes:

> Hi ,
>
> REASSIGN OWNED -- change the ownership of database objects owned by a database role.
>
> REASSIGN OWNED BY old_role [, ...] TO new_role
>
> You can create a new role then you just assign database objects depend on old role.
> REASSIGN owned by old_role to new_role;
>
> Then
>
> DROP old_role;
>
> Is this helpful?

It might be if were accurate :-)

Permissions are not reassignable.

drop owned by foo_role;

Sometimes to be on the safe side, just in case foo_role did own objects
that you'd rather not drop...

create role foo_orphaned_objects_role;
reassign owned by foo_role to foo_orphaned_objects_role;
drop owned by foo_role
drop role foo_role;

Note that you may have to repeat this for each DB in a given cluster if
foo_role owns things or is direct grant recipient.

>
> Neslisah.
>
>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> G?nderen: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> ad?na Andrus <kobruleht2@hot.ee>
> G?nderildi: 07 Ekim 2015 ?ar?amba 13:42
> Kime: pgsql-general
> Konu: [GENERAL] How to drop user if objects depend on it
>
> Hi!
>
> Database idd owner is role idd_owner
> Database has 2 data schemas: public and firma1.
> User may have directly or indirectly assigned rights in this database and objects.
> User is not owner of any object. It has only rights assigned to objects.
>
> How to drop such  user ?
>
> I tried
>
>     revoke all on all tables in schema public,firma1 from "vantaa" cascade;
>     revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
>     revoke all on database idd from "vantaa" cascade;
>     revoke all on all functions in schema public,firma1 from "vantaa" cascade;
>     revoke all on schema public,firma1 from "vantaa" cascade;
>     revoke idd_owner from "vantaa" cascade;
>     ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "vantaa";
>     DROP ROLE if exists "vantaa"
>
> but got error
>
>     role "vantaa" cannot be dropped because some objects depend on it
>     DETAIL:  privileges for schema public
>
> in statement
>
>     DROP ROLE if exists "vantaa"
>
> How to fix this so that user can dropped ?
>
> How to create sql or plpgsql method which takes user name as parameter and drops this user in all cases without
droppingdata ? 
> Or maybe there is some command or simpler commands in postgres ?
>
> Using Postgres 9.1+
> Posted also in
>
> http://stackoverflow.com/questions/32988702/how-to-drop-user-in-all-cases-in-postgres
>
> [apple-touch-icon] sql - How to drop user in postgres if it has depending objects - Stack Overflow
                                                  
>                    Database idd owner is role idd_owner Database has 2 data schemas: public and firma1. User may have
directlyor indirectly assigned rights in this     
>                    database and objects. User is not owner of any ob...
                                                  
>                    Devam?n? okuyun...
                                                  
>
> Andrus.
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: How to drop user if objects depend on it

From
"Andrus"
Date:
>> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
>> vantaa;
>I am not sure that REASSIGN OWNED will get rid of default-privilege
>specifiers --- you might have to reverse this step separately.
>In general, REASSIGN OWNED has to be done by a role that has privileges
>of (is a member of) both the source and target roles.  Superusers are
>considered members of all roles, so that's how come it works for them.

I tried as superuser:

reassign owned by farukkugay to postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma2 revoke all ON TABLES from
farukkugay;
drop user farukkugay ;

but got error

ERROR: role "farukkugay" cannot be dropped because some objects depend on it
SQL state: 2BP01
Detail: privileges for schema public

How to to delete user ?

Andrus.



Re: How to drop user if objects depend on it

From
Adrian Klaver
Date:
On 10/07/2015 09:50 AM, Andrus wrote:
>>> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
>>> vantaa;
>> I am not sure that REASSIGN OWNED will get rid of default-privilege
>> specifiers --- you might have to reverse this step separately.
>> In general, REASSIGN OWNED has to be done by a role that has privileges
>> of (is a member of) both the source and target roles.  Superusers are
>> considered members of all roles, so that's how come it works for them.
>
> I tried as superuser:
>
> reassign owned by farukkugay to postgres;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma2 revoke all ON TABLES
> from farukkugay;
> drop user farukkugay ;
>
> but got error
>
> ERROR: role "farukkugay" cannot be dropped because some objects depend
> on it
> SQL state: 2BP01
> Detail: privileges for schema public


Above you revoked DEFAULT PRIVILEGES which applies to objects created in
future. You still probably have PRIVILEGES assigned to farukkugay on the
public schema. So in psql do:

\dn+ public

>
> How to to delete user ?
>
> Andrus.


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: How to drop user if objects depend on it

From
"Andrus"
Date:
>A little further review shows that DROP OWNED is the way to get rid of
>leftover privileges.  So in general you need to do REASSIGN OWNED to move
>the ownership of objects, then DROP OWNED to get rid of privileges granted
>on non-owned objects, before you can drop a role.

I tried this in database mydb using script below but still got error

ERROR:  role "vantaa" cannot be dropped because some objects depend on it
DETAIL:  privileges for database mydb

How to drop role?

Andrus.

set local role admin; -- admin is not superuser but is member of
mydb_owner
CREATE ROLE vantaa;
grant mydb_owner to vantaa;

revoke all on all tables in schema public,firma1 from vantaa cascade;
revoke all on all sequences in schema public,firma1 from vantaa cascade;
revoke all on database mydb  from vantaa cascade;
revoke all on all functions in schema public,firma1 from vantaa cascade;
revoke all on schema public,firma1 from vantaa cascade;
revoke mydb_owner  from vantaa cascade;
ALTER ROLE vantaa inherit NOCREATEROLE NOCREATEDB NOLOGIN;

grant all on all tables in schema public,firma1 to vantaa;
grant all on all sequences in schema public,firma1 to vantaa;
grant all on database mydb  to vantaa;
grant all on schema public,firma1 to vantaa;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  GRANT all ON TABLES TO
vantaa;
revoke all on  kasutaja,kaspriv,logifail from vantaa cascade;
grant select on kaspriv,kasutaja to vantaa;
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup)
on kasutaja to vantaa;
grant insert on logifail to vantaa;

GRANT vantaa TO admin;
reassign owned by vantaa to mydb_owner;
drop owned by vantaa;
drop user vantaa;



Re: How to drop user if objects depend on it

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
>> A little further review shows that DROP OWNED is the way to get rid of
>> leftover privileges.  So in general you need to do REASSIGN OWNED to move
>> the ownership of objects, then DROP OWNED to get rid of privileges granted
>> on non-owned objects, before you can drop a role.

> I tried this in database mydb using script below but still got error
> ERROR:  role "vantaa" cannot be dropped because some objects depend on it
> DETAIL:  privileges for database mydb

The example script works for me.  What PG version are you running?  I have
a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
the past.

            regards, tom lane


Re: How to drop user if objects depend on it

From
"Andrus"
Date:
>The example script works for me.  What PG version are you running?  I have
>a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
>the past.

I'm using

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit"

In   "PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit" it works.

It looks like in 9.1  reassign owned should replaced with revoke commands.

Andrus.



Re: How to drop user if objects depend on it

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
>> The example script works for me.  What PG version are you running?  I have
>> a vague recollection that we've fixed bugs-of-omission in DROP OWNED in
>> the past.

> I'm using
> "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
> (Debian 4.4.5-8) 4.4.5, 64-bit"

Ah.  I believe this is fixed in 9.1.7 and later.  In prior versions DROP
OWNED neglects to revoke permissions on shared objects (databases,
tablespaces).

Considering that 9.1.x is up to 9.1.19 as of tomorrow, you really ought
to think about doing a minor-version upgrade.  We don't put out bug fix
releases just for idle amusement.

            regards, tom lane


How to use row values as function parameters

From
"Andrus"
Date:
Table ko should used to pass parameters to crtKAIVE() function.
ko has always single row.

I tried

CREATE or replace FUNCTION public.crtKAIVE(
_doktyybid text default 'GVY'
)
RETURNS TABLE (
id integer
)
AS $f_crkaive$
select 1
$f_crkaive$ LANGUAGE sql STABLE;

create temp  table ko ( doktyyp text ) on commit drop;
insert into ko values ('G');
select * from ko, crtkaive(ko.doktyyp)

but got error

    ERROR:  function expression in FROM cannot refer to other relations of
same query level

How to fix this so that ko can used to pass parameters to crtkaive ?

Posted also in

http://stackoverflow.com/questions/37231624/how-to-use-table-row-values-as-function-parameters

Andrus.



Re: How to use row values as function parameters

From
"David G. Johnston"
Date:
On Sat, May 14, 2016 at 4:47 PM, Andrus <kobruleht2@hot.ee> wrote:
Table ko should used to pass parameters to crtKAIVE() function.
ko has always single row.

I tried

CREATE or replace FUNCTION public.crtKAIVE(
_doktyybid text default 'GVY'
)
RETURNS TABLE (
id integer
)
AS $f_crkaive$
select 1
$f_crkaive$ LANGUAGE sql STABLE;

create temp  table ko ( doktyyp text ) on commit drop;
insert into ko values ('G');
select * from ko, crtkaive(ko.doktyyp)

but got error

   ERROR:  function expression in FROM cannot refer to other relations of same query level

How to fix this so that ko can used to pass parameters to crtkaive ?

Posted also in

http://stackoverflow.com/questions/37231624/how-to-use-table-row-values-as-function-parameters

​Upgrade your version and make use of the LATERAL feature.

Use a CTE and move the function call to the select list - then explode the result in the main query.

Basically:

WITH func_cte AS (
SELECT func_call(tbl)
FROM tbl
)​
 
​SELECT (func_call).*
FROM func_cte;
​The parens are required to make the parser see func_call as a column name instead of a table name.​

David J.


Re: How to use row values as function parameters

From
Adrian Klaver
Date:
On 05/14/2016 01:47 PM, Andrus wrote:
> Table ko should used to pass parameters to crtKAIVE() function.
> ko has always single row.
>
> I tried
>
> CREATE or replace FUNCTION public.crtKAIVE(
> _doktyybid text default 'GVY'
> )
> RETURNS TABLE (
> id integer
> )
> AS $f_crkaive$
> select 1
> $f_crkaive$ LANGUAGE sql STABLE;
>
> create temp  table ko ( doktyyp text ) on commit drop;
> insert into ko values ('G');
> select * from ko, crtkaive(ko.doktyyp)

test=# select *, crtkaive(ko.doktyyp) from ko;


  doktyyp | crtkaive


---------+----------


  G       |        1

>
> but got error
>
>    ERROR:  function expression in FROM cannot refer to other relations
> of same query level
>
> How to fix this so that ko can used to pass parameters to crtkaive ?
>
> Posted also in
>
> http://stackoverflow.com/questions/37231624/how-to-use-table-row-values-as-function-parameters
>
>
> Andrus.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: How to use row values as function parameters

From
"Andrus"
Date:
Hi!
 
Thank you.
 
>
Use a CTE and move the function call to the select list - then explode the result in the main query.
>Basically:
>WITH func_cte AS (
>SELECT func_call(tbl)
FROM tbl
)​
 
​>SELECT (func_call).*
>FROM func_cte;
​>​The parens are required to make the parser see func_call as a column name instead of a table name.​
 
I tried in 9.5
 
CREATE or replace FUNCTION crtKAIVE(
_doktyybid text default 'GVY'
)
RETURNS TABLE (
id integer
)
AS $f_crkaive$
select 1
$f_crkaive$ LANGUAGE sql STABLE;
 
create temp  table ko ( doktyyp text ) on commit drop;
insert into ko values ('G');
 
 
WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)​
​SELECT (crtKAIVE).*
FROM func_cte;
 
but got strange error
 
 
ERROR:  syntax error at or near "​"
LINE 18: )​
 
How to fix ?
 
Andrus.
 

Re: How to use row values as function parameters

From
Adrian Klaver
Date:
On 05/14/2016 02:13 PM, Andrus wrote:
> Hi!
>
> Thank you.
>
>>
> Use a CTE and move the function call to the select list - then explode
> the result in the main query.
>>Basically:
>>WITH func_cte AS (
>>SELECT func_call(tbl)
> FROM tbl
> )​
>
> ​>SELECT (func_call).*
>>FROM func_cte;
> ​>​The parens are required to make the parser see func_call as a column
> name instead of a table name.​
>
> I tried in 9.5
>
> CREATE or replace FUNCTION crtKAIVE(
> _doktyybid text default 'GVY'
> )
> RETURNS TABLE (
> id integer
> )
> AS $f_crkaive$
> select 1
> $f_crkaive$ LANGUAGE sql STABLE;
>
> create temp  table ko ( doktyyp text ) on commit drop;
> insert into ko values ('G');
>
>
> WITH func_cte AS (
> SELECT crtKAIVE(ko.doktyyp)
> FROM ko
> )​
> ​SELECT (crtKAIVE).*
> FROM func_cte;
>
> but got strange error
>
>
> ERROR:  syntax error at or near "​"
> LINE 18: )​
>
> How to fix ?

I am guessing you did the same thing I did, copy and pasted David's
example and modified. Seems there are some 'hidden' characters present.
Re-entering the code from scratch got this:

test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT (crtKAIVE).* from func_cte
;
ERROR:  type integer is not composite

Doing the below worked:

test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT * from func_cte
;
  crtkaive
----------
         1
(1 row)

test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT crtKAIVE from func_cte
;
  crtkaive
----------
         1
(1 row)

>
> Andrus.
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: How to use row values as function parameters

From
"David G. Johnston"
Date:
On Sat, May 14, 2016 at 5:34 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 05/14/2016 02:13 PM, Andrus wrote:
Hi!

Thank you.


Use a CTE and move the function call to the select list - then explode
the result in the main query.
Basically:
WITH func_cte AS (
SELECT func_call(tbl)
FROM tbl
)​

​>SELECT (func_call).*
FROM func_cte;
​>​The parens are required to make the parser see func_call as a column
name instead of a table name.​

I tried in 9.5

CREATE or replace FUNCTION crtKAIVE(
_doktyybid text default 'GVY'
)
RETURNS TABLE (
id integer
)
AS $f_crkaive$
select 1
$f_crkaive$ LANGUAGE sql STABLE;

create temp  table ko ( doktyyp text ) on commit drop;
insert into ko values ('G');


WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)​
​SELECT (crtKAIVE).*
FROM func_cte;

but got strange error


ERROR:  syntax error at or near "​"
LINE 18: )​

How to fix ?

I am guessing you did the same thing I did, copy and pasted David's example and modified. Seems there are some 'hidden' characters present. Re-entering the code from scratch got this:

test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT (crtKAIVE).* from func_cte
;
ERROR:  type integer is not composite


​There must be some kind of implicit conversion being done here.  Since the function is defined as returning a single column the resultant column is non-composite and thus doesn't accept the ".*" construct.  If the function were to return multiple columns would need to use the CTE to avoid multiple evaluation during the ".*" expansion.  With a single column it doesn't matter.

But if you are going to use 9.5 the original query should just work - <FROM tbl, func(tbl)> is equivalent to <FROM tbl LATERAL func(tbl)>​ (going from memory...) and regardless the lateral form can be made to work in 9.5 whatever the syntax.

David J.


How to get correct local time

From
"Andrus"
Date:
    select current_time at time zone 'GMT-2'

returns

"11:54:40.22045+02"

but correct local time in Windows is one hour different:

12:54

How to get correct local time ?


Using

"PostgreSQL 9.6.0, compiled by Visual C++ build 1800, 32-bit"

with  standard postgresql.conf file in Windows 10
Same issue occurs also in ealier Postgres and in earlier windows.

Server time in Windows is correct.

Daylight saving time was changed by one hour a week ago.
Maybe postgres didnt recognized it.

Posted also in

http://stackoverflow.com/questions/43090328/how-to-return-correct-local-time-in-postgres

Andrus.



Re: How to get correct local time

From
Vitaly Burovoy
Date:
On 3/29/17, Andrus <kobruleht2@hot.ee> wrote:
>
>     select current_time at time zone 'GMT-2'
>
> returns
>
> "11:54:40.22045+02"
>
> but correct local time in Windows is one hour different:
>
> 12:54
>
> How to get correct local time ?
>
>
> Using
>
> "PostgreSQL 9.6.0, compiled by Visual C++ build 1800, 32-bit"
>
> with  standard postgresql.conf file in Windows 10
> Same issue occurs also in ealier Postgres and in earlier windows.
>
> Server time in Windows is correct.
>
> Daylight saving time was changed by one hour a week ago.
> Maybe postgres didnt recognized it.
>
> Posted also in
>
> http://stackoverflow.com/questions/43090328/how-to-return-correct-local-time-in-postgres
>
> Andrus.
>

Try

SELECT now();

or

SELECT current_timestamp;

--
Best regards,
Vitaly Burovoy


Re: How to get correct local time

From
Vitaly Burovoy
Date:
On 3/29/17, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> On 3/29/17, Andrus <kobruleht2@hot.ee> wrote:
>>
>>     select current_time at time zone 'GMT-2'
>>
>> returns
>>
>> "11:54:40.22045+02"
>>
>> but correct local time in Windows is one hour different:
>>
>> 12:54
>>
>> How to get correct local time ?
>>
>>
>> Using
>>
>> "PostgreSQL 9.6.0, compiled by Visual C++ build 1800, 32-bit"
>>
>> with  standard postgresql.conf file in Windows 10
>> Same issue occurs also in ealier Postgres and in earlier windows.
>>
>> Server time in Windows is correct.
>>
>> Daylight saving time was changed by one hour a week ago.
>> Maybe postgres didnt recognized it.
>>
>> Posted also in
>>
>> http://stackoverflow.com/questions/43090328/how-to-return-correct-local-time-in-postgres
>>
>> Andrus.
>>
>
> Try
>
> SELECT now();
>
> or
>
> SELECT current_timestamp;

Oops. I missed you need a time part only.

Why do you use "at time zone"  with a constant shift from GMT? If you
want to get your local zone, use just

SELECT current_time;

If you want to get current time in a specified part of the world, use
a region name:

postgres=# SELECT current_time AT TIME ZONE 'Asia/Tokyo', current_time
AT TIME ZONE 'US/Samoa';
      timezone      |      timezone
--------------------+--------------------
 20:10:57.924534+09 | 00:10:57.924534-11

Postgres uses set of rules when and how a shift from GMT changed (or
will change by knowledge at the current moment) for the concrete
region.

--
Best regards,
Vitaly Burovoy


Re: How to get correct local time

From
Adrian Klaver
Date:
On 03/29/2017 03:03 AM, Andrus wrote:
>
>    select current_time at time zone 'GMT-2'
>
> returns
>
> "11:54:40.22045+02"
>
> but correct local time in Windows is one hour different:
>
> 12:54
>
> How to get correct local time ?

What time zone are you in?

What is the TimeZone  set to in postgresql.conf?

>
>
> Using
>
> "PostgreSQL 9.6.0, compiled by Visual C++ build 1800, 32-bit"
>
> with  standard postgresql.conf file in Windows 10
> Same issue occurs also in ealier Postgres and in earlier windows.
>
> Server time in Windows is correct.
>
> Daylight saving time was changed by one hour a week ago.
> Maybe postgres didnt recognized it.
>
> Posted also in
>
> http://stackoverflow.com/questions/43090328/how-to-return-correct-local-time-in-postgres
>
>
> Andrus.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com