CODEWITHSUNDEEP
sqlpostgresql
Susannah Potts
Susannah Potts

Reputation: 827

SQL - Inserting into postgresql table produces error on semi-colon

I'm trying to insert some test data into a table to check the functionality of a web servlet, however, using pgAdmin4 to do the insert, I am running into an issue I'm not sure how to rectify. What I want to see is the last value (an image byte stream) is null for this test info. Here is my insert statement:

INSERT INTO schema.tablename("Test Title", "Test Content", "OldWhovian", "2016-07-29 09:13:00", "1469808871694", "null");

I get back:

ERROR:  syntax error at or near ";"
LINE 1: ...ldWhovian", "2016-07-29 09:13:00", "1469808871694", "null");
                                                                      ^
********** Error **********

ERROR: syntax error at or near ";"
SQL state: 42601
Character: 122

I've tried removing the semi-colon just for kicks, and it instead errors on the close parenthesis. Is it an issue related to the null? I tried doing this without putting quotations around the null and I get back the same error but on the null instead of the semi-colon. Any help is appreciated, I am new to DBA/DBD related activities.

Related: Using PostgreSql 9.6

Upvotes: 0

Views: 2114

Answers (1)

vitfo
vitfo

Reputation: 10291

The insert statement usually has first part where you specify into which columns you want to insert and second part where you specify what values you want to insert.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

You do not need to specify into which columns part only if you supply all values in the second part. If you have a table with seven columns you can omit the first part if in the second part you supply seven values.

INSERT INTO table_name VALUES (value1, value2, value3, ...);

Example:

drop table if exists my_table;
create table my_table (
    id int not null,
    username varchar(10) not null,
    nockname varchar(10),
    created timestamptz
);

INSERT INTO my_table (id, username) VALUES (1, 'user01');

You insert into columns id and username. The column created has default value specified so when you do not supply value in insert the default is used instead. Nickname and identification_number can accept null values. When no value is supplied NULL is used.

INSERT INTO my_table VALUES (2, 'user02', NULL, NULL, current_timestamp);

That is the same as the previous but here is omitted the fist part so you must supply values for all columns. If you did not you would get an error.

If you want insert multiple values you can use several statements.

INSERT INTO my_table (id, username, identification_number) VALUES (3, 'user03', 'BD5678');
INSERT INTO my_table (id, username, created) VALUES (4, 'user04', '2016-07-30 09:26:57');

Or you can use the postgres simplification for such inserts.

INSERT INTO my_table (id, username, nickname, identification_number) VALUES 
    (5, 'user05', 'fifth', 'SX59445'),
    (6, 'user06', NULL, NULL),
    (7, 'user07', NULL, 'AG1123');

At the beginning I have written that you can omit the first part (where you specify columns) only if you supply values for all columns in the second part. It is not completely true. In special cases when you have table that has nullable columns (columns that can contain NULL value) or you have specified DEFAUL values you can also omit the first part.

create sequence my_seq start 101;
create table my_table2 (
    id              int             not null default nextval('my_seq'),
    username        varchar(10)     not null default 'default',
    nickname        varchar(10),
    identification_number   varchar(10),
    created         timestamptz     default current_timestamp
);

INSERT INTO my_table2 DEFAULT VALUES;
INSERT INTO my_table2 DEFAULT VALUES;
INSERT INTO my_table2 DEFAULT VALUES;

Result:

101   default   NULL   NULL  2016-07-30 10:28:27.797+02
102   default   NULL   NULL  2016-07-30 10:28:27.797+02
103   default   NULL   NULL  2016-07-30 10:28:27.797+02

When you do not specify values defaults are used or null. In the example above the id column has default value from sequence, username has default string "default", nickname and identification_number are null if not specified and created has default value current timestamp.

More information:

PostgreSQL INSERT

Upvotes: 2

Related Questions