[SQL] Restricting columns by users
Hello, I'm looking for a method to restrict columns by users on postgresql, searching in google I found what I want, but in mysql, below I reproduce the paragraph that shows what I exactly want: "MySQL can also restrict access on the table level and even on the column level. What this means is that a user can have zero privileges on a database, but can have all privileges on a table in that database. Alternatively, a user can have zero privileges on a database, restricted privileges on a table, and all privileges on any particular column on that table. This is done by inserting records into the TABLE_PRIV table and the COLUMN_PRIV table." Exist something like it for postgresql? Thanks ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Range of caracters
Hello,
I have a doubt about a query using a range of caracters. Example:
Data Table
Inicial - Final
9C2HB02107R008000 9C2HB02107R008200
FAIXA1FAIXA100
I´m doing the following query:
SELECT recallfxchassi.* FROM recallfxchassi
LEFT JOIN recall ON recallfxchassi.idrecall = recall.id WHERE
('FAIXA2' BETWEEN chassiinicial AND chassifinal);
This is not returning anything. I believe that is because it´s check´s caracter
by caracter and 2 is bigger than 1. But in a real situation I will need that
return something (FAIXA2 is between FAIXA1 and FAIXA100).
Does anyone knows if exists some way or command in PostgreSQL that could solve
this "problem" ?
Thanks.
Alertas do Yahoo! Mail em seu celular. Saiba mais em
http://br.mobile.yahoo.com/mailalertas/
[SQL] Adding NOT NULL columns in PostgreSQL 7.4?
I'm currently using PostgreSQL 7.4.17, and trying to add a NOT NULL column results in an error about "Adding NOT NULL columns is not implemented". The only reports of this error I can find online relate to PostgreSQL 7.2, and further state that it was supposed to be implemented in PostgreSQL 7.3, so I can't figure out why it's still saying it's not implemented. -- Michael Eshom Christian Oldies Fan Cincinnati, Ohio
Re: [SQL] Adding NOT NULL columns in PostgreSQL 7.4?
On Aug 7, 2007, at 11:54 , Michael Eshom wrote: I'm currently using PostgreSQL 7.4.17, and trying to add a NOT NULL column results in an error about "Adding NOT NULL columns is not implemented". The only reports of this error I can find online relate to PostgreSQL 7.2, and further state that it was supposed to be implemented in PostgreSQL 7.3, so I can't figure out why it's still saying it's not implemented. You can always check the release notes or the relevant documentation: http://www.postgresql.org/docs/8.2/interactive/release-8-0.html#AEN82267 E.29.4.4. Object Manipulation Changes Allow ALTER ... ADD COLUMN with defaults and NOT NULL constraints; works per SQL spec (Rod) It is now possible for ADD COLUMN to create a column that is not initially filled with NULLs, but with a specified default value. As a workaround in 7.4, this should work: * Add the column (without the NOT NULL constraint) * UPDATE the table with the values you want in the new column * Add the NOT NULL constraint to the column. You should be able to wrap this all in a transaction if so desired. Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Range of caracters
On þri, 2007-08-07 at 10:07 -0700, Wilton Wonrath wrote:
> Data Table
>
> Inicial - Final
> 9C2HB02107R008000 9C2HB02107R008200
>
> FAIXA1FAIXA100
>
> I´m doing the following query:
>
> SELECT recallfxchassi.* FROM recallfxchassi
> LEFT JOIN recall ON recallfxchassi.idrecall = recall.id WHERE
> ('FAIXA2' BETWEEN chassiinicial AND chassifinal);
>
> This is not returning anything. I believe that is because it´s check´s
> caracter by caracter and 2 is bigger than 1. But in a real situation
> I will need that return something (FAIXA2 is between FAIXA1 and
> FAIXA100).
>
> Does anyone knows if exists some way or command in PostgreSQL that
> could solve this "problem" ?
sounds like you need to normalize your schema, but it is possible.
if the prefix is fixed then you can do
WHERE substr('FAIXA2',6)::integer
between substr(chassiinicial,6)::integer
and substr(chassifinal,6)::integer;
if the prefix is not fixed, but it's length is, and you are using a
sufficiently recent version of postgresql, you can do:
WHERE (substr('FAIXA2',1,5),
substr('FAIXA2',6)::integer)
between
(substr(chassiinicial,1,5),
substr(chassiinicial,6)::integer)
and (substr(chassifinal,1,5),
substr(chassifinal,6)::integer);
if the prefix length is not fixed, you will have
to do some juggling:
WHERE (substring('FAIXA2' from '^[^0-9]*'),
substring('FAIXA2' from '[0-9]+$')::integer)
between
(substr(chassiinicial from '^[^0-9]*'),
substr(chassiinicial from '[0-9]+$')::integer)
and (substr(chassifinal from '^[^0-9]*'),
substr(chassifinal from '[0-9]+$')::integer);
gnari
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Using function like where clause
On mán, 2007-08-06 at 16:44 -0300, Ranieri Mazili wrote: > 1) Can I use a function that will return a string in a where clause like > bellow? > > select * > from table > where my_function_making_where() > and another_field = 'another_think' you could have your function return a boolean instead of a string > 2) Can I use a function that will return a string to return the list of > columns that I want to show like below? > > select my_function_making_list_of_columns() > from table > where field_test = 'mydatum' no gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Restricting columns by users
On 8/8/07, Ranieri Mazili <[EMAIL PROTECTED]> wrote: > Exist something like it for postgresql? I thought that's what views are for > Thanks Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] populate value of column
I have a table CREATE TABLE meter ( meter_id integer NOT NULL, area_no integer NOT NULL, CONSTRAINT meter_pkey PRIMARY KEY (meter_id) ) ; INSERT INTO meter(meter_id, no_of_bays) VALUES (1001, 4); INSERT INTO meter(meter_id, no_of_bays) VALUES (1012, 6); select meter_id, area_no from meter; meter_id | no_of_bays --+ 1001 | 4 1012 | 6 How can I generate the following result? meter_id | bay --+ 1001 | 01 1001 | 02 1001 | 03 1001 | 04 1012 | 01 1012 | 02 1012 | 03 1012 | 04 1012 | 05 1012 | 06 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] populate value of column
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of novice Sent: Wednesday, 8 August 2007 15:31 To: pgsql-sql@postgresql.org Subject: [SQL] populate value of column > How can I generate the following result? > > meter_id | bay > --+ > 1001 | 01 > 1001 | 02 > 1001 | 03 > 1001 | 04 > 1012 | 01 > 1012 | 02 > 1012 | 03 > 1012 | 04 > 1012 | 05 > 1012 | 06 I even tested this one too ;) SELECT meter_id, LPAD(GENERATE_SERIES(1,area_no),2,'0') FROMmeter; Works on 8.2.4 - you didn't say what version you were using. ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] populate value of column
On Aug 8, 2007, at 0:30 , novice wrote: CREATE TABLE meter ( meter_id integer NOT NULL, area_no integer NOT NULL, CONSTRAINT meter_pkey PRIMARY KEY (meter_id) ) ; INSERT INTO meter(meter_id, no_of_bays) VALUES (1001, 4); INSERT INTO meter(meter_id, no_of_bays) VALUES (1012, 6); Your insert columns don't match your table. How can I generate the following result? meter_id | bay --+ 1001 | 01 Your column headers don't match your table definition *or* your insert statements. I'm not sure what you're trying to do. Can you explain the result you want? (And in the future, please post actual DDL and statements.) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] populate value of column
Thank you :-)) That's so quick! Apologies for the error on my DDL statement On 08/08/07, Phillip Smith <[EMAIL PROTECTED]> wrote: > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > On Behalf Of novice > Sent: Wednesday, 8 August 2007 15:31 > To: pgsql-sql@postgresql.org > Subject: [SQL] populate value of column > > > How can I generate the following result? > > > > meter_id | bay > > --+ > > 1001 | 01 > > 1001 | 02 > > 1001 | 03 > > 1001 | 04 > > 1012 | 01 > > 1012 | 02 > > 1012 | 03 > > 1012 | 04 > > 1012 | 05 > > 1012 | 06 > > I even tested this one too ;) > SELECT meter_id, > LPAD(GENERATE_SERIES(1,area_no),2,'0') > FROMmeter; > > Works on 8.2.4 - you didn't say what version you were using. > > > ***Confidentiality and Privilege Notice*** > > The material contained in this message is privileged and confidential to > the addressee. If you are not the addressee indicated in this message or > responsible for delivery of the message to such person, you may not copy > or deliver this message to anyone, and you should destroy it and kindly > notify the sender by reply email. > > Information in this message that does not relate to the official business > of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. > Weatherbeeta, its employees, contractors or associates shall not be liable > for direct, indirect or consequential loss arising from transmission of this > message or any attachments > > THINK BEFORE YOU PRINT - Save paper if you don't really need to print this > e-mail. > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
