Tag Info

New answers tagged

3

For now While stuck with your unfortunate solution: CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time) That's right, AT TIME ZONE two times: The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times. The second instance converts ...


0

I would commend alternative 1 to you. Just as having cells as columns a01, a02 etc. is poor normalisation so, too, is having measurements as m01, m02 and so on. In your example you have disguised this by calling them foo, bar and baz, but that is what they are. To address this what you call measurement whould more usefully be called Well: CREATE TABLE ...


3

You may use to_char to get the time fields from a single function call: check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59') Seconds given by SS are not rounded up so that should be OK as an equivalent to floor


0

Try this. It maybe be faster, but still unreadable: alter table bananas add constraint ck_end_time_is_end_of_day check ( (end_time at time zone 'UTC' at time zone 'US/Eastern')::timestamp::date + interval '23:59:59' = date_trunc('second', end_time at time zone 'UTC' at time zone 'US/Eastern') ) ;


2

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables. We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a ...


0

Bingo!! I did exactly what Craig said and that worked :) Although I showed just two check constraints above, there were more actually. And that was a blunder me trying to create unique constraints rather than check constraints. Thx for rectifying Craig. What I did Imported the raster data using raster2pgsql command from the terminal. Made a copy of ...


4

OK, so you: Dropped a bunch of constraints Did some work Attempted to add a completely unrelated random constraint and that failed You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint. You ...


3

As Craig explained very well, you cannot create a unique index on the type raster without all the necessary operators. Your second best bet to enforce uniqueness is to create a functional index on the text representation: CREATE UNIQUE INDEX us_tmin_enforce_scalex_rast ON chp05.us_tmin (cast(rast as text)) Note that this cannot be implemented as ...


4

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.: regress=> create unique index indexname on test USING GiST(id) ; ERROR: access method "gist" does not support unique indexes Data types ...


1

The solution is to add the group category column in the StoreGroups table and then add a unique constraint on (storeId, groupCategory). The table definitions would be like this: CREATE TABLE Groups ( groupID INT NOT NULL PRIMARY KEY , name VARCHAR(255) NOT NULL , groupCategory NOT NULL VARCHAR(30) , exprireDate DATE NULL , UNIQUE (groupCategory, ...


2

One row per rare type If you want to allow 0-n rows with type 'c' and only 0-1 rows for type 'a' and 'b' (each), you can combine a simple CHECK constraint with a partial unique index: CREATE TABLE example ( example_id serial PRIMARY KEY, example_state "char" NOT NULL CHECK (example_state IN ('a', 'b', 'c')) ); CREATE UNIQUE INDEX ...


0

In create table you have set teams as primary key, and also you are aware that primary key does not allow duplicate values. mysql> create table tblShowteam( -> SetId int, -> datum date, -> teams int, -> primary key (teams)); Query OK, 0 rows affected (0.13 sec) Check the ...



Top 50 recent answers are included