Single Row Table? - Page 2 - PostgreSQL Database

Yateen Joshi

Hi,

I have got a table which is supposed to contain only one row. It does
not have any primary keys defined.
So, essentially, when a new insert happens in that table, I would like
it (the insert) to fail if there is already a row existing in that
table.
How can I do that? Can I add any constraints? Or do I need to write a
separate trigger for the same?

Thanks and regards,

Yateen V. Joshi

Nov 23 '05

Subscribe Reply

2721

Tom Lane

Thomas Kellerer <sp********@gmx .net> writes:

That was one of my first guesses as well, but then I'm not sure if you can
revoke DELETE and INSERT privilege from the owner of the table...

In Postgres you can do this, although I think it's contrary to the
restrictions of the SQL spec, so it might not work in other DBs.

The reason is that Postgres treats the owner's ordinary privileges
as having been granted by the owner to himself; therefore he can revoke
'em too. The SQL spec treats the owner's privileges as having been
granted by the mystical entity "_SYSTEM"; not being "_SYSTEM", the owner
cannot revoke them.

Since it's obviously useful to be able to make a read-only table,
I think that the spec has missed a bet here.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #11

Thomas Kellerer

Tom Lane wrote on 31.08.2004 22:59:

In Postgres you can do this, although I think it's contrary to the
restrictions of the SQL spec, so it might not work in other DBs.

The reason is that Postgres treats the owner's ordinary privileges
as having been granted by the owner to himself; therefore he can revoke
'em too. The SQL spec treats the owner's privileges as having been
granted by the mystical entity "_SYSTEM"; not being "_SYSTEM", the owner
cannot revoke them.

Since it's obviously useful to be able to make a read-only table,
I think that the spec has missed a bet here.

Thanks for the explanation. And yes I agree with you that making a table
read only by revoking the grants is something *very* useful.

Cheers
Thomas
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #12

Mike Nolan

> > Why not just revoke the delete privilege?

That was one of my first guesses as well, but then I'm not sure if you can
revoke DELETE and INSERT privilege from the owner of the table...

I just did a quick test on 7.4.5. Yes the table owner can revoke (and
re-grant) delete privileges on a table he owns, but of course I was not
able to revoke delete privileges from a superuser, since by definition
a superuser bypasses all access controls restrictions.

I assume the rule approach would apply to the superuser as well as to
other users. That makes it better able to handle this situation, whether
or not that approach has downsides is an internals question I can't answer.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #13

Mike Nolan

> Thanks for the explanation. And yes I agree with you that making a table

read only by revoking the grants is something *very* useful.

IIRC, Oracle supports read-only tablespaces. The reason for doing it at
the tablespace level probably has to with the overall concept of tablespaces
being linked to physical storage issues. Thus a read-only tablespace
could be stored on CD-ROM or some other non-writeable medium.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #14

Similar topics

12029

Single pixel border

by: Bob Sanderson | last post by:

I have a number of pages containing tables. Most have background images which have a single pixel border. The tables themselves do not have borders. For various reasons, I have to create one table without an image, so the border must be defined by the table. However, the table border does not look exactly like the single pixel one in the images. It appears to have some shading. I have tried various CSS elements but have not been able...

HTML / CSS

2790

How do copy Strings from a single dimensional array to double dimensional array

by: Venkat | last post by:

Hi All, I need to copy strings from a single dimensional array to a double dimensional array. Here is my program. #include <stdio.h> #include <stdlib.h>

C / C++

2270

Optimize query: time of "single * IN(many)" > time of "many * IN(single)"

by: Paul Janssen | last post by:

Hello! Can anyone help me out with the following situation: (a) a single query with 550 id's in the IN-clause resulting into 800+ seconds; (b) 550 queries with a single id in the IN-clause resulting into overall time of <60 seconds; The table consists of 950.000 records, and the resultset consists of 205.000 records.

PostgreSQL Database

2617

Updating Multiple Access Tables with Single Excel File

by: Ken | last post by:

Help! I have a MS Access database made up of a single table. The data updates for this database comes in the form of a single Excel spreadsheet, which is imported into the database table. I need to break-out this single database table, into multiple tables to better structure the database and reduce the need to input the same data repeatedly.

Microsoft Access / VBA

2235

Application Design, <SELECT> Multiple or Single Table?

by: rayone | last post by:

Hi folks. I need advice. 2 options, which do you think is the better option to display/retrieve/report on the data. Keep in mind reporting (Crystal), SQL Performance, VB Code, usability, architecture. Case 1: On a web page I would like to render a dropdown list

C# / C Sharp

1352

Q: Updating a single row

by: Geoff Jones | last post by:

Hi Using code like: dataAdaptor1.Update(dataSet1.Table("MyTable")) it is possible to update a datasource with all the rows in MyTable. What is the code to update a certain row in MyTable?

Visual Basic .NET

1416

linking within a single page in PHP

by: arteezan | last post by:

Mabuhay! I'm new in PHP and i want to create a single PHP page where the links are linked to contents within that single page. can anyone please help me with this? thanks in advance to anyone who is willing to help!

PHP

2988

Parse Nested Elements to Single DataGrid

by: BMeyer | last post by:

I have been losing my mind trying to parse an XML document (with nested child elements, not all of which appear in each parent node) into a DataGrid object. What I want to do is "flatten" the XML document into a text document with a single row for each parent node (that has all of the values from all of the child nodes for that row) The DataView within VS 2005 IDE displays my 15 or so child tables - and knows that some parent rows...

.NET Framework

1218

Output in a Single Table

by: sqldba20 | last post by:

Folks: I need help with a script. I have 4 tables with AsOfDate, Country and FactorInt columns. In each table AsOfdate and Country are the same for that date but the FACTORINT column varies. I want the output in a single table (master table) and it should be in this format and look like this: TBLMASTER AsOfDate Country Factor1 Factor2 Factor3 Factor4...

Microsoft SQL Server

6426

Procedure to accept a list of values in a single parameter

by: debasisdas | last post by:

The following sample code is designed to display the use of accepting a list of values in a single parameter and process the same in the where clause inside a procedure. STEP1:-First create an user defined type . CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767); STEP2:-Create a function to split the list of values in a single parameter. CREATE OR REPLACE FUNCTION SPLIT (

Oracle Database

9734

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9607

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10652

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10395

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10137

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5561

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5700

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4346

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3874

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP