Re: Help needed in skipping column for copy command

Hi,
I'm successfully executing the below:

create role developer login;
alter role developer set default_tablespace=dev;
alter role developer set search_path=dev,staging, esave, support, email,
public;

grant select on members to developer;
grant create on schema dev to developer;

However, when I do this:
psql (8.4.5, server 8.4.2)
Type "help" for help.

esave_dw=> \d members
Did not find any relation named "members".
esave_dw=>
esave_dw=> \d esave.members
Table "esave.members"
Column | Type | Modifiers
---------------------+-----------------------------+-----------
memberid | numeric | not null
etc....

How can I get this so I don't have to preface the \d with the schema
name every time?

Thanks.
Tony

On Thursday 30 December 2010 2:14:23 pm Tony Capobianco wrote:
> Hi,
> I'm successfully executing the below:
>
> create role developer login;
> alter role developer set default_tablespace=dev;
> alter role developer set search_path=dev,staging, esave, support, email,
> public;
>
> grant select on members to developer;
> grant create on schema dev to developer;
>
> However, when I do this:
> psql (8.4.5, server 8.4.2)
> Type "help" for help.
>
> esave_dw=> \d members
> Did not find any relation named "members".
> esave_dw=>
> esave_dw=> \d esave.members
> Table "esave.members"
> Column | Type | Modifiers
> ---------------------+-----------------------------+-----------
> memberid | numeric | not null
> etc....
>
> How can I get this so I don't have to preface the \d with the schema
> name every time?
>
> Thanks.
> Tony

Did you log out and then back in as developer? Per:
http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html
"The remaining variants change a role's session default for a configuration
variable, either for all databases or, when the IN DATABASE clause is
specified, only for sessions in the named database. Whenever the role
subsequently starts a new session, the specified value becomes the session
default, overriding whatever setting is present in postgresql.conf or has been
received from the postgres command line."

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> On 31 December 2010 05:14, Tony Capobianco <tcapobianco(at)prospectiv(dot)com>
> wrote:
>> esave_dw=> \d members
>> Did not find any relation named "members".
>> esave_dw=>
>> esave_dw=> \d esave.members
>>                     Table "esave.members"
>>       Column        |            Type             | Modifiers
>> ---------------------+-----------------------------+-----------
>>  memberid            | numeric                     | not null
>>  etc....
>>
>> How can I get this so I don't have to preface the \d with the schema
>> name every time?
>
> Hi Tony, you should changes the default search_path for the specified
> users.
> http://sql-info.de/postgresql/schemas.html
> take a look at practical schema usage section.
>

Gibransyah,
That did the trick! Thanks for your help. I modified my role name from
developer to dev since I already have a dev schema. Here's the steps I
ran below to get it working:

create role dev login;
alter role dev set default_tablespace=dev;
alter role dev set search_path=dev,staging, esave, support, email,public;

grant usage on schema esave to dev;
grant usage on schema dev to dev;
grant select on members to dev;
grant create on schema dev to dev;
grant create on tablespace dev to dev;

I am a little confused as to why I had to grant usage & create on dev to
dev since it's both the dev role's default_tablespace and has a schema
named after it. Either way, this corrects my issue. Thanks for your
help!

Tony

On Friday 31 December 2010 8:22:23 am tcapobianco(at)prospectiv(dot)com wrote:
> > On 31 December 2010 05:14, Tony Capobianco <tcapobianco(at)prospectiv(dot)com>
> >
> > wrote:
> >> esave_dw=> \d members
> >> Did not find any relation named "members".
> >> esave_dw=>
> >> esave_dw=> \d esave.members
> >>                     Table "esave.members"
> >>       Column        |            Type             | Modifiers
> >> ---------------------+-----------------------------+-----------
> >>  memberid            | numeric                     | not null
> >>  etc....
> >>
> >> How can I get this so I don't have to preface the \d with the schema
> >> name every time?
> >
> > Hi Tony, you should changes the default search_path for the specified
> > users.
> > http://sql-info.de/postgresql/schemas.html
> > take a look at practical schema usage section.
>
> Gibransyah,
> That did the trick! Thanks for your help. I modified my role name from
> developer to dev since I already have a dev schema. Here's the steps I
> ran below to get it working:
>
> create role dev login;
> alter role dev set default_tablespace=dev;
> alter role dev set search_path=dev,staging, esave, support, email,public;
>
> grant usage on schema esave to dev;
> grant usage on schema dev to dev;
> grant select on members to dev;
> grant create on schema dev to dev;
> grant create on tablespace dev to dev;
>
> I am a little confused as to why I had to grant usage & create on dev to
> dev since it's both the dev role's default_tablespace and has a schema
> named after it. Either way, this corrects my issue. Thanks for your
> help!
>
> Tony

