1

I have a with meterid,meterreading,reader and date read.I wish to store this data using Postgis database.

table

I used this code via postgresql to create the table

CREATE TABLE MeterRdg(
    meterid VARCHAR(5) NOT NULL PRIMARY KEY, 
    reading integer,
    reader VARCHAR(15),
    DateRead date)

I then tried to insert data into the table using the following command

INSERT INTO MeterRdg(meterid, reading,reader,DateRead)
VALUES
('M1',871.31,'Kamau','26/02/2015'),
('M2',624.53,'Kamau','26/02/2015'),
('M3',531.47,'Kamau','27/02/2015'),
('M1',926.44,'Ochieng','25/03/2015'),
('M2',761.31,'Ochieng','26/03/2015'),
('M3',683.51,'Ochieng','26/03/2015')

I get the following error:

ERROR: duplicate key value violates unique constraint "meterrdg_pkey" DETAIL: Key (meterid)=(M1) already exists.

this error

How can I redesign the CREATE table command such that M1,M2,M3 can hold multi-values?

closed as off-topic by Vince, user30184, underdark Aug 11 '15 at 16:07

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general Information Technology, with no clear GIS component, are off-topic here, but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software) and Database Administrators (relational databases)" – Vince, user30184, underdark
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    This is unrelated to GIS, but you should review the meaning of "primary key". – Vince Aug 11 '15 at 12:03
  • its related to GIS, the database am creating is a spatial one, hosted on PostGIS(a spatial database) and visualization and analysis on QGIS – Peter M Macharia Aug 11 '15 at 12:09
2

You have defined meterid as a PRIMARY KEY so they should be unique.

You may use Composite Key

CREATE TABLE MeterRdg(
    meterid VARCHAR(5) NOT NULL, 
    reading integer,
    reader VARCHAR(15),
    DateRead date,
    PRIMARY KEY(meterid, DateRead)
)

or add a column with counter and use it as a key for whole record

CREATE TABLE MeterRdg(
    record_key    serial primary key,
    meterid VARCHAR(5) NOT NULL, 
    reading integer,
    reader VARCHAR(15),
    DateRead date
)
  • in the first option, I presume that date you meant Dateread in defining the primary key........ PRIMARY KEY(meterid, date) such that it should be PRIMARY KEY(meterid, DateRead) – Peter M Macharia Aug 11 '15 at 12:21
  • Yes, I've messed them. You may choose any set of columns to be Composite Key. – dmitry.v.kiselev Aug 11 '15 at 12:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.