Postgresql – Why is “INSERT INTO table;” a syntax error

insertpostgresqlsyntax

I have this:

    CREATE TABLE people
    (
        id              bigserial,
        timestamp       timestamptz DEFAULT now() NOT NULL,
        PRIMARY KEY     (id)
    );

    CREATE TABLE "personal information"
    (
        id                  bigserial,
        "person id"         bigint NOT NULL,
        timestamp           timestamptz DEFAULT now() NOT NULL,
        "data's timestamp"  timestamptz,
        "field"             text NOT NULL,
        "value"             text,
        PRIMARY KEY         (id),
        FOREIGN KEY         ("person id") REFERENCES people (id) ON UPDATE CASCADE ON DELETE CASCADE
    );

    INSERT INTO people RETURNING id; -- Syntax error.
    INSERT INTO people; -- Syntax error.

Why do my INSERTS fail? It's a fact that all the columns in the "people" table are automatic: the id column is a bigserial, and the timestamp one is always set to the current time when it was inserted. Thus, there is no need for me to specify any columns to be inserted, as all of them are automatic.

So why is it a syntax error? What does it want me to do? This makes me feel as if I'm not doing this right somehow, but having a bunch of "personal data" fields in the "people" table would be redundant since that data will be stored in the "personal information" table. For example, the "field" might be "social security number" or "first name", so I just grab whatever record is the latest (based on timestamp) for that "person id" to get their social security number or first name.

If I were to store those values as columns in "people", it would be pointless redundancy, and it would make it a major PITA whenever I introduce a new kind of "personal information field" in the future, as I'd have to add columns to the "people" table. So I really hope that my efficient and smart (IMO) structure is not somehow incompatible with PostgreSQL, and that I'm just missing some minor syntax detail.

Best Answer

The syntax rules require at least one target column to be specified. If you want to let the defaults "kick in" for all columns, you can use the default values clause.

INSERT INTO people DEFAULT VALUES;

another alternative is to provide one column with the DEFAULT clause:

insert into people ("timestamp") values (default);

Related Solutions

Oracle Trigger – Insert Values Based on Existence with Check Constraint

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.

Sql-server – Nightly Excel Spreadheet import into mssql database

I think SSIS is really what you want to use here. To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps

From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules. Lastly for your PMP count, it seems like using the RowCount task should cover what you are looking to do with that.

There are dozens of ways to do these things even if you decide to go about this using SSIS. You can load your data into Staging tables so that everything is an insert and then you run Execute SQL Tasks to do all of the individual updates. Or you could use that MultiCast/Conditional Split approach and then use SQL Server Destinations or you could use OLE DB Commands in order to get the information either loaded or update individual records based on the data that you have available to you within the data flow. There are certainly performance issues to deal with when using those OLE DB Commands, since they are working each thing row by row, but they are fairly clear to understand within the Data flow task.

You will have to work through what works best for you. I have tried to include links of what I feel are good examples to show you how to do these kinds of things (Excel import, Multicast, Conditional Split, RowCount and OLE DB Command), so hopefully between that this will get you where you need to go.

Best Answer

Related Solutions

Oracle Trigger – Insert Values Based on Existence with Check Constraint

Sql-server – Nightly Excel Spreadheet import into mssql database

Related Question