First tablespace != schema. From here
http://www.postgresql.org/docs/9.0/interactive/manage-ag-tablespaces.html:

"Tablespaces in PostgreSQL allow database administrators to define locations in
the file system where the files representing database objects can be stored.
Once created, a tablespace can be referred to by name when creating database
objects.

By using tablespaces, an administrator can control the disk layout of a
PostgreSQL installation. This is useful in at least two ways. First, if the
partition or volume on which the cluster was initialized runs out of space and
cannot be extended, a tablespace can be created on a different partition and
used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern
of database objects to optimize performance. For example, an index which is
very heavily used can be placed on a very fast, highly available disk, such as
an expensive solid state device. At the same time a table storing archived data
which is rarely used or not performance critical could be stored on a less
expensive, slower disk system. "

Second, from the schema docs if you want 'user' schemas than

"The value for search_path must be a comma-separated list of schema names. If
one of the list items is the special value $user, then the schema having the
name returned by SESSION_USER is substituted, if there is such a schema. (If
not, $user is ignored.) "

"The default value for this parameter is '"$user", public' (where the second
part will be ignored if there is no schema named public). This supports shared
use of a database (where no users have private schemas, and all share use of
public), private per-user schemas, and combinations of these. Other effects can
be obtained by altering the default search path setting, either globally or
per-user. "

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Friday 31 December 2010 8:22:23 am tcapobianco(at)prospectiv(dot)com wrote:
> > On 31 December 2010 05:14, Tony Capobianco <tcapobianco(at)prospectiv(dot)com>
> >
> > wrote:
> >> esave_dw=> \d members
> >> Did not find any relation named "members".
> >> esave_dw=>
> >> esave_dw=> \d esave.members
> >>                     Table "esave.members"
> >>       Column        |            Type             | Modifiers
> >> ---------------------+-----------------------------+-----------
> >>  memberid            | numeric                     | not null
> >>  etc....
> >>
> >> How can I get this so I don't have to preface the \d with the schema
> >> name every time?
> >
> > Hi Tony, you should changes the default search_path for the specified
> > users.
> > http://sql-info.de/postgresql/schemas.html
> > take a look at practical schema usage section.
>
> Gibransyah,
> That did the trick! Thanks for your help. I modified my role name from
> developer to dev since I already have a dev schema. Here's the steps I
> ran below to get it working:
>
> create role dev login;
> alter role dev set default_tablespace=dev;
> alter role dev set search_path=dev,staging, esave, support, email,public;
>
> grant usage on schema esave to dev;
> grant usage on schema dev to dev;
> grant select on members to dev;
> grant create on schema dev to dev;
> grant create on tablespace dev to dev;
>
> I am a little confused as to why I had to grant usage & create on dev to
> dev since it's both the dev role's default_tablespace and has a schema
> named after it. Either way, this corrects my issue. Thanks for your
> help!
>
> Tony

In my previous message I forgot to add the following.

The set commands and grants are not linked. Setting something does not
necessarily confer privileges for that object. The search_path for instance. It
really only sets up the search order for unqualified object names. What you can
see or do with those objects is determined by the privileges on those objects.
Those privileges come from either the role that created the object or are
GRANT(ed) by a sufficiently privileged role to another role.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Hi,
I need helping converting following db2 function in postgresql function.
Any pointer will be great help in proceeding me ahead.

CREATE FUNCTION in_liststring ( string CLOB(64K) )
RETURNS TABLE ( ordinal INTEGER, index INTEGER )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( VALUES ( 0, 0 )
UNION ALL
SELECT ordinal+1, COALESCE(NULLIF(
-- find the next delimiter ','
LOCATE(',', string, index+1), 0),
LENGTH(string)+1)
FROM t
-- to prevent a warning condition for infinite
-- recursions, we add the explicit upper
-- boundary for the &quot;ordinal&quot; values
WHERE ordinal < 10000 AND
-- terminate if there are no further delimiters
-- remaining
LOCATE(',', string, index+1) <> 0 )
SELECT ordinal, index
FROM t
UNION ALL
-- add indicator for the end of the string
SELECT MAX(ordinal)+1, LENGTH(string)+1
FROM t
;

commit;

DROP FUNCTION INSTRTBL;

CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
RETURNS TABLE ( INSTRTBL CLOB(64K) )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( SELECT ordinal, index
FROM TABLE ( in_liststring(string) ) AS x )
SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
-- the join below makes sure that we have the lower and
-- upper index where we can find each of the ',' delimiters
-- that are separating the INSTRTBL. (For this, we exploit
-- the additional indexes pointing to the beginning and end
-- of the string.)
FROM t AS t1 JOIN t AS t2 ON
( t2.ordinal = t1.ordinal+1 )
;

2011/1/11 Amar Dhole <adhole(at)tibco(dot)com>

> Hi,
> I need helping converting following db2 function in postgresql function.
> Any pointer will be great help in proceeding me ahead.
>
> CREATE FUNCTION in_liststring ( string CLOB(64K) )
> RETURNS TABLE ( ordinal INTEGER, index INTEGER )
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
> RETURN
> WITH t(ordinal, index) AS
> ( VALUES ( 0, 0 )
> UNION ALL
> SELECT ordinal+1, COALESCE(NULLIF(
> -- find the next delimiter ','
> LOCATE(',', string, index+1), 0),
> LENGTH(string)+1)
> FROM t
> -- to prevent a warning condition for infinite
> -- recursions, we add the explicit upper
> -- boundary for the &quot;ordinal&quot; values
> WHERE ordinal < 10000 AND
> -- terminate if there are no further delimiters
> -- remaining
> LOCATE(',', string, index+1) <> 0 )
> SELECT ordinal, index
> FROM t
> UNION ALL
> -- add indicator for the end of the string
> SELECT MAX(ordinal)+1, LENGTH(string)+1
> FROM t
> ;
>
> commit;
>
> DROP FUNCTION INSTRTBL;
>
> CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
> RETURNS TABLE ( INSTRTBL CLOB(64K) )
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
> RETURN
> WITH t(ordinal, index) AS
> ( SELECT ordinal, index
> FROM TABLE ( in_liststring(string) ) AS x )
> SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
> -- the join below makes sure that we have the lower and
> -- upper index where we can find each of the ',' delimiters
> -- that are separating the INSTRTBL. (For this, we exploit
> -- the additional indexes pointing to the beginning and end
> -- of the string.)
> FROM t AS t1 JOIN t AS t2 ON
> ( t2.ordinal = t1.ordinal+1 )
> ;
>
>

create or replace function instrtbl(text)
returns table(instrtbl text)
language sql
immutable
strict
as $$
SELECT * FROM regexp_split_to_table($1, ',')
$$;

filip(at)filip=# select * from instrtbl( 'one, two, really long three' );
instrtbl
--------------------
one
two
really long three
(3 rows)

I love PostgreSQL.

Filip

Thanks this solves my problem..

________________________________

From: filip(dot)rembialkowski(at)gmail(dot)com [mailto:filip(dot)rembialkowski(at)gmail(dot)com] On Behalf Of Filip Rembialkowski
Sent: Wednesday, January 12, 2011 1:41 AM
To: Amar Dhole
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] help needs in converting db2 function in postgresql.

2011/1/11 Amar Dhole <adhole(at)tibco(dot)com>

Hi,
I need helping converting following db2 function in postgresql function.
Any pointer will be great help in proceeding me ahead.

CREATE FUNCTION in_liststring ( string CLOB(64K) )
RETURNS TABLE ( ordinal INTEGER, index INTEGER )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( VALUES ( 0, 0 )
UNION ALL
SELECT ordinal+1, COALESCE(NULLIF(
-- find the next delimiter ','
LOCATE(',', string, index+1), 0),
LENGTH(string)+1)
FROM t
-- to prevent a warning condition for infinite
-- recursions, we add the explicit upper
-- boundary for the &quot;ordinal&quot; values
WHERE ordinal < 10000 AND
-- terminate if there are no further delimiters
-- remaining
LOCATE(',', string, index+1) <> 0 )
SELECT ordinal, index
FROM t
UNION ALL
-- add indicator for the end of the string
SELECT MAX(ordinal)+1, LENGTH(string)+1
FROM t
;

commit;

DROP FUNCTION INSTRTBL;

CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
RETURNS TABLE ( INSTRTBL CLOB(64K) )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
WITH t(ordinal, index) AS
( SELECT ordinal, index
FROM TABLE ( in_liststring(string) ) AS x )
SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
-- the join below makes sure that we have the lower and
-- upper index where we can find each of the ',' delimiters
-- that are separating the INSTRTBL. (For this, we exploit
-- the additional indexes pointing to the beginning and end
-- of the string.)
FROM t AS t1 JOIN t AS t2 ON
( t2.ordinal = t1.ordinal+1 )
;

create or replace function instrtbl(text)
returns table(instrtbl text)
language sql
immutable
strict
as $$
SELECT * FROM regexp_split_to_table($1, ',')
$$;

filip(at)filip=# select * from instrtbl( 'one, two, really long three' );
instrtbl
--------------------
one
two
really long three
(3 rows)

I love PostgreSQL.

Filip

I have table created as follows

CREATE TABLE D_2147483927_2147484848_TAB(

CP VARCHAR(256) ,

CPR VARCHAR(256) ,

CHOUSENO VARCHAR(256) ,

CSTREET VARCHAR(256) ,

CLOCALITY VARCHAR(256) ,

CCITY VARCHAR(256) ,

CPROVINCE VARCHAR(256) ,

CCOUNTRY VARCHAR(256) ,

CZIP VARCHAR(256) ,

CCO VARCHAR(256) )

I am using copy command to copy the content of file into the table.
(one.txt)

PR,PRO,HOUSENO,STREET,LOCALITY,CITY,PROVINCE,COUNTRY,ZIP,CON

,,A-24 Siddi vihar apt.,Near Krishna Chowk,New
Sanghvi,Pune,MH,India,411027

In the above data, data for last column is missing.

copy D_2147483927_2147484848_TAB from 'D:/work/one.txt' with delimiter
as ',' quote '"' csv HEADER ;

I get the following error as

ERROR: missing data for column "ccontains"

CONTEXT: COPY d_2147483927_2147484848_tab, line 2: "q,q,A-24 Siddi
vihar apt.,

Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 "

Can any one please tell me how can I make copy command to ignore the
data missing column ? as the data in file is unknown so it column name
is which is missing is not known in advance.

Thanks

Amar

On 01/17/2011 05:59 AM, Amar Dhole wrote:
> I have table created as follows
>
> CREATE TABLE D_2147483927_2147484848_TAB(
>
> CP VARCHAR(256) ,
>
> CPR VARCHAR(256) ,
>
> CHOUSENO VARCHAR(256) ,
>
> CSTREET VARCHAR(256) ,
>
> CLOCALITY VARCHAR(256) ,
>
> CCITY VARCHAR(256) ,
>
> CPROVINCE VARCHAR(256) ,
>
> CCOUNTRY VARCHAR(256) ,
>
> CZIP VARCHAR(256) ,
>
> CCO VARCHAR(256) )
>
> I am using copy command to copy the content of file into the table.
> (one.txt)
>
> PR,PRO,HOUSENO,STREET,LOCALITY,CITY,PROVINCE,COUNTRY,ZIP,CON
>
> ,,A-24 Siddi vihar apt.,Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027
>
> In the above data, data for last column is missing.

Looks like two columns missing. I see 10 headers and 8 data values
unless that is a cut and paste issue.

>
> copy D_2147483927_2147484848_TAB from 'D:/work/one.txt' with delimiter
> as ',' quote '"' csv HEADER ;
>
> I get the following error as
>
> ERROR: missing data for column "ccontains"

Where does ccontains from? I am not seeing it in the table or the text file.

>
> CONTEXT: COPY d_2147483927_2147484848_tab, line 2: "q,q,A-24 Siddi vihar
> apt.,
>
> Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 "
>
> Can any one please tell me how can I make copy command to ignore the
> data missing column ? as the data in file is unknown so it column name
> is which is missing is not known in advance.

You can specify a column list to COPY. See here:
http://www.postgresql.org/docs/9.0/interactive/sql-copy.html

>
> Thanks
>
> Amar
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

COPY D_2147483927_2147484848_TAB
(
CP
, CPR
, CHOUSENO
, CSTREET
, CLOCALITY
, CCITY
, CPROVINCE
, CCOUNTRY
, CZIP
)
FROM 'one.txt'
WITH DELIMITER ',' QUOTE '"' csv HEADER;

On Mon, Jan 17, 2011 at 7:59 AM, Amar Dhole <adhole(at)tibco(dot)com> wrote:

> I have table created as follows
>
>
>
>
>
> CREATE TABLE D_2147483927_2147484848_TAB(
>
> CP VARCHAR(256) ,
>
> CPR VARCHAR(256) ,
>
> CHOUSENO VARCHAR(256) ,
>
> CSTREET VARCHAR(256) ,
>
> CLOCALITY VARCHAR(256) ,
>
> CCITY VARCHAR(256) ,
>
> CPROVINCE VARCHAR(256) ,
>
> CCOUNTRY VARCHAR(256) ,
>
> CZIP VARCHAR(256) ,
>
> CCO VARCHAR(256) )
>
>
>
> I am using copy command to copy the content of file into the table.
> (one.txt)
>
>
>
> PR,PRO,HOUSENO,STREET,LOCALITY,CITY,PROVINCE,COUNTRY,ZIP,CON
>
> ,,A-24 Siddi vihar apt.,Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027
>
>
>
>
>
> In the above data, data for last column is missing.
>
>
>
>
>
> copy D_2147483927_2147484848_TAB from 'D:/work/one.txt' with delimiter as
> ',' quote '"' csv HEADER ;
>
>
>
>
>
> I get the following error as
>
>
>
> ERROR: missing data for column "ccontains"
>
> CONTEXT: COPY d_2147483927_2147484848_tab, line 2: "q,q,A-24 Siddi vihar
> apt.,
>
> Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 "
>
>
>
> Can any one please tell me how can I make copy command to ignore the data
> missing column ? as the data in file is unknown so it column name is which
> is missing is not known in advance.
>
>
>
> Thanks
>
> Amar
>
>
>